Ruby, Rails, Firefox, Anime, Mac
In: Fun22 Dec 2006
Just noticed an insidious “bug” in MySQL when you use “AND”s instead of commas to separate the fields you are updating in your SET clause. Being a little rusty in SQL, I entered something like the query below while trying to make a quick fix to some records:
UPDATE characters SET alignment = 'Neutral Evil' AND last_name = 'Majere' WHERE first_name = 'Raistlin'
Much to my consternation, the record appeared to have disappeared when I tried to query for it. After much bewildered cursing, I came across MySQL bug #15928: update syntax error not detected (this is not an actual bug though). As it turns out, MySQL does a logical AND and the result is that the ‘alignment’ field is set to ‘0’ since
'Neutral Evil' AND last_name = 'Majere' results in
false. And MySQL casts it to ‘0’. Grrr. Luckily I was able to revert the changes by finding the record again with the new ‘0’ alignment value (was using an auto-committing MySQL GUI client, no ROLLBACK available).