Bug #59885 Specific INSERT syntax fails after UPDATING.
Submitted: 2 Feb 2011 10:19 Modified: 23 Mar 2011 13:52
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1.54 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[2 Feb 2011 10:19] Peter Laursen
Description:
I find a slightly different behavior with 5.0.90 and 5.1.45 as compared to 5.1.54 and 5.5.8. It looks like some fix was made in between 5.1.45 and 5.1.54, but the fix is not complete. 

How to repeat:
5.0.90 + 5.1.45

DROP TABLE IF EXISTS `auto_table`;

CREATE TABLE `auto_table` (
`auto_column` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`auto_column`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

INSERT INTO `auto_table` VALUES();
INSERT INTO `auto_table`(`auto_column`) VALUES(NULL);

SELECT * FROM `auto_table`;
/*
auto_column
-----------
          1
          2
*/

UPDATE `auto_table` SET `auto_column`= LAST_INSERT_ID() + 1 WHERE `auto_column` = LAST_INSERT_ID();

SELECT * FROM `auto_table`;
/*
auto_column
-----------
          1
          3
*/

-- now this syntax fails 
INSERT INTO `auto_table`() VALUES();
-- Error Code : 1062
-- Duplicate entry '3' for key 'PRIMARY'

-- as well as this
INSERT INTO `auto_table`(`auto_column`) VALUES(NULL);
-- Error Code : 1062
-- Duplicate entry '3' for key 'PRIMARY'

SELECT * FROM `auto_table`;
/*
auto_column
-----------
          1
          3
*/

5.1.54 + 5.5.8:

DROP TABLE IF EXISTS `auto_table`;

CREATE TABLE `auto_table` (
`auto_column` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`auto_column`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

INSERT INTO `auto_table` VALUES();
INSERT INTO `auto_table`(`auto_column`) VALUES(NULL);

SELECT * FROM `auto_table`;
/*
auto_column
-----------
          1
          2
*/

UPDATE `auto_table` SET `auto_column`= LAST_INSERT_ID() + 1 WHERE `auto_column` = LAST_INSERT_ID();

SELECT * FROM `auto_table`;
/*
auto_column
-----------
          1
          3
*/

-- now this syntax fails 
INSERT INTO `auto_table`() VALUES();
-- Error Code : 1062
-- Duplicate entry '3' for key 'PRIMARY'

-- but this works
INSERT INTO `auto_table`(`auto_column`) VALUES(NULL);
SELECT * FROM `auto_table`;
/*
auto_column
-----------
          1
          3
          4
*/

Suggested fix:
The INSERT syntax "INSERT INTO `auto_table` VALUES();" should work after updating with an expression using LAST_INSERT_ID() as it does before the UPDATE.
[3 Feb 2011 12:47] Peter Laursen
I have changed the synopsis and the category.  It is not related to LAST_INSERT_ID() as I first thought. It seems to be an InnoDB issue.  It works with MyISAM as expected.

On 5.1.54 (w. default InnoDB):

DROP TABLE IF EXISTS `auto_table`;

CREATE TABLE `auto_table` (
`auto_column` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`auto_column`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

INSERT INTO `auto_table` VALUES();
INSERT INTO `auto_table`(`auto_column`) VALUES(NULL);
UPDATE `auto_table` SET `auto_column`= 3 WHERE `auto_column` = 2;

INSERT INTO `auto_table`() VALUES(); -- failure
INSERT INTO `auto_table`(`auto_column`) VALUES(NULL); -- success
SELECT * FROM `auto_table`;

DROP TABLE IF EXISTS `auto_table`;

CREATE TABLE `auto_table` (
`auto_column` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`auto_column`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

INSERT INTO `auto_table` VALUES();
INSERT INTO `auto_table`(`auto_column`) VALUES(NULL);
UPDATE `auto_table` SET `auto_column`= 3 WHERE `auto_column` = 2;

INSERT INTO `auto_table`() VALUES(); -- success
INSERT INTO `auto_table`(`auto_column`) VALUES(NULL); -- success
SELECT * FROM `auto_table`;
[3 Feb 2011 12:53] Peter Laursen
One more typo fix .. :-(
[7 Feb 2011 11:29] Valeriy Kravchuk
Based on your last test case, this is what happens:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS `auto_table`;
Query OK, 0 rows affected, 1 warning (0.13 sec)

mysql> CREATE TABLE `auto_table` (
    -> `auto_column` INT(11) NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (`auto_column`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO `auto_table` VALUES(); -- value 1 inserted
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO `auto_table`(`auto_column`) VALUES(NULL); -- value 2 inserted
Query OK, 1 row affected (0.05 sec)

mysql> UPDATE `auto_table` SET `auto_column`= 3 WHERE `auto_column` = 2; -- 2 is modified to 3, but why auto_increment counter should change?
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show create table `auto_table`\G
*************************** 1. row ***************************
       Table: auto_table
Create Table: CREATE TABLE `auto_table` (
  `auto_column` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`auto_column`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> INSERT INTO `auto_table`() VALUES(); -- fail as value 3 already exists
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> show create table `auto_table`\G
*************************** 1. row ***************************
       Table: auto_table
Create Table: CREATE TABLE `auto_table` (
  `auto_column` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`auto_column`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 -- auto_inc value was incremented
1 row in set (0.00 sec)

mysql> INSERT INTO `auto_table`() VALUES(); -- now it works
Query OK, 1 row affected (0.03 sec)

Why do you think that there is any bug here (taking into account http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html and http://dev.mysql.com/doc/refman/5.1/en/create-table.html)?
[23 Feb 2011 12:15] Peter Laursen
I am sorry, but I do not understand exactly what I should notice from those doc pages. Could you be more specific?

My point is that the 2 statements

INSERT INTO `auto_table` VALUES();
INSERT INTO `auto_table`(`auto_column`) VALUES(NULL);

.. are identical in the respect that the server will handle the auto_increment automatically. So I cannot see why the internal auto_increment cursor/pointer does not behave the same with both.
[23 Feb 2011 13:52] Valeriy Kravchuk
Note that I've used

INSERT INTO `auto_table`() VALUES(); 

two times in a row in my test case. First time it failed, then it worked. Explicit reference to auto_inc column does not matter, it just happened so that you mentioned it second time and had not mentioned first time.

I was referring to the following text from http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html:

"If a user specifies NULL or 0 for the AUTO_INCREMENT column in an INSERT, InnoDB treats the row as if the value was not specified and generates a new value for it."
[24 Mar 2011 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".