# I'm an idiot



## Tyson (Nov 16, 2009)

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


----------



## iSpinz (Nov 16, 2009)

Your stupid?


----------



## 4Chan (Nov 16, 2009)

Does this have anything to do with Nationals?


----------



## JBCM627 (Nov 16, 2009)

Does this have anything to do with Tyson learning how to query databases? 

Good thing Ron just released that new version, I guess. How often is the database backed up?


----------



## qqwref (Nov 16, 2009)

I've seen worse.


----------



## Kian (Nov 16, 2009)

<Tyson> I ust did something really bad
<Tyson> and I pretty much need help now...
<Tyson> this is really bad
<+qqwref> wish I could help
<%PatrickJameson> did you just delete the entire WCA database?
*** esad3 has quit IRC: Ping timeout
<Tyson> [fudge] *edited for the forum*
<Tyson> yeah


----------



## Lt-UnReaL (Nov 16, 2009)

[22:38] <Tyson> DAMMIT ****!
[22:38] <Ethan_> wait
[22:38] <Ethan_> did you actually delete everything?
[22:38] <Eidolon> owned
[22:38] <Tyson> yes
[22:38] <PatrickJameson> holy **** you really did
[22:38] <qqwref> uhhhhh
[22:38] <Eidolon> look at the WCA site
[22:38] <qqwref> ...
[22:38] <Tyson> dude, ****ing help me
[22:38] <qqwref> uh.
[22:38] <Tyson> someone
[22:38] <flooom> OOLOL


----------



## blade740 (Nov 16, 2009)

really stupid.


----------



## Tyson (Nov 16, 2009)

We had joked about this at work too... with a trading firm who tried to play a April Fool's prank by updating the trading account value of one guy to 10 million... but they forgot a "WHERE name='GUY'" and updated EVERYONE.


----------



## iSpinz (Nov 16, 2009)

Wow. Just wow.


----------



## JBCM627 (Nov 16, 2009)

It has been mentioned before, but the put-everything-in-one-giant-table structure isn't great. Among other things (performance issues!!), it is extremely vulnerable to this sort of mistake. So don't feel bad, Tyson.


----------



## ajmorgan25 (Nov 16, 2009)

iSpinz said:


> Your stupid?



Edit: You're stupid.


----------



## Edmund (Nov 16, 2009)

You have to have all this on back-up somewhere, right?


----------



## JTW2007 (Nov 16, 2009)

Mah competitionz! NOOOOOoooooO!!1!


----------



## Edmund (Nov 16, 2009)

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


----------



## Tyson (Nov 16, 2009)

ajmorgan25 said:


> iSpinz said:
> 
> 
> > Your stupid?
> ...



I generally get pretty annoyed when people do that, but I'm going to say that in my current state of duress, it's understandable.


----------



## vrumanuk (Nov 16, 2009)

Edmund said:


> You have to have all this on back-up somewhere, right?



..


----------



## *LukeMayn* (Nov 16, 2009)

*._.*


----------



## JBCM627 (Nov 16, 2009)

vrumanuk said:


> Edmund said:
> 
> 
> > You have to have all this on back-up somewhere, right?
> ...





JBCM627 said:


> Good thing Ron just released that new version, I guess.



So in one form or another, yes.


----------



## Tim Major (Nov 16, 2009)

You're awesome. I am now equal best in the world at multi-bld. 0/0, My world record. (of course it's tied with thousands of other people.)


----------



## vrumanuk (Nov 16, 2009)

Seems like everyone's PBs are still on their respective pages.


----------



## Jake Gouldon (Nov 16, 2009)

Yes you are. haha.


----------



## PatrickJameson (Nov 16, 2009)

ZB_FTW!!! said:


