MySQL Bugs: #67535: LAST_INSERT_ID() and INSERT IGNORE behavior

Bug #67535	LAST_INSERT_ID() and INSERT IGNORE behavior
Submitted:	9 Nov 2012 13:41	Modified:	20 Nov 2012 8:09
Reporter:	Jonas Reinhardt	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.97, 5.1.67, 5.5.29, 5.7.0	OS:	Any (MS Windows, Linux)
Assigned to:		CPU Architecture:	Any

Description:
In the manual it is stated that
"If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is not incremented and LAST_INSERT_ID() returns 0, which reflects that no row was inserted."
http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id

I tested the below script on
5.1.59-community and  5.5.28-MariaDB-log 
which didn't return 0!

How to repeat:
USE test;

DROP TABLE IF EXISTS test_myisam;
DROP TABLE IF EXISTS test_innodb;
DROP TABLE IF EXISTS test_heap;
CREATE TABLE test_myisam (id int auto_increment, nr int, primary key(id), unique key (nr)) ENGINE MyISAM AUTO_INCREMENT=1000;
CREATE TABLE test_innodb (id int auto_increment, nr int, primary key(id), unique key (nr)) ENGINE InnoDB AUTO_INCREMENT=2000;
CREATE TABLE test_heap (id int auto_increment, nr int, primary key(id), unique key (nr)) ENGINE Heap AUTO_INCREMENT=3000;

INSERT IGNORE INTO test_myisam (nr) VALUES (9);
SELECT LAST_INSERT_ID() as mysiam1;
INSERT IGNORE INTO test_myisam (nr) VALUES (9);
SELECT LAST_INSERT_ID() as myisam2;

INSERT IGNORE INTO test_innodb (nr) VALUES (9);
SELECT LAST_INSERT_ID() as innodb1;
INSERT IGNORE INTO test_innodb (nr) VALUES (9);
SELECT LAST_INSERT_ID() as innodb2;

INSERT IGNORE INTO test_heap (nr) VALUES (9);
SELECT LAST_INSERT_ID() as heap1;
INSERT IGNORE INTO test_heap (nr) VALUES (9);
SELECT LAST_INSERT_ID() as heap2;

= = RESULT = = 
mysiam1
1000
myisam2
1000
innodb1
2000
innodb2
2000
heap1
3000
heap2
3000

Suggested fix:
Update database to correspond to manual or update manual to reflect function.

Same result with Oracle's 5.5.28 on Windows.

Thank you for the report.

Verified as described.

I think this is documentation issue. Also earlier manual says: "The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted." And for INSERT IGNORE it should say "If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is not incremented and LAST_INSERT_ID() returns previous incremented value, which reflects that no row was inserted. "

I'm not sure, but I think the LAST_INSERT_ID returned 0 before, in older versions.
The procedure I found the behavior is a couple of years old.

My implemented work-around is now:

SET @@session.last_insert_id=0; 
INSERT IGNORE ...
IF last_insert_id()=0 THEN ...

i can also confirm this on version 5.6.10 community/windows and
i am also not sure if this is a documentation issue. i can't find another way of indication wether insert ignore inserted a row or not. with the described functionality, this would make it more comfortably in a stored procedure.

I expanded the INSERTs/SELECTs as seen below.  Two things to note:

** ROW_COUNT() may be the 'right' way to determine if an INSERT took effect.  (Caution:  '2' can be returned by a single-row REPLACE; I don't know about UPDATE.)

** Notice how InnoDB, but not MyISAM and MEMORY, burns ids.

mysql> INSERT IGNORE INTO test_myisam (nr) VALUES (9);
mysql> SELECT LAST_INSERT_ID() as myisam1, ROW_COUNT();
+---------+-------------+
| myisam1 | ROW_COUNT() |
+---------+-------------+
|    1000 |           1 |
+---------+-------------+
mysql> INSERT IGNORE INTO test_myisam (nr) VALUES (9);
mysql> SELECT LAST_INSERT_ID() as myisam2, ROW_COUNT();
+---------+-------------+
| myisam2 | ROW_COUNT() |
+---------+-------------+
|    1000 |           0 |
+---------+-------------+
mysql> INSERT IGNORE INTO test_myisam (nr) VALUES (99);
mysql> SELECT LAST_INSERT_ID() as myisam3, ROW_COUNT();
+---------+-------------+
| myisam3 | ROW_COUNT() |
+---------+-------------+
|    1001 |           1 |
+---------+-------------+

mysql>
mysql> INSERT IGNORE INTO test_innodb (nr) VALUES (9);
mysql> SELECT LAST_INSERT_ID() as innodb1, ROW_COUNT();
+---------+-------------+
| innodb1 | ROW_COUNT() |
+---------+-------------+
|    2000 |           1 |
+---------+-------------+
mysql> INSERT IGNORE INTO test_innodb (nr) VALUES (9);
mysql> SELECT LAST_INSERT_ID() as innodb2, ROW_COUNT();
+---------+-------------+
| innodb2 | ROW_COUNT() |
+---------+-------------+
|    2000 |           0 |
+---------+-------------+
mysql> INSERT IGNORE INTO test_innodb (nr) VALUES (99);
mysql> SELECT LAST_INSERT_ID() as innodb3, ROW_COUNT();
+---------+-------------+
| innodb3 | ROW_COUNT() |
+---------+-------------+
|    2002 |           1 |
+---------+-------------+
1 row in set (0.00 sec)

mysql>
mysql> INSERT IGNORE INTO test_heap (nr) VALUES (9);
mysql> SELECT LAST_INSERT_ID() as heap1, ROW_COUNT();
+-------+-------------+
| heap1 | ROW_COUNT() |
+-------+-------------+
|  3000 |           1 |
+-------+-------------+
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO test_heap (nr) VALUES (9);
mysql> SELECT LAST_INSERT_ID() as heap2, ROW_COUNT();
+-------+-------------+
| heap2 | ROW_COUNT() |
+-------+-------------+
|  3000 |           0 |
+-------+-------------+
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO test_heap (nr) VALUES (99);
mysql> SELECT LAST_INSERT_ID() as heap3, ROW_COUNT();
+-------+-------------+
| heap3 | ROW_COUNT() |
+-------+-------------+
|  3001 |           1 |
+-------+-------------+
1 row in set (0.00 sec)