I'm an idiot

[pjk]

Things happen, I'm sure it will be corrected. I assume backups were done (as Ron mentioned), and if not, you can always just re-add the competition files that are saved. It may be best to back it up after new competition data is added for now on. There is really no reason not to.

Tip: never rely on the webhost for your backups.

 

[LewisJ]

1) I would expect so. I am assuming users are stored in a user-table, results are stored in a results-table and results have a relation to the user-table
2) Nope, giving someone permission to run a delete-query was. Combined with a non-transactional backup-scheme
3) I don't see any problems with this at all. It would surely be how I did it
4) Why? The biggest optimisation that I could think of would be to have an index on the results with the newest results listed first. 75.000 records are really not a significant amount to wonder about optimisations like a seperate archive table for older results. I get the idea that you are already caching lots of data (like WR's and the statistics page) that are accessed often and don't change often. Because this database is basically read-only untill new competition data is entered I would assume that there is either a script or a trigger that calculates the cached data after every bulk update

So basically my recommendations for the future would be: Don't make mistakes, don't give too many people permissions to execute non-readonly queries and especially to use transactions so you have much greater control (at the cost of more diskspace and maybe performance)

That is something along the lines of how the results are stored; a lot of the issue with this was also the web interface; phpMyAdmin is a dangerous thing for someone that doesn't know exactly what they're doing.

So basically:
-Be more careful with the database in all possible ways
-The WCA DB's schema is just fine (assuming it uses innoDB for the tables (MyISAM can't run transactions) and has foreign keys and such)

 

[Muesli]

ctrl+z?

 

[Stefan]

But the WCA database isn't a relational database, which is how this happened. All results are in one giant table.

1. The WCA database is a relational database.
4. Suggest an alternative way to store the results?

1) I would expect so. I am assuming users are stored in a user-table, results are stored in a results-table and results have a relation to the user-table
4) Why?

1. Yes, we do have several tables like that. But in my opinion, that's not necessary for a relational database. A single relation is enough.
4. Not because I see a problem with the current way or want to change it. No, just because he badmouthed the current way. Surely he knows a better way or else he wouldn't have done that. It's pretty useless and annoying to just say something is bad but not offer the slightest hint of how to improve it.

Btw, we do have our own admin backend for several tasks. Looks like we should extend it (talking about it with Tyson already). Ideally, everything should be done through that backend, nothing with direct database access.

 

[James Ludlow]

no big deal...a little duct tape and everything will be fine.

dammit, why wouldn't phpMyAdmin accept my 70,000 insert command? :/

As Clint Eastwood said in Gran Torino, “WD-40, a vice grip, and a roll of duct tape. Any man worth his salt can fix almost any problem with this stuff alone”

 

[DavidWoner]

 

[Swordsman Kirby]

That is quite an accurate portrait of Tyson.

 

[Swoncen]

ROF2L!!!

 

[hawkmp4]

Oh that comic made my day.

 

[theretardedcuber]

ROF2L!!!

LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLOOOOOOOOOOOOOOLZ

 

[Tyson]

Thanks guys.

Some faint memory of order has been re-established.

 

[TheBB]

Thanks Tyson, this made my day.

 

[Carrot]

ROF2L!!!

LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLOOOOOOOOOOOOOOLZ

ROF2L!!!!!

 

[Ron]

Database has been fully recovered!
If you find issues, please e-mail [email protected]

PS: and a fresh back-up was made...

 

[Nukoca]

Database has been fully recovered!
If you find issues, please e-mail [email protected]

PS: and a fresh back-up was made...

Are you sure? It still says that Tomasz Zolnowski has the world record of 7.91 sec.

 

[Erik]

I know the statistics page is only updated once in a while, but because of some weird reason at the "Sum of 3x3/4x4/5x5 ranks (since Oct 23, 2008 - Single and Average)" page, it says that I have the 12th single time in 3x3 (it's been doing this for a few months now if I'm correct so it doesn't have anything to do with "Tyson-gate" (kiddin!)). Just saying...

And, a big hand for Ron!

 

[Olivér Perge]

Database has been fully recovered!
If you find issues, please e-mail [email protected]

PS: and a fresh back-up was made...

Nice job! Thank you!

 

[JTW2007]

Certainly that's the hardest I've laughed in three months.

 

[wrbcube4]

I just made that my desktop background. hehe

 

[JBCM627]

While I'm not an expert on this, I do know there are a few reasons that breaking things down further will help. I gave a pretty direct hint for improvement when I 'badmouthed' the current schema: all the results are all in one table. So splitting things up I believe would be better. Maybe memory or efficiency aren't the biggest concerns now since there are 'only' 75,000 records. But what about in 10 years when there may be 1,000,000? At what point is it time to improve things?

By breaking the results database up, you can reduce memory usage and decrease query time quite a bit. Each competition could have its own table referencing individual events, which in turn would hold the actual results. Doing this for the WCA results means result tables lose half their current columns (big drop in memory usage!) and can be queried for individual competition results significantly faster as ~10^3 fewer items need to be searched through. By using appropriate table prefixes, you could even select results from tables without needing to go through indexes.

After breaking things up (if you don't want to use subqueries or joins, etc), tables to index event results by speed and competitor can be made. Mem
usage would go back up some, but not to where it was initially. Results can be queried just as fast for all rankings, and significantly faster for individual competition or competitor results.

This also essentially eliminates the chance of a fatal mistake like Tyson's (so yes, the database structure did contribute). If a competitor's name needs to be changed, using a more normalized schema it is changed once, not 100 times in some big results table. Assigning numeric uids means even WCAIDs can be changed by updating only 1 row. The results tables shouldn't even need to be touched. If a result needs to be modified, it is updated in just one event on just one table. No results from other competitions or events are touched. Damage is mitigated if tables are ruined.