MySQL UPDATE behavior

Question

mysql> update tablename set fieldname = 'C200900674' where fieldname - 'C200900673';

ERROR 1062 (23000): Duplicate entry 'C200900674-2008-0-1' for key 1

Any thoughts or suggestions on this? We had someone accidentally do that update with a minus sign instead of an equal sign. It apparently tried to change all records less than that value? Even though it's alphanumeric and really fairly incomplete. On top of that, a quantity of records were updated before it got that error and there was no feedback at all. Nothing like "Query OK, X rows affected (0.00 sec)" so we had no idea how many were changed. autocommit=1 so no ability to rollback.

Anyway, just looking for any tips or pointers on this. Why that query did anything at all, it really looks like it should have returned an error to me. Other than the obvious answer of not letting inexperienced admins do boneheaded things of course.

Thanks

Answer 1

If your table is using the InnoDB storage engine, no harm was done. Even with autocommit=1 the query is only executed "all-or-nothing". The fact that you got an ERROR message is proof that the DB did not touch your data. Whenever you get an ERROR, the "x rows affected" message is omitted.

Even in case the uniquess constraint would not have been violated the query would have failed with a different error:

ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'fieldname'

This is because the minus sign would have caused MySQL to try and calculate the value of the field contents minus something else. This does not work. You only did not see this error, because the other one "got there first".

Answer 2

Whenever there's a doubt of how Mysql interpreted a WHERE clause, reverse it to a SELECT.

SELECT fieldname - 'C200900673' FROM tablename;

And:

SELECT fieldname FROM tablename WHERE fieldname - 'C200900673';

See what value is returned by the first select, and what rows the second one find.

Sadly, since Mysql is pretty lax on numeric/string conversions, specially on the 4.x series, and even on non-strict 5.x, heck... even strict-moded, it's hard to tell exactly what went wrong without all the details of your Mysql configuration. It could be because fieldname was cast to some number, and so was 'C200900673', basically running:

update tablename set fieldname = 'C200900674' where NUMBER - NUMBER;

Which could translate to:

update tablename set fieldname = 'C200900674' where 1;

Anyway, I hope you have a backup!