Bug #53614 ROW-base binlog result in different data .
Submitted: 13 May 2010 5:12 Modified: 7 Aug 2010 7:36
Reporter: He yunfei Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S1 (Critical)
Version:5.1.40, 5.1.48-bzr OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Tags: ALTER TABLE, RBR, table schemas
Triage: Needs Triage: D2 (Serious)

[13 May 2010 5:12] He yunfei
Description:
MYSQL in  Dual-MASTER ,  ROW-base binlog result in different data .

## Lab :  
  Dual-Master
 Master1 == 10.249.160.132
  Master2 == 10.249.160.133
  RHEL 5.4 X64, MYSQL 5.1.40 
  binlog_format =  MIXED  
  tx_isolation  = READ-COMMITTED 
  (Attention:  READ-COMMITTED + INNODB = ROW Based Binlog format )

## First  let's know how mysql record sql in row-base mode . 
eg:  update h1 set name='h-m@2' where id=5; 

BINLOG recorded that:

BINLOG '
wX3rSxMCAAAALwAAAHAGAAAAACYAAAAAAAAABHRlc3QAAmgxAAMDDw8EKADoAwY=
wX3rSxgCAAAAPQAAAK0GAAAQACYAAAAAAAEAA///+AUAAAACaDUEAGgxMTX4BQAAAAVoLW1AMgQA
aDExNQ==
'/*!*/;
### UPDATE test.h1
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='h5' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='h115' /* VARSTRING(1000) meta=1000 nullable=1 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='h-m@2' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3='h115' /* VARSTRING(1000) meta=1000 nullable=1 is_null=0 */

How to repeat:
use test; 
set names gbk; 
drop table if exists h1 ;
create table h1 (id int , name varchar(20),comment varchar(500 ) , primary key (id))
engine=innodb default charset =gbk ; 

insert into h1 values 
(1,'h1','h111'),
(2,'h2','h112'),
(3,'h3','h113'),
(4,'h4','h114'),
(5,'h5','h115');

flush logs ;

#### How to repeat .

#### Step 1 , at Master1 , 意图是让MASTER2的SQL在Master1上延时应用。
     stop slave ; 

#### Step 2 ,at Master2
     update h1 set name='h-m@2' where id=5; 
     insert into h1 values (6,'h6@2','dsflk');
     ### Have not apply on Master1
     
#### Step 3 ,at Master1 
     alter table h1 add addr varchar(500) after name ;   ### 这里故障打断原的字段顺序
     select * from h1; 
         +----+------+------+---------+
         | id | name | addr | comment |
         +----+------+------+---------+
         |  1 | h1   | NULL | h111    |
         |  2 | h2   | NULL | h112    |
         |  3 | h3   | NULL | h113    |
         |  4 | h4   | NULL | h114    |
         |  5 | h5   | NULL | h115    |
         +----+------+------+---------+
    start slave;  ### Start to apply sql log from Master 2
    select * from h1; 
         +----+-------+-------+---------+
         | id | name  | addr  | comment |
         +----+-------+-------+---------+
         |  1 | h1    | NULL  | h111    |
         |  2 | h2    | NULL  | h112    |
         |  3 | h3    | NULL  | h113    |
         |  4 | h4    | NULL  | h114    |
         |  5 | h-m@2 | h115  | h115    |   ### addr = h115 ????? wrong 
         |  6 | h6@2  | dsflk | NULL    |   ### addr = dsflk ????? wrong 
         +----+-------+-------+---------+
     
   #### At here . what we see ? 
   #### Column Addr, we have not do anything on it . bug it have data . 
   #### Column Comment for record 6 , it should be "dsflk". not "NULL"
     
     
#### Step 4 ,at Master2 , There are data looks right ;

    select * from h1;  
         +----+-------+------+---------+
         | id | name  | addr | comment |
         +----+-------+------+---------+
         |  1 | h1    | NULL | h111    |
         |  2 | h2    | NULL | h112    |
         |  3 | h3    | NULL | h113    |
         |  4 | h4    | NULL | h114    |
         |  5 | h-m@2 | NULL | h115    |
         |  6 | h6@2  | NULL | dsflk   |
         +----+-------+------+---------+

####  at last,
   Data in Master1 and Master2 are not same anymore.

Suggested fix:
should we remember which column_ID(the columnId will not change any more)  we has modified in Binlog? 

or 

force can't not change columns order.
[26 May 2010 20:26] Sveta Smirnova
Thank you for the report.

Verified as described, but only with row binlog format. With mixed everything works fine.

Test case:

--source include/master-slave.inc
--source include/have_innodb.inc

set names gbk; 
drop table if exists h1 ;
create table h1 (id int , name varchar(20),comment varchar(500 ) , primary key (id))
engine=innodb default charset =gbk ; 

insert into h1 values 
(1,'h1','h111'),
(2,'h2','h112'),
(3,'h3','h113'),
(4,'h4','h114'),
(5,'h5','h115');

flush logs ;

sleep 1;

connection slave;
stop slave;

connection master;
update h1 set name='h-m@2' where id=5; 
insert into h1 (id, name, comment) values (6,'h6@2','dsflk');

connection slave;
alter table h1 add addr varchar(500) after name ; 
select * from h1; 

start slave;
sleep 1;
select * from h1; 

Master option file:

--binlog-format=row

With next-mr replication fails with error which in my opinion is correct.
[27 May 2010 2:01] He yunfei
Thx for your reply . 

 But I cann't image what's you result . and may be your option file is not same with mine. eg: tx_isolation=?   binlog_format=?
[27 May 2010 2:01] He yunfei
Thx for your reply . 

 But I cann't image what's you result . and may be your option file is not same with mine. eg: tx_isolation=?   binlog_format=?
[27 May 2010 8:43] Sveta Smirnova
Same output like in the initial description:

select * from h1;
id      name    addr    comment
1       h1      NULL    h111
2       h2      NULL    h112
3       h3      NULL    h113
4       h4      NULL    h114
5       h-m@2   h115    h115
6       h6@2    dsflk   NULL
[1 Jun 2010 18:13] Jon Stephens
Corrected Lead.
[2 Jun 2010 1:54] guanding jin
The problem is exist,that is  concealed.suggestion:alter architecture to avoid problem;
[2 Jun 2010 2:13] He yunfei
HI, Sveta Smirnova 
in fact , I don't very understand what your means ; 

The result is correct ?  with App , or With you ? 

May be we should stand with App and to handle them .
[7 Jun 2010 20:35] Jon Stephens
He Yunfei, Guanding Jin,

Hello! 

After analysis and discussion with Replication developers, we have concluded that this is expected behaviour, and that your problem is similar to the issue encountered in BUG#51406 (order of columns in the table must remain the same on both master and slave).

I'm working on adding a better explanation/examples to the Manual, and I will update this bug report when that is done.

Thanks!

Jon Stephens
MySQL Documentation Team
Stockholm
[7 Aug 2010 6:33] Jon Stephens
Set target version, priority.
[7 Aug 2010 7:36] Jon Stephens
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.

Updated http://dev.mysql.com/doc/refman/5.1/en/replication-features-differing-tables.html#replicat... with more info and an example. Also added to 5.5/5.6/6.0 versions of this section.

Closed.