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