MySQL go home you're drunk #12476424

5.5.output.sql

mysql> INSERT INTO hosts (site_id, hostname, created_at, updated_at) VALUES (NULL, 'test.me', '2014-01-01', '2014-01-01');
ERROR 1048 (23000): Column 'site_id' cannot be null
mysql> INSERT INTO hosts (site_id, hostname, created_at, updated_at) VALUES (1, 'test.me', '2014-01-01', '2014-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE `hosts` SET `site_id` = NULL WHERE `hosts`.`site_id` = 1 AND `hosts`.`id` IN (1)
    -> ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from hosts;
+----+---------+----------+------+-------+------------+---------------------+---------------------+------------+---------+
| id | site_id | hostname | ttl  | cname | live_cname | created_at          | updated_at          | ip_address | has_aka |
+----+---------+----------+------+-------+------------+---------------------+---------------------+------------+---------+
|  1 |       0 | test.me  | NULL | NULL  | NULL       | 2014-01-01 00:00:00 | 2014-01-01 00:00:00 | NULL       |    NULL |
+----+---------+----------+------+-------+------------+---------------------+---------------------+------------+---------+
1 row in set (0.00 sec)

mysql>

5.6.output.sql

mysql> INSERT INTO hosts (site_id, hostname, created_at, updated_at) VALUES (NULL, 'test.me', '2014-01-01', '2014-01-01');
ERROR 1048 (23000): Column 'site_id' cannot be null
mysql> INSERT INTO hosts (site_id, hostname, created_at, updated_at) VALUES (1, 'test.me', '2014-01-01', '2014-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from hosts;
+----+---------+----------+------+-------+------------+---------------------+---------------------+------------+---------+
| id | site_id | hostname | ttl  | cname | live_cname | created_at          | updated_at          | ip_address | has_aka |
+----+---------+----------+------+-------+------------+---------------------+---------------------+------------+---------+
|  1 |       1 | test.me  | NULL | NULL  | NULL       | 2014-01-01 00:00:00 | 2014-01-01 00:00:00 | NULL       |    NULL |
+----+---------+----------+------+-------+------------+---------------------+---------------------+------------+---------+
1 row in set (0.00 sec)

mysql> UPDATE `hosts` SET `site_id` = NULL WHERE `hosts`.`site_id` = 1 AND `hosts`.`id` IN (1);
ERROR 1048 (23000): Column 'site_id' cannot be null

hosts.sql.output

mysql> describe hosts;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| site_id    | int(11)      | NO   | MUL | NULL    |                |
| hostname   | varchar(255) | NO   | UNI | NULL    |                |
| ttl        | int(11)      | YES  |     | NULL    |                |
| cname      | varchar(255) | YES  |     | NULL    |                |
| live_cname | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
| ip_address | varchar(255) | YES  |     | NULL    |                |
| has_aka    | tinyint(1)   | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

mySQL 5.5 (top) will allow an UPDATE to NULL in a NOT NULL field. In fact, it will set it to 0.
mySQL 5.6 (bottom) doesn't allow this bug.