MySQL Bugs: #51406: Changing order of columns breaks Row-Based Replication

Bug #51406	Changing order of columns breaks Row-Based Replication
Submitted:	22 Feb 2010 21:49	Modified:	29 Apr 2010 9:42
Reporter:	Ryan McElroy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
Changing the order of columns on the slave will break row-based replication.

How to repeat:
Set up a master and slave using row-based replication, then

Master:
mysql> create table rpt (a int, b int);
Query OK, 0 rows affected (0.10 sec)
 
Slave:
mysql> drop table rpt;
Query OK, 0 rows affected (0.01 sec)
 
mysql> create table rpt (b int, a int);
Query OK, 0 rows affected (0.02 sec)
 
Master:
mysql> insert into rpt (a, b) values (1, 2);
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from rpt;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)
 
Slave
mysql> select * from rpt;
+------+------+
| b    | a    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

Suggested fix:
- Add column names to RBR

- Change documentation (http://dev.mysql.com/doc/refman/5.1/en/replication-features-differing-tables.html) to point out that even if the number of columns is the same, order still matters between master and slave

Thank you for the report.

Verified as described. Behavior is not consistent with statement-based replication.

With version 5.5.99 replication fails with error.

Test case which I used:

$cat rpl_bug51406.test
--source include/master-slave.inc

create table rpt (a int, b int);

sleep 1;
connection slave;
drop table rpt;
create table rpt (b int, a int);

connection master;
insert into rpt set a=1, b=2;
insert into rpt (a, b) values (1, 2);
select * from rpt;

sleep 1;
connection slave;
select * from rpt;

Set lead, corrected version, and assigned to myself for resolution.

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