MySQL delete issue with NULL

Question

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?

Answer 1

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.