| Bug #41984 | inserts with auto-increment fails | ||
|---|---|---|---|
| Submitted: | 8 Jan 2009 22:28 | Modified: | 30 Mar 2009 15:04 |
| Reporter: | Evgeniy Pirogov | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1.22rc 5.1.30 GA | OS: | Any (Linux, Windows) |
| Assigned to: | Sunny Bains | CPU Architecture: | Any |
[10 Jan 2009 9:55]
Sunny Bains
I ran the test against an older snapshot of MySQL but the latest InnoDB 5.1
code and I can't reproduce the reported error. We have not made any changes
to the AUTOINC code since the r2858 snapshot that was sent on the 23 Oct 2008.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.28 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE t1 (
-> c1 INT(11) NOT NULL AUTO_INCREMENT,
-> c2 INT(11) NOT NULL,
-> PRIMARY KEY(c1)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO t1 values (9,1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+----+----+
| c1 | c2 |
+----+----+
| 9 | 1 |
+----+----+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> DELETE FROM t1;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> INSERT INTO t1(c2) VALUES(1) ;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+----+----+
| c1 | c2 |
+----+----+
| 10 | 1 |
+----+----+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> DELETE FROM t1;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> INSERT INTO t1 VALUES(11,1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+----+----+
| c1 | c2 |
+----+----+
| 11 | 1 |
+----+----+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM t1;
+----+----+
| c1 | c2 |
+----+----+
| 11 | 1 |
+----+----+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO t1(c2) VALUES(1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+----+----+
| c1 | c2 |
+----+----+
| 11 | 1 |
| 12 | 1 |
+----+----+
2 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Regards,
-sunny
[12 Jan 2009 0:35]
Evgeniy Pirogov
Could it be that it's related to Intel complied version of mysql for linux only? I'll try gcc binary and will update the bug
[12 Jan 2009 15:31]
Evgeniy Pirogov
windows version of mysql 5.1.30 downloaded from http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.30-win32.zip/from/ftp://mysql.mirro... the same error version() 5.1.30-community insert explicit 9. must become 10 insert explicit 9. must become 10 id account_id 9 1 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 delete all. stays 10 delete all. stays 10 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 insert autoincrement. must become 11 insert autoincrement. must become 11 id account_id 10 1 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 delete all. stays 11 delete all. stays 11 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 insert explicit 11. must become 12, but stays 11 insert explicit 11. must become 12, but stays 11 id account_id 11 1 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 id account_id 11 1 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 insert auto-increment insert auto-increment ERROR 1062 (23000) at line 44: Duplicate entry '11' for key 'PRIMARY'
[12 Jan 2009 15:32]
Evgeniy Pirogov
so far for two OS (Linux, Windows) I can reproduce the bug
[13 Jan 2009 2:06]
Sunny Bains
Evgeniy, I don't doubt that *you* can reproduce it with 5.1.30, my testing is against the revision that we have in our source repository which was shipped to MySQL in Oct 2008. And I can't reproduce the problem in the current/shipped version. The fix therefore should be in the next release of MySQL which incorporates the Oct 2008 snapshot code. Regarding the problem being an icc issue, possible but I doubt it. Regards, -sunny
[13 Jan 2009 3:01]
Evgeniy Pirogov
I did testing with gcc complied binaries 5.1.30 which i downloaded today and installed on debian etch. the same error. I think this error is in innodb engine in 5.1.30 (and 5.1.22rc - which we use in production currently since Jan 2008)
[18 Feb 2009 22:17]
Evgeniy Pirogov
5.1.30 GA 64 bit
CentOS 5.0
uname -a
Linux 2.6.18-92.el5 #1 SMP Tue Jun 10 18:51:06 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux
mysql -u root test <mysql-bug.sql
version()
5.1.30-log
insert explicit 9. must become 10
insert explicit 9. must become 10
id account_id
9 1
*************************** 1. row ***************************
Table: test_inc
Create Table: CREATE TABLE `test_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
delete all. stays 10
delete all. stays 10
*************************** 1. row ***************************
Table: test_inc
Create Table: CREATE TABLE `test_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
insert autoincrement. must become 11
insert autoincrement. must become 11
id account_id
10 1
*************************** 1. row ***************************
Table: test_inc
Create Table: CREATE TABLE `test_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
delete all. stays 11
delete all. stays 11
*************************** 1. row ***************************
Table: test_inc
Create Table: CREATE TABLE `test_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
insert explicit 11. must become 12, but stays 11
insert explicit 11. must become 12, but stays 11
id account_id
11 1
*************************** 1. row ***************************
Table: test_inc
Create Table: CREATE TABLE `test_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
id account_id
11 1
*************************** 1. row ***************************
Table: test_inc
Create Table: CREATE TABLE `test_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
insert auto-increment
insert auto-increment
ERROR 1062 (23000) at line 44: Duplicate entry '11' for key 'PRIMARY'
[28 Mar 2009 10:57]
Andrei Elkin
Evgeniy, I might hit the same issue. Are you able to reproduce your problem with the latest (5.1.34 at the moment)? If so, could you please leave info on your my.cnf. Thanks! Andrei
[28 Mar 2009 11:54]
Andrei Elkin
I am sorry for auto-incrementing the current release(), it's 5.1.32 actually.
[30 Mar 2009 15:04]
Evgeniy Pirogov
I can't reproduce it with 5.1.32 this my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 256M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 skip-federated skip-name-resolve server-id = 1 innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:10M:autoextend innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb-file-per-table = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
Description: inserting explicit values into auto-incremented column in InnoDB table, inserting with auto-incrementing deleting inserting explicit values into auto-incremented column in InnoDB table, inserting with auto-incrementing - failes output from the run (on how to repeat) @dev:~$ mysql -u root -p test <mysql-bug.sql Enter password: version() 5.1.30 insert explicit 9. must become 10 insert explicit 9. must become 10 id account_id 9 1 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 delete all. stays 10 delete all. stays 10 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 insert autoincrement. must become 11 insert autoincrement. must become 11 id account_id 10 1 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 delete all. stays 11 delete all. stays 11 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 insert explicit 11. must become 12, but stays 11 insert explicit 11. must become 12, but stays 11 id account_id 11 1 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 id account_id 11 1 *************************** 1. row *************************** Table: test_inc Create Table: CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 insert auto-increment insert auto-increment ERROR 1062 (23000) at line 44: Duplicate entry '11' for key 'PRIMARY' if commented section to be uncommented and script runs again - no problem no problem found on 5.0.* (linux/mac osx) no problem found with MyISAM on 5.1.* only InnoDB How to repeat: select version(); DROP TABLE IF EXISTS test_inc; CREATE TABLE `test_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; select 'insert explicit 9. must become 10'; insert into test_inc ( id, account_id) values (9,1); select * from test_inc; show create table test_inc \G select 'delete all. stays 10'; delete from test_inc; select * from test_inc; show create table test_inc \G select 'insert autoincrement. must become 11'; insert into test_inc ( account_id) values (1) ; select * from test_inc ; show create table test_inc \G select 'delete all. stays 11'; delete from test_inc ; select * from test_inc ; show create table test_inc \G select 'insert explicit 11. must become 12, but stays 11'; insert into test_inc ( id, account_id) values (11,1); select * from test_inc; show create table test_inc \G /* select 'insert explicit 12. must become 13'; insert into test_inc ( id, account_id) values (12,1); select * from test_inc; show create table test_inc \G */ select * from test_inc; show create table test_inc \G select 'insert auto-increment'; insert into test_inc ( account_id) values (1); select * from test_inc; show create table test_inc \G