| 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: | |
| 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: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
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.