MySQL Bugs: #41986: Replication slave does not pick up proper AUTO

Bug #41986	Replication slave does not pick up proper AUTO_INCREMENT value for Innodb tables
Submitted:	9 Jan 2009 0:21	Modified:	20 Jan 2009 21:33
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Zhenxing He	CPU Architecture:	Any
Tags:	auto_increment, innodb, replication, row-based replication, rpl.rpl_innodb_bug28430

Description:
Replication slaves don't seem to be gaining the proper AUTO_INCREMENT value when using Innodb tables.

As an example, run test rpl.rpl_innodb_bug28430 and observe the difference between expected and actual results.  The only thing missing from the slave's CREATE TABLE statement is the proper AUTO_INCREMENT value:

This appears to occur for both row and mixed replication modes.

It is unknown if this is related to Bug#37399.  This issue seems to occur when synching the slave and master after the master has been running for a time and the AUTO_INCREMENT value is not properly captured for the slave.  Bug#37399 seems similar, but the circumstances described are not an exact match.

Test failure output:
rpl.rpl_innodb_bug28430 'row'  [ fail ]

--- /Users/pcrews/usr/local/bin/data1/work/rpl_innodb/mysql-5.1-bugteam/mysql-test/suite/rpl/r/rpl_innodb_bug28430.result	2009-01-08 23:22:14.000000000 +0300
+++ /Users/pcrews/usr/local/bin/data1/work/rpl_innodb/mysql-5.1-bugteam/mysql-test/suite/rpl/r/rpl_innodb_bug28430.reject	2009-01-09 02:53:17.000000000 +0300
@@ -103,6 +103,8 @@
 CALL test.proc_byrange();
 SELECT count(*) as "Master byrange" FROM test.byrange_tbl;
 Master byrange	500

<reporter comment:  The following statements are run against the slave>
 show create table test.byrange_tbl;
 Table	byrange_tbl
 Create Table	CREATE TABLE `byrange_tbl` (
@@ -113,7 +115,7 @@
   `fkid` mediumint(9) DEFAULT NULL,
   `filler` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
 /*!50100 PARTITION BY RANGE (id)
 SUBPARTITION BY HASH (id)
 SUBPARTITIONS 2

How to repeat:
Run the following test from the mysql-test directory:

./mysql-test-run --force --suite=rpl rpl_innodb_bug28430

Observe the failures - this is the difference in expected and actual results for the slave's version of the table created (and manipulated via INSERTS) on the master.

Suggested fix:
Unknown.

We should determine if this is an issue in replication code or Innodb code.

However, we need the slave to capture the proper AUTO_INCREMENT starting point or we run into duplicate key issues (the slave tries to pick the default starting point, but data already exists for those AUTO_INCREMENTed values).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63057

2747 He Zhenxing	2009-01-13
      BUG#41986 Replication slave does not pick up proper AUTO_INCREMENT value for Innodb tables
      
      The next number (AUTO_INCREMENT) field of the table for write
      rows events are not initialized, and cause some engines (innodb)
      not correctly update the tables's auto_increment value.
      
      This patch fixed this problem by honor next number fields if present.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63196

2747 He Zhenxing	2009-01-14
      BUG#41986 Replication slave does not pick up proper AUTO_INCREMENT value for Innodb tables
      
      The next number (AUTO_INCREMENT) field of the table for write
      rows events are not initialized, and cause some engines (innodb)
      not correctly update the tables's auto_increment value.
      
      This patch fixed this problem by honor next number fields if present.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63198

2966 He Zhenxing	2009-01-14 [merge]
      Merge BUG#41986 to 6.0-bugteam

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63317

2733 He Zhenxing	2009-01-15 [merge]
      auto merge

Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:chad@mysql.com-20090114155637-vi1ld8rpsfh465go) (merge vers: 5.1.31) (pib:6)

Documented in the 5.1.31 changelog as follows:

        Per-table AUTO_INCREMENT option values were not replicated
        correctly for InnoDB tables.

Set to NDI pending merge to 6.0.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Set status back to NDI pending merge to 6.0 tree.

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Set back to NDI pending merge to 6.0.

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:timothy.smith@sun.com-20090114143745-x2dvnmix6gjlt6z6) (merge vers: 6.0.10-alpha) (pib:6)

Fix also documented in 6.0.10 changelog; closed.