I'm an idiot

[LewisJ]

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

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

1. It's phpMyAdmin
2. 70k inserts is too much for a web POST esp with PHP's builtin execution timeout. Either try uploading the file with all the inserts from the import menu in PMA or running it straight from the MySQL CLI.

Also be sure to use DB transactions if at all possible! That would prevent problems in the future...

And as for who writes a DB system like that...well, relational databases using SQL for queries comprise the enormous majority of all databases in the world. It's just that oftentimes databases are accessed through a nice squishy frontend that WILL have a big red "are you sure you wanna delete everything?" warning.

 

[Faz]

Nooo!!! My awesome 5x5 solves.

Idc about everything else.

 

[IamWEB]

*screenshots*

 

[shelley]

Relax, everyone. Backups exist for situations like this.

 

[JBCM627]

And as for who writes a DB system like that...well, relational databases using SQL for queries comprise the enormous majority of all databases in the world.

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

 

[Ton]

This is an open invitation for everyone to tell me how stupid I am.

Well, In short , delete works fine in SQL

 

[Swoncen]

I'm sure this can be fixed! Don't panic now.

 

[Erik]

haha fail idiot:fp! (hey, you asked for it )
But, I'm sure it all can be fixed soon. And if not and only the results of the last weekend count then I'm the happy WR holder of at least 12 WR's

 

[Carrot]

Fail =D.... Why am I 13th in pyraminx single? :fp (according to my PB's..) I can't even check who just pwned me in pyraminx >.<

 

[AvGalen]

Backups are a must and SHOULD be good enough for a low-transaction-count database like this.
I would recommend a transactional database with a "Full Recovery" option (example) that would have turned this whole problem into a "recover untill 1 second before Tyson fudged up" issue (also known as big red undo button)

I guess the positive thing about this is that the statistics page will soon be refreshed

Edit: Just checked my personal page and everything is back.....unless have you have competed in the last 2.5 years . I really hope a more recent database backup exists!

 

[Muesli]

btw: Just remember if you have to some how recreate all this, I had a 6.87 single for 3x3 speed in comp.

Dont forget my world record in all puzzles either.

 

[Ron]

We have 4 types of back-ups:
1) our hosting provider makes daily back-ups
2) I make local back-ups every month
3) we post a partial back-up in Excel every month
4) we still have all source result files of all competitions

Regarding 1: our hosting provider Aplus.net was taken over by Hostopedia. Our server was recently migrated to the new company. We have had many different types of problems since then.

- On November 8 the database was OK again.
- On November 11 some idiot at Hostopedia restored a database version of November 6.
- On November 13 some idiot at Hostopedia restored a database version of November 11 (being the November 6 version).

Then they told me that they did not have back-ups of November 6-11.
So I had to rebuild everything since November 6.

- On November 15 I had finally caught up on everything and posted this weekend's results.

- Today some other idiot deleted all results down to 2007.

- There is another idiot who gave the 'some other idiot' access to the database.
His punishment is that tonight he needs to restore the November 2 dump and add the recent competition results.

And still we are all friends.

Have fun,

Ron
PS: still 2 other issues are pending, one of them is that Drupal (CMS of the WCA main page) is still not working.

 

[Anthony]

Sounds like Ron has everything under control.

 

[nitrocan]

How about making the system so that it backs up every time you make a change.

 

[Hyprul 9-ty2]

I'm competing on the 28th *sigh of relief*

 

[LewisJ]

And as for who writes a DB system like that...well, relational databases using SQL for queries comprise the enormous majority of all databases in the world.

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

MySQL is a relational database system; regardless of a databases schema, a MySQL database IS a relational database.

 

[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.
2. The type of database wasn't how this happened.
3. Yes they're in one table, but what's the problem with that?
4. Suggest an alternative way to store the results?

 

[Tyson]

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.
2. The type of database wasn't how this happened.
3. Yes they're in one table, but what's the problem with that?
4. Suggest an alternative way to store the results?

In response to number 2, yes, it wasn't the database that caused this. It was all me. In all my fame and glory.

Though, I must say, if in the process of deleting rows, the system had warned me "do you really want to delete 73,380 rows?" instead of "are you sure you want to delete", I probably would have caught it.

 

[hr.mohr]

Thanks for the info Ron.

 

[AvGalen]

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.
2. The type of database wasn't how this happened.
3. Yes they're in one table, but what's the problem with that?
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
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)