> (of course it's tied with thousands of other people.)



About 6.79 billion to be more precise.


----------



## anythingtwisty (Nov 16, 2009)

Oh well. I guess it's time for some new world records!


----------



## shafiqdms1 (Nov 16, 2009)

that is pretty fail...but I am assuming they back their database up every now and then....right??


----------



## MichaelP. (Nov 16, 2009)

As soon as Austin fall is up, Anthony will hold the WR!!!


----------



## JTW2007 (Nov 16, 2009)

vrumanuk said:


> Seems like everyone's PBs are still on their respective pages.



Indeed. It appears that all competition history has been lost.


----------



## Connor (Nov 16, 2009)

http://74.125.155.132/search?q=cach...ersons&single=Single&cd=1&hl=en&ct=clnk&gl=ca

Cached pages FTW


----------



## Nukoca (Nov 16, 2009)

Oh, wow... this isn't good.


----------



## esquimalt1 (Nov 16, 2009)

Connor said:


> http://74.125.155.132/search?q=cach...ersons&single=Single&cd=1&hl=en&ct=clnk&gl=ca
> 
> Cached pages FTW



I think you just fixed the issue.


----------



## Konsta (Nov 16, 2009)

PatrickJameson said:


> ZB_FTW!!! said:
> 
> 
> > (of course it's tied with thousands of other people.)
> ...



That's one estimation, and/but it doesn't count in the _dead people_


----------



## PatrickJameson (Nov 16, 2009)

Konsta said:


> PatrickJameson said:
> 
> 
> > ZB_FTW!!! said:
> ...



Touché.


----------



## Dirk BerGuRK (Nov 16, 2009)

Haha wow. That had to be a horrible feeling when you realized what happened. I am sure you will find a solution if you haven't already.


----------



## Nukoca (Nov 16, 2009)

esquimalt1 said:


> Connor said:
> 
> 
> > http://74.125.155.132/search?q=cach...ersons&single=Single&cd=1&hl=en&ct=clnk&gl=ca
> ...


 I don't think so. Try retrieving some results. This link is only slightly better: http://web.archive.org/web/20071015025839/http://www.worldcubeassociation.org/results/events.php

I'm sure they have backups.


----------



## fundash (Nov 16, 2009)

OMG NOO!!!
..b..bu..but ....w..what about meh timez?!?!


----------



## V-te (Nov 16, 2009)

Ok, we're screwed. Glad I haven't competed.


----------



## fundash (Nov 16, 2009)

V-te said:


> Ok, we're screwed. Glad I haven't competed.



omg, i would say usualy un-lucky..but in this case:
OMFG MAN UR SO LUCKY!!!


----------



## nitrocan (Nov 16, 2009)

fundash said:


> V-te said:
> 
> 
> > Ok, we're screwed. Glad I haven't competed.
> ...



The PBs are still there.


----------



## Bob (Nov 16, 2009)

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

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


----------



## Tyson (Nov 16, 2009)

73,380 to be exact...

Seriously though... I'm sure this error has happened time and time again. Who the hell writes a database program like this, knowing that a short statement can kill everything, and then doesn't have a bright red undo button somewhere?

Though, I guess it's like unix and the rm command. Not for n00bs.

It should be fixed this afternoon in the United States. I owe Ron the biggest scoop of ice cream known to man.


----------



## LewisJ (Nov 16, 2009)

Bob said:


> 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 (Nov 16, 2009)

Nooo!!! My awesome 5x5 solves.

Idc about everything else.


----------



## IamWEB (Nov 16, 2009)

*screenshots*


----------



## shelley (Nov 16, 2009)

Relax, everyone. Backups exist for situations like this.


----------



## JBCM627 (Nov 16, 2009)

LewisJ said:


> 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 (Nov 16, 2009)

Tyson said:


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



Well, In short , delete works fine in SQL


----------



## Swoncen (Nov 16, 2009)

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


----------



## Erik (Nov 16, 2009)

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 (Nov 16, 2009)

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 (Nov 16, 2009)

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 (Nov 16, 2009)

Edmund said:


> 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 (Nov 16, 2009)

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 (Nov 16, 2009)

Sounds like Ron has everything under control.


----------



## nitrocan (Nov 16, 2009)

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


----------



## Hyprul 9-ty2 (Nov 16, 2009)

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


----------



## LewisJ (Nov 16, 2009)

JBCM627 said:


> LewisJ said:
> 
> 
> > 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.
> ...



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


----------



## Stefan (Nov 16, 2009)

JBCM627 said:


> 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 (Nov 16, 2009)

StefanPochmann said:


> JBCM627 said:
> 
> 
> > But the WCA database _isn't_ a relational database, which is how this happened. All results are in one giant table.
> ...



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 (Nov 16, 2009)

Thanks for the info Ron.


----------



## AvGalen (Nov 16, 2009)

StefanPochmann said:


> JBCM627 said:
> 
> 
> > But the WCA database _isn't_ a relational database, which is how this happened. All results are in one giant table.
> ...


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)


----------



## pjk (Nov 16, 2009)

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 (Nov 16, 2009)

AvGalen said:


> 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
> ...



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 (Nov 16, 2009)

ctrl+z?


----------



## Stefan (Nov 16, 2009)

AvGalen said:


> StefanPochmann said:
> 
> 
> > JBCM627 said:
> ...



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 (Nov 16, 2009)

Bob said:


> 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 (Nov 16, 2009)




----------



## Swordsman Kirby (Nov 16, 2009)

That is quite an accurate portrait of Tyson.


----------



## Swoncen (Nov 16, 2009)

DavidWoner said:


>



ROF2L!!!


----------



## hawkmp4 (Nov 16, 2009)

Oh that comic made my day.


----------



## theretardedcuber (Nov 16, 2009)

Swoncen said:


> DavidWoner said:
> 
> 
> >
> ...




LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLOOOOOOOOOOOOOOLZ


----------



## Tyson (Nov 16, 2009)

Thanks guys.

Some faint memory of order has been re-established.


----------



## TheBB (Nov 16, 2009)

Thanks Tyson, this made my day.


----------



## Carrot (Nov 16, 2009)

theretardedcuber said:


> Swoncen said:
> 
> 
> > DavidWoner said:
> ...




ROF2L!!!!!


----------



## Ron (Nov 16, 2009)

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

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


----------



## Nukoca (Nov 16, 2009)

Ron said:


> 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 (Nov 16, 2009)

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 (Nov 16, 2009)

Ron said:


> 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 (Nov 16, 2009)

DavidWoner said:


>



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


----------



## wrbcube4 (Nov 16, 2009)

I just made that my desktop background. hehe


----------



## JBCM627 (Nov 16, 2009)

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.


----------



## Stefan (Nov 16, 2009)

Erik said:


> 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


Do you think this is wrong? What should it be?


----------



## Kian (Nov 16, 2009)

StefanPochmann said:


> Erik said:
> 
> 
> > 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
> ...



Erik has the best single. He should rank 1, not 12.


----------



## JL58 (Nov 16, 2009)

Erik who?


----------



## miniGOINGS (Nov 16, 2009)

Musli4brekkies said:


> ctrl+z?



Yes. Just, yes.


----------



## Edmund (Nov 16, 2009)

JL58 said:


> Erik who?



Erik Erikson 



Spoiler



Erik Akkersdijk


----------



## Stefan (Nov 16, 2009)

JBCM627 said:


> 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?


You mean when computers and the internet are much more powerful than today?



JBCM627 said:


> At what point is it time to improve things?


When things aren't good enough anymore. Or when they won't be good enough anymore in the foreseeable future.



JBCM627 said:


> Each competition could have its own table


Wow that is ugly. Plus, you think we can do the database's job better than it? Plus, for views other than single competition you want me to send hundreds of queries to hundreds of tables? Yeah right.



JBCM627 said:


> 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!)


