Bug #41986 Replication slave does not pick up proper AUTO_INCREMENT value for Innodb tables

• Submitted: 9 Jan 1:21
• Modified: 20 Jan 22:33
• Reporter: Patrick Crews
• Status: Closed
• Category: Server: RBR
• Severity: S3 (Non-critical)
• Version: 5.1+
• OS: Any
• Assigned to: Zhenxing He
• Target Version: 5.1.31
• Tags: rpl.rpl_innodb_bug28430, innodb, auto_increment, row-based replication, replication
• Triage: Triaged: D2 (Serious)

[9 Jan 1:21] Patrick Crews

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).

[13 Jan 5:44] Bugs System

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.

[14 Jan 9:27] Bugs System

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.

[14 Jan 11:06] Bugs System

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

[15 Jan 7:25] Bugs System

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

[15 Jan 7:40] Bugs System

Pushed into 5.1.31 (revid:[e-mail]-20090115053147-tx1oapthnzgvs1ro) (version source
revid:[e-mail]-20090114155637-vi1ld8rpsfh465go) (merge vers: 5.1.31) (pib:6)

[16 Jan 17:53] Jon Stephens

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.

[19 Jan 12:26] Bugs System

Pushed into 5.1.31-ndb-6.2.17 (revid:[e-mail]-20090119095303-uwwvxiibtr38djii)
(version source revid:[e-mail]-20090115073240-1wanl85vlvw2she1) (merge vers:
5.1.31-ndb-6.2.17) (pib:6)

[19 Jan 14:04] Bugs System

Pushed into 5.1.31-ndb-6.3.21 (revid:[e-mail]-20090119104956-guxz190n2kh31fxl)
(version source revid:[e-mail]-20090119104956-guxz190n2kh31fxl) (merge vers:
5.1.31-ndb-6.3.21) (pib:6)

[19 Jan 15:00] Jon Stephens

Set status back to NDI pending merge to 6.0 tree.

[19 Jan 17:10] Bugs System

Pushed into 5.1.31-ndb-6.4.1 (revid:[e-mail]-20090119144033-4aylstx5czzz88i5)
(version source revid:[e-mail]-20090119144033-4aylstx5czzz88i5) (merge vers:
5.1.31-ndb-6.4.1) (pib:6)

[19 Jan 18:05] Jon Stephens

Set back to NDI pending merge to 6.0.

[20 Jan 19:56] Bugs System

Pushed into 6.0.10-alpha (revid:[e-mail]-20090119171328-2hemf2ndc1dxl0et) (version
source revid:[e-mail]-20090114143745-x2dvnmix6gjlt6z6) (merge vers:
6.0.10-alpha) (pib:6)

[20 Jan 22:33] Jon Stephens

Fix also documented in 6.0.10 changelog; closed.