MySQL Bugs: #18208: SBR fails to replicate auto

Bug #18208	SBR fails to replicate auto_increment values for Cluster
Submitted:	14 Mar 2006 7:32	Modified:	17 Mar 2006 8:34
Reporter:	Magnus Blåudd	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.1	OS:	Any (all)
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
When a table with auto_incerement key is replicated to a NDB table on the slave, the auto_increment vaus does not become tha same as on the master.

How to repeat:
CREATE TABLE t1 (
 a int unsigned not null auto_increment primary key,
 b int unsigned
) ENGINE=NDB;

CREATE TABLE t2 (
 a int unsigned not null auto_increment primary key,
 b int unsigned
) ENGINE=NDB;

INSERT INTO t1 VALUES (NULL, 0);
INSERT INTO t1 SELECT NULL, 0 FROM t1;

INSERT INTO t2 VALUES (NULL, 0), (NULL,1);

It seems like the slave thread terminates itself with "Unknown error 1105" because it gets a duplicate key error when executing the statement form the master. See
below excerpt from slave.log

060301 18:10:13 [Note] next log
'/home/msvensson/mysql/bug17728/my51-bug17728/mysql-test/var/log/slave-relay-bin
.000003' is currently active
060301 18:10:14 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/t1
060301 18:10:15 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/t2
060301 18:10:15 [ERROR] Slave: Error 'Can't write; duplicate key in table 't2''
on query. Default database: 'test'. Query: 'INSERT INTO t2 VALUES (NULL, 0),
(NULL,1)', Error_code: 1022
060301 18:10:15 [Warning] Slave: Can't write; duplicate key in table 't2'
Error_code: 1022
060301 18:10:15 [Warning] Slave: Unknown error Error_code: 1105
060301 18:10:15 [ERROR] Error running query, slave SQL thread aborted. Fix the
problem, and restart the slave SQL thread with "SLAVE START". We stopped at log
'master-bin.000001' position 927

The slave thread stops because the query "INSERT INTO t2 VALUES (NULL,0),
(NULL,1)" fails on the slave when it generates the autoincrement values.

One "Intvar_log_event" with INSERT_ID_EVENT 1 has been read from the log,
indicating the the autoinc value of the first insert should be 1, but what the
second value became is unknown(most likely 2, but it could be higher).

The slave is required to have the same auto increment
values as on the  master. Otherwise a subsequent update would miss
or update wrong row.

Suggested fix:
On the server - make sure to generate auto_inc

Suggested fix:
On the server - make sure to generate consecutive auto_increment values  by allocating all the auto inc values needed for the insert in ha_ndbcluster::start_bulk_insert. This will also optimize inserts in tables with auto_increment  since the auto inc value only had to be fetched one time from the server.

On the slave - use the value that came from the server in the "INSERT_ID_EVENT" as the first auto inc value and then set consecutive numbers for the other rows. Exactly as it was made on the server.

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/3860

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/3862

Pushed to 5.1.8

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.1.8 changelog. Closed.