Bug #80016 Auto Increment Abnormal Jump - Master Master Replication - MySQL 5.1

• Submitted: 17 Jan 2016 8:22
• Modified: 17 Jan 2016 15:53
• Reporter: Anon Dude
• Status: Won't fix
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.72
• OS: CentOS (6.7)
• CPU Architecture: Any

[17 Jan 2016 8:22] Anon Dude

Description:
When trying to insert value in InnoDB table with autoincrement column results in unexpected value generation for autoincrement column.

mysql> select * from test_myisam;
+----+------+--------+
| id | name | amount |
+----+------+--------+
|  2 | ABC  |      1 |
|  3 | DEF  |      2 |
|  4 | GHI  |      3 |
|  5 | JKL  |      4 |
|  6 | MNO  |      5 |
|  7 | PQR  |      6 |
+----+------+--------+
6 rows in set (0.00 sec)

mysql> select * from test_innodb;
+----+------+--------+
| id | name | amount |
+----+------+--------+
|  2 | ABC  |      1 |
|  3 | DEF  |      2 |
|  4 | GHI  |      3 |
|  7 | JKL  |      4 |
|  8 | MNO  |      5 |
| 11 | PQR  |      6 |
+----+------+--------+
6 rows in set (0.00 sec)

How to repeat:
Master 1
---------
vi /etc/my.cnf
---------
[mysqld]
server-id=1
log-bin="mysql-bin"
relay-log="mysql-relay-log"
auto_increment_increment=2
auto_increment_offset=2

service mysql restart
-----------------------------
mysql -u root
create user 'replicator'@'%' identified by 'replicator';
grant replication slave on *.* to 'replicator'@'%' identified by 'replicator';
flush privileges;

stop slave;
show master status;
change master to master_host = '192.168.6.87', master_user = 'replicator', master_password = 'replicator', master_log_file = 'mysql-bin.000001', master_log_pos = 106; 
start slave;
show slave status\G

Master 2
---------
vi /etc/my.cnf
---------
[mysqld]
server-id=2
log-bin="mysql-bin"
relay-log="mysql-relay-log"
auto_increment_increment=2
auto_increment_offset=1

service mysql restart
-----------------------------

create user 'replicator'@'%' identified by 'replicator';
grant replication slave on *.* to 'replicator'@'%' identified by 'replicator';
flush privileges;

stop slave;
show master status;
change master to master_host = '192.168.6.159', master_user = 'replicator', master_password = 'replicator', master_log_file = 'mysql-bin.000001', master_log_pos = 106; 
start slave;
show slave status\G

MYISAM
--------
Master 1
---------
drop database test;
create database test;
use test;
create table test_myisam (id int not null primary key auto_increment, name varchar(40) not null, amount int) engine=myisam;
insert into test_myisam(name,amount) values ('ABC', 1);
select * from test_myisam;

Master 2
---------
use test;
select * from test_myisam;
insert into test_myisam(name,amount) values ('DEF', 2);
select * from test_myisam;

Master 1
---------
select * from test_myisam;
insert into test_myisam(name,amount) values ('GHI', 3);
select * from test_myisam;

Master 2
---------
select * from test_myisam;
insert into test_myisam(name,amount) values ('JKL', 4);
select * from test_myisam;

Master 1
---------
select * from test_myisam;
insert into test_myisam(name,amount) values ('MNO', 5);
select * from test_myisam;

Master 2
---------
select * from test_myisam;
insert into test_myisam(name,amount) values ('PQR', 6);
select * from test_myisam;

INNODB
-------
Master 1
---------
use test;
create table test_innodb (id int not null primary key auto_increment, name varchar(40) not null, amount int) engine=innodb;
insert into test_innodb(name,amount) values ('ABC', 1);
select * from test_innodb;

Master 2
---------
use test;
select * from test_innodb;
insert into test_innodb(name,amount) values ('DEF', 2);
select * from test_innodb;

Master 1
---------
select * from test_innodb;
insert into test_innodb(name,amount) values ('GHI', 3);
select * from test_innodb;

Master 2
---------
select * from test_innodb;
insert into test_innodb(name,amount) values ('JKL', 4);
select * from test_innodb;

Master 1
---------
select * from test_innodb;
insert into test_innodb(name,amount) values ('MNO', 5);
select * from test_innodb;

Master 2
---------
select * from test_innodb;
insert into test_innodb(name,amount) values ('PQR', 6);
select * from test_innodb;

Master 1/2
---------
select * from test_myisam;
select * from test_innodb;

Suggested fix:
1.Switch to MyISAM
2.Upgrade to 5.5

[17 Jan 2016 15:53] MySQL Verification Team

Thank you for the bug report.

https://www.mysql.com/support/eol-notice.html