Bug #23006 Replicated AUTO_INCREMENT field is out of sync (simple master -> slave)
Submitted: 5 Oct 2006 11:11 Modified: 17 Oct 2006 6:20
Reporter: Mikhail Entaltsev Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.24 OS:Linux (Linux gentoo)
Assigned to: CPU Architecture:Any
Tags: auto_increment, replication

[5 Oct 2006 11:11] Mikhail Entaltsev
Description:
After simple master -> slave replication auto_increment field is out of sync. Both (master and slave) are from standard Gentoo installation.

Master (Linux 2.6.17-gentoo-r4 Intel Xeon):
> show variables like "version%"

Variable_name,Value
version,5.0.24-log
version_bdb,Sleepycat Software: Berkeley DB 4.1.24: (August 10, 2006)
version_comment,Gentoo Linux mysql-5.0.24
version_compile_machine,i686
version_compile_os,pc-linux-gnu

> SHOW VARIABLES LIKE 'auto_inc%';

Variable_name,Value
auto_increment_increment,1
auto_increment_offset,1

Slave (Linux 2.6.17-gentoo-r8 Intel Xeon):
> show variables like "version%"

Variable_name,Value
version,5.0.24a-log
version_bdb,Sleepycat Software: Berkeley DB 4.1.24: (September 13, 2006)
version_comment,Gentoo Linux mysql-5.0.24-r1
version_compile_machine,i686
version_compile_os,pc-linux-gnu

> SHOW VARIABLES LIKE 'auto_inc%';

Variable_name,Value
auto_increment_increment,1
auto_increment_offset,1

How to repeat:
Execute on master:

CREATE TABLE `A` (
  `ID` int unsigned NOT NULL auto_increment,
  `Num` int unsigned NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

insert into A (Num)
values (1),(2),(3),(4),(5);

select * from A;

Result:
ID,Num
1,1
2,2
3,3
4,4
5,5

Execute on Slave:

select * from A;

Result:

ID,Num
1,1
1124,2
30935,3
60746,4
90557,5
[5 Oct 2006 11:25] Mikhail Entaltsev
Forget to say that on replication between 2 MySQLs:

version,4.0.20-standard-log
version_comment,Official MySQL-standard binary
version_compile_os,pc-linux

everything is working fine.
[6 Oct 2006 6:48] Sveta Smirnova
Thank you for the report.

Please, confirm or refuse if slave is readonly.
[6 Oct 2006 7:09] Mikhail Entaltsev
Yes, slave is read only.
[11 Oct 2006 9:00] Sveta Smirnova
Thank you for the report.

I can not repeat it with current BK sources.

Could you please try MySQL 5.0.26 you can download from http://dev.mysql.com/downloads/mysql/5.0.html
[16 Oct 2006 23:23] Mikhail Entaltsev
After upgrading slave to 5.0.26 everything is perfect. Thanks.
[17 Oct 2006 6:20] Sveta Smirnova
I change status because your last reply.