MySQL Bugs: #16993: ALTER TABLE ADD id INT AUTO_INCREMENT is not replicated correctly

Bug #16993	ALTER TABLE ADD id INT AUTO_INCREMENT is not replicated correctly
Submitted:	1 Feb 2006 3:49	Modified:	20 Apr 2006 12:07
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.6-alpha	OS:	Linux (Linux 32 Bit OS)
Assigned to:	Mats Kindahl	CPU Architecture:	Any

Description:
The following test case produce mismatched data between the Master and Slave clusters:

SHOW VARIABLES LIKE 'relay_log_space_limit';

CREATE TABLE t1 (name varchar(64), age smallint(3));
INSERT INTO  t1 SET name='Andy', age=31;
INSERT t1 SET name='Jacob', age=2;
INSERT into t1 SET name='Caleb', age=1;
ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1 ORDER BY id;
save_master_pos;
connection slave;
sync_with_master;
SELECT * FROM t1 ORDER BY id;
connection master;
drop table t1;
save_master_pos;
connection slave;
sync_with_master;

  ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY;
  SELECT * FROM t1 ORDER BY id;
  name  age     id
! Caleb 1       00000001
  Jacob 2       00000002
! Andy  31      00000003
  SELECT * FROM t1 ORDER BY id;
  name  age     id
  Andy  31      00000001
! Caleb 1       00000002
! Jacob 2       00000003
  drop table t1;

How to repeat:
./mysql-test-run --with-ndbcluster-all --ndb-extra-test --mysqld=--default-storage-engine=ndb --force --do-test=rpl000005

Thank you for a bug report. Verified just as described with 5.1.6-alpha-BK (ChangeSet@1.2119.1.1, 2006-02-01 13:28:45+03:00) on SuSE 9.3.

The issue is the ALTER TABLE statement in the bin log. you can actually force this to happen on MyISAM as well:
Using MyISAM

connection slave;
stop slave;
reset slave;
connection master;
CREATE TABLE t1 (name varchar(64), age smallint(3));
INSERT INTO  t1 SET name='Andy', age=31;
INSERT t1 SET name='Jacob', age=2;
INSERT into t1 SET name='Caleb', age=1;
reset master;
connection slave;
CREATE TABLE t1 (name varchar(64), age smallint(3));
INSERT INTO  t1 SET name='Andy', age=31;
INSERT into t1 SET name='Caleb', age=1;
INSERT t1 SET name='Jacob', age=2;
start slave;
connection master;
ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1 ORDER BY id;
save_master_pos;
connection slave;
sync_with_master;
SELECT * FROM t1 ORDER BY id;
connection master;
drop table t1;
save_master_pos;
connection slave;
sync_with_master;

In addition tomas wanted me to add:

tomas	so I guessed, another way is to e.g. run optimize table on the slave before the alter... it will in general yield the same discrepancy in the general case
tomas	jeb, load table from master perhaps would result in the same in a more complex case
tomas	mysql dump and restore on slave for sure....
tomas	will reorder the data
tomas	so I think it is a valid bug
tomas	even if the test case you did is a bit awkward...

The bug is not related to row-based replication. It is the result of replication of the ALTER TABLE statement.

Add text to the following effect near the description of the ALTER TABLE statement.

"Note that when replication is used, adding an AUTO_INCREMENT column to a table might not produce the same ordering on the rows on the slave and the master. The reason is that the  order in which the rows are assigned numbers is dependent on the specific storage engine used for the table and the order in which the rows were inserted. If it is important to have the same order on the master and slave, the rows has to be ordered before before assigning a AUTO_INCREMENT number. Assuming that we want to add an AUTO_INCREMENT column to the table T1, the following statements will produce a new table T2 identical to T1 but with an AUTO_INCREMENT column:

CREATE TABLE T2 (id INT AUTO_INCREMENT PRIMARY KEY) 
SELECT * FROM T1 ORDER BY col1, col2;

We here assume that col1 and col2 are the columns of table T1."

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

I've added the text to these sections:

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

Thanks, but this is 5.1 and the links above are 5.0, where does it merge?

It will appear in the 5.1 manual as well.

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
http://dev.mysql.com/doc/refman/5.1/en/replication-features.html

The test case needs to be enabled (and probably fixed) before closing this bug report.

In MySQL 5.1 disabled.def:

rpl_ndb_relay_space      : BUG#16993 2006-02-16 jmiller RBR: ALTER TABLE ZEROFILL AUTO_INCREMENT is not replicated correctly