| Well, you people are right about MySQL... |
by bwkaz |
2007-05-09 10:31:47 |
(Those of "you people" that say "it's crap", that is.)
I have a database that's used to store weekly scores for our church golf league, and calculate points, etc. And it's in MySQL, mostly because that was what I had installed when I first set it up. There was a bug in the client program that had set the date of the first week of the year incorrectly, and that combined with some odd code elsewhere in the program to make the second week's points wrong (mostly because the handicaps were calculated incorrectly due to the wrong date).
Anyway, to make a long story shorter, the Points column and the Adjustment column in the RoundScoreByHole table had the wrong values for almost every row on a certain week (there's a foreign key in this table to the Round table and the Hole table, and the Round table has a FK to the Week table (where the week's date is kept) and the Players table). So after recalculating the correct points values using the correct handicaps, I went into the mysql client to fix the tables, so the output HTML files would be correct.
After about an hour of "update RoundScoreByHole set Points = x where RoundScoreByHole.Key = y" for lots of values of y (many times with ORed-together criteria to update multiple records at once), I stopped paying attention to what I was typing in before hitting enter. And after a bit longer, I did this:
update RoundScoreByHole set Points = 1 where RoundScoreByHole.Key = 3341 or 3347;
and hit return. After I saw the "matched 3370-whatever rows, updated 2000-some" message, I realized my mistake -- I had forgotten the "RoundScoreByHole.Key = " part of the criteria.
And instead of choking on the bad syntax (or at least, I hope that's bad syntax; if not, then this isn't a MySQL rant but a general SQL rant), MySQL "helpfully" decided I meant "true" instead of "3347", so it "helpfully" matched *every* *single* *row* in the table. And it broke almost every one of them. (A few were supposed to have a Points value of 1, but most were not. Valid points values for an individual player/week/hole combination are 0, 1, and 2; most rows had 0 or 2 in them.)
GRRRRRR!!
And of course there aren't any backups of the previous years (whose records all got broken), so I can't restore from them. I have to write some other program to go in and recalculate the correct points based on the matchups, scores, and adjustments.
(The adjustment comes from the handicap difference: if the difference is N, then the person with the higher handicap will get an extra stroke on N holes. If N is greater than 9, they get multiple strokes on some holes. And the holes that they get strokes on are determined by the hole's handicap value (assigned by the course) -- low-handicap holes get strokes first. Anyway, the important bit is that the info I need is still in the table, it'll just be hard to fix 2000 rows manually.) |
|
[ Reply ] |
|
ouchie | by dennismv | 2007-05-09 10:37:23 |
|
Eeeew, Office... :-P | by bwkaz | 2007-05-09 10:47:29 |
|
ya | by dennismv | 2007-05-09 11:02:23 |
|
Sounds like it could be done with SQL | by Arachnid | 2007-05-09 13:23:47 |
|
one liner ? | by dennismv | 2007-05-09 14:22:06 |
|
Sure. CASE WHEN blah THEN foo ELSE bar END (n/t) | by Arachnid | 2007-05-09 14:58:43 |
|
mm thanks ! learn something new every day :) (n/t) | by dennismv | 2007-05-09 15:27:05 |
|
Eh, you may be right, but it's done already. :-) | by bwkaz | 2007-05-09 16:07:27 |
|
Not using transactions? | by rfrovarp | 2007-05-09 10:43:33 |
|
So it's a MySQL thing then. I suspected as much. | by bwkaz | 2007-05-09 10:52:33 |
|
You *should* be backing up before mucking about ;) (n/t) | by kahuana | 2007-05-09 10:55:30 |
|
... Point taken. :-) (n/t) | by bwkaz | 2007-05-09 11:02:25 |
|
Just enter 'BEGIN;' before doing anything. | by Arachnid | 2007-05-09 12:57:41 |
|
That's SQL for you. | by CynicalRyan | 2007-05-09 10:48:19 |
|
Doesn't work on Oracle or Postgres (n/t) | by rfrovarp | 2007-05-09 10:53:08 |
|
Then file a bug report. It should. ;) (n/t) | by CynicalRyan | 2007-05-09 10:54:07 |
|
ERROR: argument of OR must be type boolean, | by rfrovarp | 2007-05-09 10:57:26 |
|
Yeah, those errors are what I would have expected. (n/t) | by bwkaz | 2007-05-09 11:02:06 |
|
Hm, gotta do some digging, then. (n/t) | by CynicalRyan | 2007-05-09 11:08:05 |
|
postgres can't convert bool to int or int to bool | by Ston | 2007-05-09 11:17:18 |
|
Yeah, thought it is implementation vs standard. | by CynicalRyan | 2007-05-09 11:19:06 |
|
ora-00920 | by rfrovarp | 2007-05-09 11:34:41 |
|
Sorry was at lunch | by Ston | 2007-05-09 13:31:20 |
|
Expression | by rfrovarp | 2007-05-09 13:33:39 |
|
*sigh* While it may be a "tragedy", | by kahuana | 2007-05-09 10:48:56 |
|
More "just an annoyance". But you're right. :-) (n/t) | by bwkaz | 2007-05-09 11:14:40 |
|
I disagree | by Arachnid | 2007-05-09 12:59:50 |
|
MySQL is very userfriendly | by subbywan | 2007-05-09 11:40:24 |
|
Useful syntax for future reference: | by Arachnid | 2007-05-09 13:00:27 |
