MySQL delete issue with NULL
mysql
Solution
Aha, it's not a bug, it's a feature.
`sql_auto_is_null`:
If this variable is set to 1 (the default), then after a statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form: SELECT * FROM tbl_name WHERE auto_col IS NULL
The default has been changed from 1 to 0 in >= 5.5.3
It is mentioned in MySQL's bug database, but that was someone equally unaware as us of this feature.
Problem
We have found an issue with deleting records, which seems to be down to specific versions of MySQL. However I can't find any mention of this issue to know when it was fixed. Some code was trying to do a delete where the primary key is NULL (which should never occur), with code generated from a framework. MySQL deleted a previously inserted row, with a valid not null primary key. Can demonstrate the issue here. Test table:- ``` CREATE TABLE IF NOT EXISTS `fred_delete` ( `id` int(11) NOT NULL AUTO_INCREMENT, `test` varchar(25) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; ``` Then execute the following sql:- ``` INSERT INTO fred_delete (id, test) VALUES (NULL, 'a'), (NULL, 'b'), (NULL, 'c'), (NULL, 'd'), (NULL, 'e'), (NULL, 'f'), (NULL, 'g'); DELETE FROM fred_delete WHERE id IS NULL; SELECT * FROM fred_delete; ``` On 5.1.58-log mysql install this inserts 7 rows then deletes the first inserted row. On 5.6.12-log install this inserts 7 rows and doesn't delete any. Does anyone know what versions of MySQL are subject to this behavior? Or is this an issue with configuration?