1. I don't see how that would lose half the columns. Probably cause it's not clear what you mean.
2. I don't think 50% are worth the ugliness. Unless we really have a speed problem. Which we don't.



JBCM627 said:


> and can be queried for individual competition results significantly faster


I just tested the 2009 world championship page, I think that's the largest competition. The query took 0.0437 seconds. Why do you think that needs to be faster?



JBCM627 said:


> This also essentially eliminates the chance of a fatal mistake like Tyson's


Not really.



JBCM627 said:


> 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.


So what? Big deal. Btw, we do already have a persons table, and the redundancy is only for efficiency reasons.



JBCM627 said:


> Assigning numeric uids means even WCAIDs can be changed by updating only 1 row.


No idea what you mean. Uids for what?



JBCM627 said:


> 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.


Um, that's the case already?

Summary: You want to make things ugly, contrary to the spirit of databases, to solve a problem that isn't there.


----------



## Stefan (Nov 17, 2009)

Kian said:


> StefanPochmann said:
> 
> 
> > Erik said:
> ...


If you say that because of his 7.08, that was not "since Oct 23, 2008".


----------



## waffle=ijm (Nov 17, 2009)

Edmund said:


> JL58 said:
> 
> 
> > Erik who?
> ...



fixed that spoiler for you.


----------



