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

[29 Nov 2004 18:43] Paul DuBois
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.
[29 Nov 2004 18:57] Heikki Tuuri
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>
[31 Jan 2006 7:57] Calvin Sun
more test case in bug#16689.
[24 May 2006 12:17] Bugs System
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
[7 Jun 2006 8:59] Bugs System
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
[7 Jun 2006 10:54] Ramil Kalimullin
fixed in 4.1.21
[9 Jun 2006 16:03] Paul DuBois
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.
[9 Jul 2006 15:53] Bugs System
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
[1 Mar 2007 14:27] Bugs System
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