| 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: | |
| 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 | ||
[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.
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.