Wednesday, 8 July 2009

Wicked bug in MySQL "non nullable" column

We pulled our hair off on that bug for a while until we figured what was wrong with MySQL in that instance.
In a InnoDB database, we have a table tbl_demo with a integer column "rank", non nullable and defaulting to 0. We have an after insert trigger in this column. The trigger calculates a derived value and updates the column "rank".

When we upgraded MySQL from version 5.0.51a to version 5.1.30, the UPDATE statement started to fail. After a bit of trouble shouting, we released that this is actually a bug of MySQL which in the earlier version was not enforcing the column definition. This was working whereas it should not have ever worked....
Anyway, the proper way to have this work on both MySQL 5.0.51a and 5.1.30 is to write the SQL Update statement as follows:
update tbl_demo set rank=IFNULL(new_value_for_rank,0) where id=123;

The use of the IFNULL makes the code work with the bug fix in MySQL.
A simple workaround for quite an annoying behavior....

No comments: