MySQL Bugs: #16689: LAST_INSERT_ID() not stable within statement

Bug #16689	LAST_INSERT_ID() not stable within statement
Submitted:	20 Jan 2006 22:31	Modified:	31 Jan 2006 7:56
Reporter:	Paul DuBois	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.2 and up	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
LAST_INSERT_ID() returns the most recently generated
AUTO_INCREMENT value for the current connection.

However, multiple invocations of LAST_INSERT_ID() within
the same statement can return different values.  That is,
LAST_INSERT_ID() is not stable within a given statement
and cannot be relied on, for example, with multiple-row
INSERT statements.

LAST_INSERT_ID() was stable up through 4.1.1.  The behavior
became incorrect in 4.1.2, and remains incorrect in all higher
releases

How to repeat:
DROP TABLE IF EXISTS parent, child;
CREATE TABLE parent (
    p_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (p_id)
);

CREATE TABLE child (
    c_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    p_id SMALLINT UNSIGNED NOT NULL,
    KEY (c_id)
);

INSERT INTO parent VALUES (NULL);
INSERT INTO child VALUES
(NULL, LAST_INSERT_ID());

INSERT INTO parent VALUES (NULL);
INSERT INTO child VALUES
(NULL, LAST_INSERT_ID()),
(NULL, LAST_INSERT_ID());

INSERT INTO parent VALUES (NULL);
INSERT INTO child VALUES
(NULL, LAST_INSERT_ID()),
(NULL, LAST_INSERT_ID()),
(NULL, LAST_INSERT_ID());

INSERT INTO parent VALUES (NULL);
INSERT INTO child VALUES
(NULL, LAST_INSERT_ID()),
(NULL, LAST_INSERT_ID()),
(NULL, LAST_INSERT_ID()),
(NULL, LAST_INSERT_ID());

SELECT * FROM parent;
SELECT * FROM child;

These are the results prior to 4.1.2 (and these are the
results that I would expect):

+------+
| p_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
+------+------+
| c_id | p_id |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    2 |
|    4 |    3 |
|    5 |    3 |
|    6 |    3 |
|    7 |    4 |
|    8 |    4 |
|    9 |    4 |
|   10 |    4 |
+------+------+

These are the (incorrect) results from 4.1.2 and up:

+------+
| p_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
+------+------+
| c_id | p_id |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    2 |
|    4 |    3 |
|    5 |    4 |
|    6 |    5 |
|    7 |    4 |
|    8 |    7 |
|    9 |    8 |
|   10 |    9 |
+------+------+

Suggested fix:
I don't know the fix, but the problem might stem
from the use of LAST_INSERT_ID() to insert into
a table that has its own AUTO_INCREMENT column.
If you remove "AUTO_INCREMENT" from the definition
of the child table, the p_id values are assigned properly
by the inserts into child.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE parent (
    ->     p_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     PRIMARY KEY (p_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE TABLE child (
    ->     c_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     p_id SMALLINT UNSIGNED NOT NULL,
    ->     KEY (c_id)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO parent VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO child VALUES
    -> (NULL, LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO parent VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO child VALUES
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID());
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO parent VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO child VALUES
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID());
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO parent VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO child VALUES
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID());
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT * FROM parent;
+------+
| p_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM child;
+------+------+
| c_id | p_id |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    2 |
|    4 |    3 |
|    5 |    4 |
|    6 |    5 |
|    7 |    4 |
|    8 |    7 |
|    9 |    8 |
|   10 |    9 |
+------+------+
10 rows in set (0.00 sec)

mysql> 

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.1-alpha-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE parent (
    ->     p_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     PRIMARY KEY (p_id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> CREATE TABLE child (
    ->     c_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     p_id SMALLINT UNSIGNED NOT NULL,
    ->     KEY (c_id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> INSERT INTO parent VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO child VALUES
    -> (NULL, LAST_INSERT_ID());
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> INSERT INTO parent VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO child VALUES
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID());
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO parent VALUES (NULL);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO child VALUES
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID());
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO parent VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO child VALUES
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID()),
    -> (NULL, LAST_INSERT_ID());
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM parent;
+------+
| p_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM child;
+------+------+
| c_id | p_id |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    2 |
|    4 |    3 |
|    5 |    3 |
|    6 |    3 |
|    7 |    4 |
|    8 |    4 |
|    9 |    4 |
|   10 |    4 |
+------+------+
10 rows in set (0.01 sec)

mysql>

A duplicate of bug#6880.