MySQL Bugs: #37399: auto_increment value gets out of sync on master and slave

Bug #37399	auto_increment value gets out of sync on master and slave
Submitted:	13 Jun 2008 16:07	Modified:	4 Mar 2009 8:06
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.1.26, 6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
when inserting and deleting rows from an innodb table with auto_increment defined, the slave and master differ in what they believe the auto_increment value is in show table status.  after restarting the slave, the figure is correct, presumably because innodb recalculated it.

How to repeat:
have a running RBR replication with default options.
master:

mysqld --log-bin --binlog-format=row --server-id=1

make sure the slave is running and connected.

run this on master:

drop table if exists at;
create table at(a int not null auto_increment primary key,b text)engine=innodb;
insert into at (b) values ('a'),('b'),('c');
delete from at limit 1;
insert into at (b) values ('a'),('b'),('c');
show table status like 'at';

now compare 'show table status like 'at'; from master and slave and notice the value of column Auto_increment

my (cutted) output:

on master i got:
mysql> show table status like 'at'\G
           Name: at
         Engine: InnoDB
           Rows: 5
 Auto_increment: 7  <--
1 row in set (0.02 sec)

on slave i got:

mysql> show table status like 'at'\G
           Name: at
         Engine: InnoDB
           Rows: 5
 Auto_increment: 4 <--
1 row in set (0.00 sec)

Thank you for the report.

Verified as described.

Workaround >1, because bug is not repeatable with mixed mode or MyISAM storage engine.