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