Description:
The manual states the following:
>> http://dev.mysql.com/doc/mysql/en/information-functions.html <<
"If you use INSERT IGNORE and the record is ignored, the AUTO_INCREMENT counter still is incremented and LAST_INSERT_ID() returns the new value."
This is true for 4.0, but is no longer true for 4.1, which returns 0 in this case.
Personally, I think it should return NULL (in all versions) if an INSERT IGNORE fails to insert a row, but that would likely break replication if it were changed at this point. The change between versions should at least be documented.
How to repeat:
On 4.0:
>>>>>
mysql> create table test_insert (id int not null auto_increment, x int, primary key(id), unique(x));
Query OK, 0 rows affected (0.01 sec)
mysql> insert ignore into test_insert (x) values (5);
Query OK, 1 row affected (0.00 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> insert ignore into test_insert (x) values (5);
Query OK, 0 rows affected (0.00 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
mysql> insert ignore into test_insert (x) values (5);
Query OK, 0 rows affected (0.01 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
<<<<<
On 4.1:
>>>>>
mysql> create table test_insert (id int not null auto_increment, x int, primary key(id), unique(x));
Query OK, 0 rows affected (0.01 sec)
mysql> insert ignore into test_insert (x) values (5);
Query OK, 1 row affected (0.30 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.02 sec)
mysql> insert ignore into test_insert (x) values (5);
Query OK, 0 rows affected (0.00 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
<<<<<
Suggested fix:
Add proper documentation.
