MySQL Bugs: #6880: LAST_INSERT_ID() value changes during multi-row INSERT

Bug #6880	LAST_INSERT_ID() value changes during multi-row INSERT
Submitted:	29 Nov 2004 18:43	Modified:	9 Jun 2006 16:03
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.x/5.0.x	OS:
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
LAST_INSERT_ID() is often used in master-detail situations:
- Insert row into master table that contains an AUTO_INCREMENT column.
- Insert one or more rows into detail table, using the value of
  LAST_INSERT_ID() to insert the AUTO_INCREMENT value for the corresponding
  master row.

This works in 4.0.  For a multiple-row INSERT into the detail table,
LAST_INSERT_ID() returns a constant value (the value from the master
record), even if the detail table itself contains an AUTO_INCREMENT
column.

However, as of MySQL 4.1.2 and 5.0.1, LAST_INSERT_ID() changes from
row to row for the INSERT into the detail table.

How to repeat:
Here is a script the demonstrates the problem:

DROP TABLE IF EXISTS person, shirt;
CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT * FROM person;

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID()),
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());

SELECT * FROM shirt;

Result in 4.0.23 (correct):

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Antonio Paz |
+----+-------------+
+----+---------+-------+-------+
| id | style   | color | owner |
+----+---------+-------+-------+
|  1 | polo    | blue  |     1 |
|  2 | dress   | white |     1 |
|  3 | t-shirt | blue  |     1 |
|  4 | polo    | blue  |     1 |
|  5 | dress   | white |     1 |
|  6 | t-shirt | blue  |     1 |
+----+---------+-------+-------+

Result in current 4.1.x/5.0.x (incorrect):

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Antonio Paz |
+----+-------------+
+----+---------+-------+-------+
| id | style   | color | owner |
+----+---------+-------+-------+
|  1 | polo    | blue  |     1 |
|  2 | dress   | white |     1 |
|  3 | t-shirt | blue  |     2 |
|  4 | polo    | blue  |     3 |
|  5 | dress   | white |     4 |
|  6 | t-shirt | blue  |     5 |
+----+---------+-------+-------+

The problem here is that for the INSERT into the detail table,
the value of LAST_INSERT_ID() is changing during the statement.
For the first row, it returns the value from the master table,
but for the second and subsequent rows, it returns the value
from the previous insert into the detail table.

Hi!

I verified this with the latest 4.1 bk tree and InnoDB type tables. I removed the 'showstopper' status from the bug, because I do not think this is a showstopper.

--Heikki

heikki@hundin:~/mysql-4.1/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.8-debug-log

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

mysql> DROP TABLE IF EXISTS person, shirt;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> CREATE TABLE person (
    ->     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     name CHAR(60) NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE shirt (
    ->     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    ->     color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    ->     owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO person VALUES (NULL, 'Antonio Paz');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM person;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | Antonio Paz |
+----+-------------+
1 row in set (0.00 sec)

mysql>
mysql> INSERT INTO shirt VALUES
    -> (NULL, 'polo', 'blue', LAST_INSERT_ID()),
    -> (NULL, 'dress', 'white', LAST_INSERT_ID()),
    -> (NULL, 't-shirt', 'blue', LAST_INSERT_ID()),
    -> (NULL, 'polo', 'blue', LAST_INSERT_ID()),
    -> (NULL, 'dress', 'white', LAST_INSERT_ID()),
    -> (NULL, 't-shirt', 'blue', LAST_INSERT_ID());
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM shirt;
+----+---------+-------+-------+
| id | style   | color | owner |
+----+---------+-------+-------+
|  1 | polo    | blue  |     1 |
|  2 | dress   | white |     1 |
|  3 | t-shirt | blue  |     2 |
|  4 | polo    | blue  |     3 |
|  5 | dress   | white |     4 |
|  6 | t-shirt | blue  |     5 |
+----+---------+-------+-------+
6 rows in set (0.00 sec)

mysql> show create table person;
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------+
| Table  | Create Table

                            |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------+
| person | CREATE TABLE `person` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `name` char(60) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------+
1 row in set (0.00 sec)

mysql>

more test case in bug#16689.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6828

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7344

fixed in 4.1.21

Noted in 4.1.21 changelog.

In a multiple-row INSERT statement, LAST_INSERT_ID() should return
the same value for each row. However, in some cases, the value could
change if the table being inserted into had its own AUTO_INCREMENT
column.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8960

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20909

ChangeSet@1.2487, 2007-03-01 15:25:42+01:00, msvensson@pilot.blaudden +5 -0
  Switch back to master before cleanup of the created tables
  That causes test case for bug#6880 to be run on the master
  instead of the slave and thus the result files need to be updated