InnoDB AUTO_INCREMENT produces same value twice

[Sveta Smirnova]

Upstream bug http://bugs.mysql.com/bug.php?id=76872 is repeatable with Percona Server 5.6.25-73.1 and 5.5.44-37.3

How to repeat:

Follow instructions from comment "[30 Apr 15:39] Sinisa Milivojevic ":

sveta@thinkie:~/build/ps-5.5/mysql-test> ./mtr --start innodb --mysqld=--auto_increment_increment=2 &
[2] 8296
sveta@thinkie:~/build/ps-5.5/mysql-test> defined(@array) is deprecated at lib/mtr_cases.pm line 339.
        (Maybe you should just omit the defined()?)
defined(@array) is deprecated at ./mtr line 495.
        (Maybe you should just omit the defined()?)
Subroutine lldb_arguments redefined at ./mtr line 5824.
Logging: ./mtr --start innodb --mysqld=--auto_increment_increment=2
150820 0:22:49 [Note] /home/sveta/build/ps-5.5/bin/mysqld (mysqld 5.5.44-37.3-debug) starting as process 8301 ...
MySQL Version 5.5.44
Checking supported features...
 - skipping SSL, mysqld not compiled with SSL
 - binaries are debug compiled
Collecting tests...
Checking leftover processes...
 - found old pid 5235 in 'mysqld.2.pid', killing it...
   process did not exist!
 - found old pid 5232 in 'mysqld.1.pid', killing it...
   process did not exist!
Removing old var directory...
Creating var directory '/home/sveta/build/ps-5.5/mysql-test/var'...
Installing system database...

==============================================================================

TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
worker[1]
Started [mysqld.1 - pid: 8329, winpid: 8329]
worker[1] Using config for test innodb.innodb
worker[1] Port and socket path for server(s):
worker[1] mysqld.1 13001 /home/sveta/build/ps-5.5/mysql-test/var/tmp/mysqld.1.sock
worker[1] Waiting for server(s) to exit...

sveta@thinkie:~/build/ps-5.5/mysql-test> mysqlmtr -P13001 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.44-37.3-debug-log Source distribution

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> create table t3(test_id int not null auto_increment primary key, testcol varchar(255)) engine=innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t3(testcol) values('aldsldjfhasjk');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t3;
+---------+---------------+
| test_id | testcol |
+---------+---------------+
| 1 | aldsldjfhasjk |
+---------+---------------+
1 row in set (0.00 sec)

mysql> \q
Bye
sveta@thinkie:~/build/ps-5.5/mysql-test> mysqladminmtr -P13001 shutdown
sveta@thinkie:~/build/ps-5.5/mysql-test>
sveta@thinkie:~/build/ps-5.5/mysql-test> worker[1] Server [mysqld.1 - pid: 8329, winpid: 8329, exit: 0] died
mysql-test-run: *** ERROR: Test suite aborted

[2]+ Exit 1 ./mtr --start innodb --mysqld=--auto_increment_increment=2
sveta@thinkie:~/build/ps-5.5/mysql-test> ./mtr --start-dirty innodb --mysqld=--auto_increment_increment=2 &
[2] 8377
sveta@thinkie:~/build/ps-5.5/mysql-test> defined(@array) is deprecated at lib/mtr_cases.pm line 339.
        (Maybe you should just omit the defined()?)
defined(@array) is deprecated at ./mtr line 495.
        (Maybe you should just omit the defined()?)
Subroutine lldb_arguments redefined at ./mtr line 5824.
Logging: ./mtr --start-dirty innodb --mysqld=--auto_increment_increment=2
150820 0:23:35 [Note] /home/sveta/build/ps-5.5/bin/mysqld (mysqld 5.5.44-37.3-debug) starting as process 8382 ...
MySQL Version 5.5.44
Checking supported features...
 - skipping SSL, mysqld not compiled with SSL
 - binaries are debug compiled
Collecting tests...
Checking leftover processes...

==============================================================================

TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
worker[1]
Started [mysqld.1 - pid: 8385, winpid: 8385]
worker[1] Using config for test innodb.innodb
worker[1] Port and socket path for server(s):
worker[1] mysqld.1 13001 /home/sveta/build/ps-5.5/mysql-test/var/tmp/mysqld.1.sock
worker[1] Waiting for server(s) to exit...

sveta@thinkie:~/build/ps-5.5/mysql-test> for i in {1..5}; do echo "INSERT INTO test.t3 (testcol) VALUES ('aldsldjfhasjk')" | mysqlmtr -P13001 > /dev/null & done
[3] 8408
[4] 8410
[5] 8412
[6] 8414
[7] 8417
sveta@thinkie:~/build/ps-5.5/mysql-test> ERROR 1062 (23000) at line 1: Duplicate entry '3' for key 'PRIMARY'

[3] Done echo "INSERT INTO test.t3 (testcol) VALUES ('aldsldjfhasjk')" | ../bin/mysql -uroot -h127.0.0.1 -P13000 -P13001 > /dev/null
[4] Exit 1 echo "INSERT INTO test.t3 (testcol) VALUES ('aldsldjfhasjk')" | ../bin/mysql -uroot -h127.0.0.1 -P13000 -P13001 > /dev/null
[5] Done echo "INSERT INTO test.t3 (testcol) VALUES ('aldsldjfhasjk')" | ../bin/mysql -uroot -h127.0.0.1 -P13000 -P13001 > /dev/null
[6]- Done echo "INSERT INTO test.t3 (testcol) VALUES ('aldsldjfhasjk')" | ../bin/mysql -uroot -h127.0.0.1 -P13000 -P13001 > /dev/null
[7]+ Done echo "INSERT INTO test.t3 (testcol) VALUES ('aldsldjfhasjk')" | ../bin/mysql -uroot -h127.0.0.1 -P13000 -P13001 > /dev/null

[Laurynas Biveinis (laurynas-biveinis)]

Experimental fix at https://github.com/laurynas-biveinis/percona-server/commit/bd9d2eb05db41a43141988221f5d86a4303a3b36 for 5.5 and https://github.com/laurynas-biveinis/percona-server/commit/c0b58a3f546633e9c00fb0523a75392a7acd1b76 for 5.6.

[Yura Sorokin (yura-sorokin)]

Fixed in upstream 5.7.17
https://github.com/percona/percona-server/pull/1310

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-921