## Tim Major (Nov 17, 2009)

^^^^^LOL^^^^^
Ownage.

Yes, quite a few records are still wrong, but I'm sure they'll soon be fixed. 

Edit: This was aimed at Stefan's comment.

Edit 2: No, only the stats page.


----------



## Stefan (Nov 17, 2009)

ZB_FTW!!! said:


> Yes, quite a few records are still wrong


Other than on the statistics page?


----------



## Edmund (Nov 17, 2009)

waffle=ijm said:


> Edmund said:
> 
> 
> > JL58 said:
> ...



Ouch, I feel like a total ignoramus.


----------



## Stefan (Nov 17, 2009)

ZB_FTW!!! said:


> Edit 2: No, only the stats page.


If that's an answer to my clarification request... please don't go back to edit your posts to answer a question asked below. I almost missed it.


----------



## Tim Major (Nov 17, 2009)

StefanPochmann said:


> ZB_FTW!!! said:
> 
> 
> > Edit 2: No, only the stats page.
> ...



Oops, sorry. I was trying a little too hard to keep my post count down.


----------



## Stefan (Nov 17, 2009)

ZB_FTW!!! said:


> Oops, sorry. I was trying a little too hard to keep my post count down.


Why? Please don't tell me you're listening to the idiots who act like double-posting is a sin. Oh gosh... I just searched a little and saw you're constantly apologizing for double-posting when you really shouldn't. Sigh.


----------



## miniGOINGS (Nov 17, 2009)

StefanPochmann said:


> ZB_FTW!!! said:
> 
> 
> > Oops, sorry. I was trying a little too hard to keep my post count down.
> ...



Just a little question for you Stefan, do you recall ever double-posting yourself?


----------



## dannyz0r (Nov 17, 2009)

He's done it tons of times.


----------



## DavidWoner (Nov 17, 2009)

ZB_FTW!!! said:


> StefanPochmann said:
> 
> 
> > ZB_FTW!!! said:
> ...



Posts in off-topic do not count towards your post count.


----------



## Stefan (Nov 17, 2009)

miniGOINGS said:


> Just a little question for you Stefan, do you recall ever double-posting yourself?


Sure. Happens often. Most recent example on the previous page of this thread. I also edit often, but if I have something significant to add and it's been more than a few seconds since my previous post, then there's a considerable risk that people who already saw the original post would miss the edit. Hence I often double post. And it really pisses me off when people get attacked for clearly legitimate double posts. Well, I *don't* recall people attacking *me* for it, I assume that's because they're not only morons but also cowards.


----------



## Bob (Nov 17, 2009)

Pochmann, I missed you!


----------



## cmhardw (Nov 17, 2009)

Bob said:


> Pochmann, I missed you!



I was thinking the same thing, Stefan's back! 

Chris


----------



## miniGOINGS (Nov 17, 2009)

StefanPochmann said:


> miniGOINGS said:
> 
> 
> > Just a little question for you Stefan, do you recall ever double-posting yourself?
> ...



Wow, so many things in that post scream truth.


----------



## Stefan (Nov 17, 2009)

StefanPochmann said:


> Summary: You want to make things ugly, contrary to the spirit of databases, to solve *a problem that isn't there*.


I take that last part back. I was thinking about the speed issue that Jim focused on and with which I disagree. But obviously we *do* have a problem with safety. Though I still disagree about how to fix that. I believe better backups and more backend functionality / less direct database access are the way to go, not tearing the data apart.


----------



## LewisJ (Nov 17, 2009)

JBCM - while your ideas sound great in certain ways and in writing, I must ask - have you ever done much work with optimizing databases? I guarantee your proposed structure would result in more server load than the current structure. Relational databases simply should not be fragmented as much as you propose - it makes it too difficult to search and select out of a broad range of items.

Regardless of what the current structure is, a good structure for it would have a table for competitors, competitions, events (3x3, 4x4, 3x3 BLD) , competition events (3x3 round 1 at worlds 09, 3x3 BLD at MIT 09), and results with relations between the tables. All competitors, all competitions, all events, all competition events, and all results in each single table with their proper relations between them. This makes almost any realistic foreseen selection quite easy to make.


