Jason Fox

I recently came across an annoying bug in MySQL v5.1 (also in 6.0 apparently) that bit me hard, so, I thought I’d post on it in case you are being bitten by the same bug.

If you attempt to update a column that does not allow NULL to NULL, MySQL will set the column’s value to the default value for that column’s data type.  This is true only when you are not running MySQL strict mode.  Here’s an example to illustrate.

mysql> create table null_test (id int not null unique(id), name varchar(25)
null default null);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table null_test;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                           |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| null_test | CREATE TABLE `null_test` (
  `id` int(11) NOT NULL,
  `name` varchar(25) default NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into null_test (id, name) values (1, 'Jane');
Query OK, 1 row affected (0.00 sec)

mysql> update null_test set id = null;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from null_test where name = 'Jane';
+----+------+
| id | name |
+----+------+
|  0 | Jane |
+----+------+
1 row in set (0.00 sec)

More information about this bug can be found in the bug report submitted Janurary 4, 2008.