----------



## Stefan (Nov 17, 2009)

After thinking some more about Jim's suggestion I think I understand it now, parts I should've understood right away. I still don't like it, but I apologize for being gruff and not thinking about it enough before replying.

Lewis: What you describe as good structure is pretty much how it's currently done, except we don't have your "competition events" table but name competition+event+round in the results table for each result. If we had such a table, I'd btw call it "rounds", with the understanding that a row really refers to a specific round (i.e. in a specific competition and event). I think I actually pondered about this but dismissed it, though I don't quite remember why. Probably a mix of not necessary, not worth it, and never change a running system.


----------



## LewisJ (Nov 17, 2009)

Rounds is definitely a better term, I thought of the idea but couldnt come up with a good name for it. And I do see how it can be decided against - I didnt even think of it until after a minute or two of pondering. It would only be helpful in certain cases but complicates things and thus it really is unnecessary, I just like to be very complete with DB schemas 

And as for never change a running system...tell that to half the internet


----------



## qqwref (Nov 17, 2009)

StefanPochmann said:


> Lewis: What you describe as good structure is pretty much how it's currently done, except we don't have your "competition events" table but name competition+event+round in the results table for each result.


Just wondering, but does this mean that the competition names and event names are duplicated many times in the results table?


----------



## Stefan (Nov 17, 2009)

StefanPochmann said:


> JBCM627 said:
> 
> 
> > 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.
> ...


Gah, not true. Another big reason are the people who compete for the first time, they make us store their name in addition to their id because at first they don't have an id yet, and the mapping to existing or new ids isn't quite trivial, thanks to typos, different persons with the same name, and persons changing their names.


----------



## Stefan (Nov 17, 2009)

qqwref said:


> StefanPochmann said:
> 
> 
> > Lewis: What you describe as good structure is pretty much how it's currently done, except we don't have your "competition events" table but name competition+event+round in the results table for each result.
> ...


Not their names, but their IDs. Poor use of the verb "name", sorry, maybe "reference" would be better.


----------



## LewisJ (Nov 17, 2009)

qqwref said:


> StefanPochmann said:
> 
> 
> > Lewis: What you describe as good structure is pretty much how it's currently done, except we don't have your "competition events" table but name competition+event+round in the results table for each result.
> ...



Round name might be duplicated since there is no dedicated table (i suppose that's more support for having one but it doesn't matter too much either way) but competition name shouldnt be; results should have a column linking to the competition the result is from.
edit: semi-ninja'd :/


----------



## Stefan (Nov 17, 2009)

LewisJ said:


> Round name might be duplicated since there is no dedicated table


Actually, we do have a table for them, I think with attributes id+name+rank.


----------



## JBCM627 (Nov 17, 2009)

StefanPochmann said:


> Though I still disagree about how to fix that. I believe better backups and more backend functionality / less direct database access are the way to go, not tearing the data apart.


I agree that better backups and more backend will help. What I proposed would be taking it a bit far in terms of breaking things up, but I still think that it could help if implemented to a further extent than it is now.



LewisJ said:


> JBCM - while your ideas sound great in certain ways and in writing, I must ask - have you ever done much work with optimizing databases?


Not much, no. However, I have implemented a system similar to what I described in the live results system I made. It is filesystem based for portability, but since data is stored in a serialized array, this could (easily?) be transferred to a database structure. I'd like to get it to the point where I can do that, but probably won't until December at the soonest. Granted, live results are somewhat different, since the results system is designed for individual competitions.



LewisJ said:


> I guarantee your proposed structure would result in more server load than the current structure. Relational databases simply should not be fragmented as much as you propose - it makes it too difficult to search and select out of a broad range of items.


So for generating rankings, which is something you need to select a lot of items for, sure. But it would reduce the time needed to execute many other queries.



LewisJ said:


> Round name might be duplicated since there is no dedicated table (i suppose that's more support for having one but it doesn't matter too much either way) but competition name shouldnt be; results should have a column linking to the competition the result is from.


You can also more or less see the current schema in the dumps:
http://www.worldcubeassociation.org/results/wcadump200910.xlsx


----------



## keemy (Nov 17, 2009)

When I saw your blog entry about this earlier I loled a lot.


----------



## LewisJ (Nov 17, 2009)

StefanPochmann said:


> LewisJ said:
> 
> 
> > Round name might be duplicated since there is no dedicated table
> ...



Ah well thats what I meant by competition events...so I guess there IS a table for that. All is complete 

JBCM - the time difference in selecting 10 rows from a table of 75k and selecting 10 rows from a table of 100 is pretty much unmeasurable. SQL databases are quick even with tables with hundreds of thousands or millions of rows. Also, simple filesystem based storage is a bit different in what's efficient and what's not from an RDBMS. If I wanted to find the PBs and history of times of all events of a person (like what is displayed on a person's page) with your proposed one-table-per-event structure...I'd need a hell of a lot of queries. The nature of an RDBMS makes minimizing query count a reasonably high priority; there is a certain amount of overhead associated with each query in checking permissions and locking the involved tables for a read. With the current system, it all takes just a couple queries (and if you wanna be extra ninjalike, it can be done with just one if you throw on a good bit of serverside (PHP or whatever) processing and sorting).


----------



## Tim Major (Nov 17, 2009)

DavidWoner said:


> ZB_FTW!!! said:
> 
> 
> > StefanPochmann said:
> ...



Basically, I have about 8 posts a day. I didn't consider that this was off-topic. I'm not trying to stop double posting, as much as I'm trying to stop being a post whore.


----------



## Stefan (Nov 17, 2009)

LewisJ said:


> StefanPochmann said:
> 
> 
> > LewisJ said:
> ...


I doubt that. Your examples were 3x3 round 1 at worlds 09, 3x3 BLD at MIT 09. Our table only has nine rows, for example id='1', name="First round", rank=20. See the xlsx file. It rather describes the round *type*, not a specific round in some specific competition in some specific event.


----------



## hr.mohr (Nov 17, 2009)

StefanPochmann said:


> Kian said:
> 
> 
> > StefanPochmann said:
> ...



If only records after Oct. 23, 2008 are counted then Tomasz should be in first place and not second with his 7.91. Right?


----------



## Erik (Nov 17, 2009)

StefanPochmann said:


> Kian said:
> 
> 
> > StefanPochmann said:
> ...



Yah I misread there, but it instantly rises the question: 
Why would it be convenient to see this rankings since October 23 2008??? What's the idea behind this? Shouldn't it display the current combined rankings then? Nonetheless it should say 2nd soon then with 8.33...
Weird weird weird

@hrMohr: it says on the top of the page that it's generated on the end of October 2009, Fuglsang was on 31 October/1 November


----------



## hr.mohr (Nov 17, 2009)

@Erik: yeah, missed that. Also I think that you forgot about Kittikorn's 8.25 

Oct. 23 seems kinda arbitrary. Why not display the all time rankings and the season bests just like they do in athletics?


----------



## LewisJ (Nov 17, 2009)

StefanPochmann said:


> I doubt that. Your examples were 3x3 round 1 at worlds 09, 3x3 BLD at MIT 09. Our table only has nine rows, for example id='1', name="First round", rank=20. See the xlsx file. It rather describes the round *type*, not a specific round in some specific competition in some specific event.



Ah OK, makes sense.


----------



## KwS Pall (Nov 17, 2009)

I think you only need 3 tables:
Competitors (WCA ID,Name,Surname etc)
Competitions (Comp ID,Name,Venue,Date etc)
Results (WCA ID,Comp ID, Event, Round, Time) (ie '2008CABA03','Essen09','444','1','50.27')

Is that correct? or have I missed something?

Any further ideas?


----------



## LewisJ (Nov 17, 2009)

KwS Pall said:


> I think you only need 3 tables:
> Competitors (WCA ID,Name,Surname etc)
> Competitions (Comp ID,Name,Venue,Date etc)
> Results (WCA ID,Comp ID, Event, Round, Time) (ie '2008CABA03','Essen09','444','1','50.27')
> ...



This would end up in a lot of repeated data for events and rounds and such; the events and round type tables cut much of this out. 

You only NEED 1 table for the whole thing, but that's even more spreadsheet-like and less database-like than doing it with 2, etc for 3, but with 5 it's taking advantage of how relational databases work to store less repeated data and make selecting specific bits of that data easier.


----------



## jazzthief81 (Nov 17, 2009)

Erik said:


> Yah I misread there, but it instantly rises the question:
> Why would it be convenient to see this rankings since October 23 2008??? What's the idea behind this? Shouldn't it display the current combined rankings then? Nonetheless it should say 2nd soon then with 8.33...
> Weird weird weird



The ranking is based on all results over the past year (notice how the page hasn't been regenerated since 23rd October 2009). 

The idea behind it is that it gives a good idea of who are the best all round cubers *on current form*.


----------



## KwS Pall (Nov 17, 2009)

LewisJ said:


> KwS Pall said:
> 
> 
> > I think you only need 3 tables:
> ...



how do you repeat data in these tables? just give me an example


----------



## tim (Nov 17, 2009)

KwS Pall said:


> LewisJ said:
> 
> 
> > KwS Pall said:
> ...



Events and rounds are repeated, like he said. And i've learnt that using a string as a (foreign) key isn't the best idea performance-wise.

I've seen someone arguing about performance: Cubemania's times table has about 1.1 million entries (user_id, time, date, scramble, puzzle_id, average_id) and selecting the last 100 solves of person x takes only a few milliseconds. It's just a matter of proper indices.


----------



## LewisJ (Nov 17, 2009)

KwS Pall said:


> KwS Pall said:
> 
> 
> > I think you only need 3 tables:
> ...



Basically, every single result (all 75k of them) would have to store:
-Name of the round it is from ("Combined Final" "First Round" etc)
-Name of the event ("3x3 Multi BLD" "Master Magic" etc)
Rather than:
-ID of the round type (1, 2, 3, 4, 5, 6, 7)
-ID of the event (1, 2, 3, ...however many events there are)

And as tim said, using strings (varchars, chars, whatever you choose) as a foreign key is rather suboptimal performancewise (thus you wouldnt use WCA ID or competition name for the primary key; they would be a unique keys, but a simple integer index would be much faster); same goes for matching specific strings in a SELECT; an integer key for event linked to a table of possible events not only reduces data redundancy but also improves SELECT performance when you have conditions related to events and rounds (which pertains to a significant portion of SELECT queries in this case)


----------



## AvGalen (Nov 17, 2009)

tim said:


> KwS Pall said:
> 
> 
> > LewisJ said:
> ...


 
Data would surely be repeated because in every round there are several solves (2 or 3 for blind, 3 for megaminx, 5 for most others)

Having lots of small tables for similar data is a horrible idea and doesn't benefit performance. It does duplicate a lot of information (table-structure) and makes writing queries really ugly (select * from "a union of all tables that have a name that starts with results_" :fp)
Having less columns in a table is only important if you have a database that doesn't understand "sparse columns" (storage) and if you use "select *" instead of "select col1, col2, col3" type queries (memory/cpu)

I think the sum of 345ranks should be based on all results ever, not on all results from last year. What if someone did really well in the past, but hasn't been participating in a year? Adding a "recent" section could be an option like it has been for longest blindfolded succes streak 

Why is nobody reacting to the use of a transactional database? That would have made this whole situation a minor annoyance that could have been solved in a couple of minutes
Why are cubes now named "4x4"? I can imagine calling a cube "4" or "4x4x4" but "4x4" is just stupid.
And finally, why aren't statistics and records recalculated automatically after entering data from a competition? It seems like all queries on the results table are select-queries untill new competition data is entered and at that moment all pre-calculated data should be renewed as well. Or is recalculating that data really such a long process?


----------



## Swordsman Kirby (Nov 17, 2009)

AvGalen said:


> And finally, why aren't statistics and records recalculated automatically after entering data from a competition? It seems like all queries on the results table are select-queries untill new competition data is entered and at that moment all pre-calculated data should be renewed as well. Or is recalculating that data really such a long process?



What's interesting is that my records table has my 5x5 PB from the last competition, but it doesn't have my clock records.


----------



## hr.mohr (Nov 17, 2009)

AvGalen said:


> Why is nobody reacting to the use of a transactional database? That would have made this whole situation a minor annoyance that could have been solved in a couple of minutes



OK I'll bite. 

phpMyAdmin uses auto commit so even if all tables where using innodb then the idiot in this case would still have made the fatal error.


----------



## LewisJ (Nov 17, 2009)

hr.mohr said:


> AvGalen said:
> 
> 
> > Why is nobody reacting to the use of a transactional database? That would have made this whole situation a minor annoyance that could have been solved in a couple of minutes
> ...



Exactly. Based on hearing foreign key thrown around by stefan and others it seems as though the database does use InnoDB tables, so it is *possible* to use transactions on it, but as you said, 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.


----------



## Dene (Nov 17, 2009)

StefanPochmann said:


> miniGOINGS said:
> 
> 
> > Just a little question for you Stefan, do you recall ever double-posting yourself?
> ...



And what's wrong with editing a post quickly to add something like:
"POST IN EDITING, WAIT BEFORE REPLYING"?

I don't see any reason to double post. (Dene refuses to be called a moron and a coward, and also feels like an argument... I bet he won't rise to the biat though  ).


----------



## Kian (Nov 17, 2009)

StefanPochmann said:


> Kian said:
> 
> 
> > StefanPochmann said:
> ...



My apologies, you are certainly correct. I misread.


----------



## AvGalen (Nov 17, 2009)

hr.mohr said:


> AvGalen said:
> 
> 
> > Why is nobody reacting to the use of a transactional database? That would have made this whole situation a minor annoyance that could have been solved in a couple of minutes
> ...


My mistake for not mentioning `a transactional database with a full recovery plan` as I did before.
Not only can transactions be rolled back, but you can also do `point in time´ recoveries. That basically means that Tyson writes down the date/time of the mistake and you restore the database untill 1 second before that mistake.


----------



## LewisJ (Nov 18, 2009)

Ah I see, wasn't thinking of point in time. Point in time recovery is very doable with MySQL's binary log provided everything is done with transactions. 
http://dev.mysql.com/tech-resources/articles/point_in_time_recovery.html
is a good article explaining how to setup and use it. 
*cough stefan ron etc cough*

Wouldn't it be funny if it HAD been setup like this and nobody here or among the WCA guys had thought to use it....


----------



## AvGalen (Nov 18, 2009)

LewisJ said:


> Ah I see, wasn't thinking of point in time. Point in time recovery is very doable with MySQL's binary log provided everything is done with transactions.
> http://dev.mysql.com/tech-resources/articles/point_in_time_recovery.html
> is a good article explaining how to setup and use it.
> *cough stefan ron etc cough*
> ...



excellent tutorial you linked to and exactly what I meant. Hopefully this is usable on a hosted server as well


----------



## Stefan (Nov 18, 2009)

Dene said:


> And what's wrong with editing a post quickly to add something like:
> "POST IN EDITING, WAIT BEFORE REPLYING"?


That doesn't help, does it? The people who have already loaded the page with your post on it will still miss your edit.

Will look at that Point-in-Time Data Recovery...


----------



## Dene (Nov 19, 2009)

And if you make your second post before they make their reply to the first, they will probably completely overlook that too. So it's a lose-lose situation. I know which option I prefer regardless.


----------



## AvGalen (Nov 30, 2009)

StefanPochmann said:


> Dene said:
> 
> 
> > And what's wrong with editing a post quickly to add something like:
> ...


I am curious about the outcome of this.

I also think something is still wrong with the database. I just checked http://www.worldcubeassociation.org/results/statistics.php and saw that it was "Generated on November 29 2009." yet I still see this: "Arnaud van Galen 47". But when I look at my profile page I can see 51 competitions just for 3x3x3. I think the statistics haven't been updated on november 29 after all


----------



## Stefan (Nov 30, 2009)

AvGalen said:


> StefanPochmann said:
> 
> 
> > Will look at that Point-in-Time Data Recovery...
> ...


Didn't get to that yet. I do have recent backups, though, and I guess Ron does, too.



AvGalen said:


> I also think something is still wrong with the database.


You're right, the statistics look old, something got mixed up. Btw, the last few days I've been working a lot on the statistics, to make them better and a lot faster. Almost finished with that, and then I'll clean up. I'll let you know when I'm done, probably later today.


----------



## Giacomo Itam (Nov 16, 2019)

Mauro è bello


----------



## Zeke Mackay (Nov 17, 2019)

Thanks for the 10 year bump


----------



## White KB (Nov 18, 2019)

Uh wiat waht
(wait what)
It's still up boooÿyyyyyyyyyyyyyyyyyy
anyway...


----------

