Luís Soares wrote:
> Hi,
>
> I didn't get it. Is it the case that the pending event is
> not flushed at the end of the first statement (or OTOH, it
> is flushed too soon), and several rows from different
> statements end up in the same rows event?
>
> If this is the case, can't we make sure that the pending
> event is flushed when it has to, and write the ignorable
> event before it?
>
The problem is that the '6' and '7' values are binlogged into one rows
event.
The '6' value is from the first statement, and the '7' value is from the
second statement in the case. Jasonh, mats, and I have a detailed discussion
on IRC about this. You can check it. Thanks!
Best Regards,
Daogang
> Regards,
> Luís
>
> On Mon, 2010-06-07 at 18:13 +0800, Daogang Qu wrote:
>
>> He Zhenxing wrote:
>>
>>> Hi Daogang,
>>>
>>> Nice work! Patch looks good, please look for some comments!
>>>
>>> STATUS
>>> ------
>>> Not Approved!
>>>
>>> REQUIRED CHANGES
>>> -----------------
>>> RC1. The special comment will have a problem when there is also
>>> (special) comment in the original query, you could try the following,
>>> which will result in a syntax error:
>>>
>>> /*!50000 create /*!50000 table */ t1 (a int) */;
>>>
>>> Failed: 1064: You have an error in your SQL syntax; check the manual
>>> that corresponds to your MySQL server version for the right syntax to
>>> use near '*/' at line 1
>>>
>>> One suggestion is to output the query using '#' comment, and also use a
>>> BINLOG statement to ship the Rows_query_log_event so that it can be
>>> applied and relayed.
>>>
>>> # original query
>>> BINLOG '...'
>>>
>>>
>> Updated.
>>
>>> You may think of other alternatives.
>>>
>>> RC2. The handling of insert delayed is not correct, multiple insert
>>> delayed run in sequence or in parallel will result in wrong binlog.
>>>
>>> insert delayed into t1 values (1),(2);
>>> insert delayed into t1 values (3),(4),(5),(6)
>>>
>>> It seems that if multiple insert delayed are running in parallel the
>>> rows can be intermixed. So I think you need to have a logic to check if
>>> the new row is belong to the same query of the previous row, if it is
>>> not, then write a new Rows_query_log event before the new row.
>>>
>>>
>>>
>> According to current mechanism, Sometimes the multiple 'insert delayed
>> .....' statements
>> are executed in a transaction, but sometimes everyone is executed in a
>> separate transaction.
>>
>> 1. The Rows_query_log_event will be displayed rightly by MYSQLBINLOG if
>> everyone
>> is executed in a separate transaction as following:
>> # at 571
>> #100607 17:46:50 server id 1 end_log_pos 632 Rows_query
>> # insert delayed into t1(a) values(1),(6)
>> # at 632
>> # at 674
>> #100607 17:46:50 server id 1 end_log_pos 674 Table_map: `test`.`t1`
>> mapped to number 24
>> #100607 17:46:50 server id 1 end_log_pos 712 Write_rows: table id 24
>> flags: STMT_END_F
>>
>> BINLOG '
>> CsAMTB0BAAAAPQAAAHgCAACAACkjIGluc2VydCBkZWxheWVkIGludG8gdDEoYSkgdmFsdWVzKDEp
>> LCg2KQ==
>> CsAMTBMBAAAAKgAAAKICAAAAABgAAAAAAAEABHRlc3QAAnQxAAIDAwAC
>> CsAMTBcBAAAAJgAAAMgCAAAQABgAAAAAAAEAAv/8AQAAAAEAAAA=
>> '/*!*/;
>> ### INSERT INTO test.t1
>> ### SET
>> ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
>> ### @2=1 /* INT meta=0 nullable=1 is_null=0 */
>> # at 712
>> # at 754
>> #100607 17:46:50 server id 1 end_log_pos 754 Table_map: `test`.`t1`
>> mapped to number 24
>> #100607 17:46:50 server id 1 end_log_pos 792 Write_rows: table id 24
>> flags: STMT_END_F
>> BINLOG '
>> CsAMTBMBAAAAKgAAAPICAAAAABgAAAAAAAEABHRlc3QAAnQxAAIDAwAC
>> CsAMTBcBAAAAJgAAABgDAAAQABgAAAAAAAEAAv/8AgAAAAYAAAA=
>> '/*!*/;
>> ### INSERT INTO test.t1
>> ### SET
>> ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
>> ### @2=6 /* INT meta=0 nullable=1 is_null=0 */
>> # at 792
>> #100607 17:46:50 server id 1 end_log_pos 861 Query thread_id=6
>> exec_time=0 error_code=0
>> SET TIMESTAMP=1275904010/*!*/;
>> COMMIT
>> /*!*/;
>> # at 861
>> #100607 17:46:50 server id 1 end_log_pos 929 Query thread_id=6
>> exec_time=0 error_code=0
>> SET TIMESTAMP=1275904010/*!*/;
>> BEGIN
>> /*!*/;
>> # at 929
>> #100607 17:46:50 server id 1 end_log_pos 999 Rows_query
>> # insert delayed into t1(a) values(7),(8),(9),(10)
>> # at 999
>> # at 1041
>> #100607 17:46:50 server id 1 end_log_pos 1041 Table_map: `test`.`t1`
>> mapped to number 24
>> #100607 17:46:50 server id 1 end_log_pos 1079 Write_rows: table id 24
>> flags: STMT_END_F
>>
>> BINLOG '
>> CsAMTB0BAAAARgAAAOcDAACAADIjIGluc2VydCBkZWxheWVkIGludG8gdDEoYSkgdmFsdWVzKDcp
>> LCg4KSwoOSksKDEwKQ==
>> CsAMTBMBAAAAKgAAABEEAAAAABgAAAAAAAEABHRlc3QAAnQxAAIDAwAC
>> CsAMTBcBAAAAJgAAADcEAAAQABgAAAAAAAEAAv/8AwAAAAcAAAA=
>> '/*!*/;
>> ### INSERT INTO test.t1
>> ### SET
>> ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
>> ### @2=7 /* INT meta=0 nullable=1 is_null=0 */
>> # at 1079
>> # at 1121
>> #100607 17:46:50 server id 1 end_log_pos 1121 Table_map: `test`.`t1`
>> mapped to number 24
>> #100607 17:46:50 server id 1 end_log_pos 1177 Write_rows: table id 24
>> flags: STMT_END_F
>> BINLOG '
>> CsAMTBMBAAAAKgAAAGEEAAAAABgAAAAAAAEABHRlc3QAAnQxAAIDAwAC
>> CsAMTBcBAAAAOAAAAJkEAAAQABgAAAAAAAEAAv/8BAAAAAgAAAD8BQAAAAkAAAD8BgAAAAoAAAA=
>> '/*!*/;
>> ### INSERT INTO test.t1
>> ### SET
>> ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
>> ### @2=8 /* INT meta=0 nullable=1 is_null=0 */
>> ......
>>
>>
>>
>> 2. The Rows_query_log_event will not displayed very rightly by
>> MYSQLBINLOG if the multiple
>> 'insert delayed .....' statements are executed in a transaction. But I
>> think It's acceptable as
>> they are in a transaction. Seems we don't have way to correct it.
>> See below:
>> BEGIN
>> /*!*/;
>> # at 571
>> #100607 17:50:11 server id 1 end_log_pos 632 Rows_query
>> # insert delayed into t1(a) values(1),(6)
>> # at 632
>> # at 674
>> #100607 17:50:11 server id 1 end_log_pos 674 Table_map: `test`.`t1`
>> mapped to number 24
>> #100607 17:50:11 server id 1 end_log_pos 712 Write_rows: table id 24
>> flags: STMT_END_F
>>
>> BINLOG '
>> 08AMTB0BAAAAPQAAAHgCAACAACkjIGluc2VydCBkZWxheWVkIGludG8gdDEoYSkgdmFsdWVzKDEp
>> LCg2KQ==
>> 08AMTBMBAAAAKgAAAKICAAAAABgAAAAAAAEABHRlc3QAAnQxAAIDAwAC
>> 08AMTBcBAAAAJgAAAMgCAAAQABgAAAAAAAEAAv/8AQAAAAEAAAA=
>> '/*!*/;
>> ### INSERT INTO test.t1
>> ### SET
>> ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
>> ### @2=1 /* INT meta=0 nullable=1 is_null=0 */
>> # at 712
>> # at 754
>> # at 792
>> #100607 17:50:11 server id 1 end_log_pos 862 Rows_query
>> # insert delayed into t1(a) values(7),(8),(9),(10)
>> # at 862
>> #100607 17:50:11 server id 1 end_log_pos 900 Write_rows: table id 24
>> flags: STMT_END_F
>>
>> BINLOG '
>> 08AMTBMBAAAAKgAAAPICAAAAABgAAAAAAAEABHRlc3QAAnQxAAIDAwAC
>> 08AMTBcBAAAAJgAAABgDAAAAABgAAAAAAAAAAv/8AgAAAAYAAAA=
>> ### INSERT INTO test.t1
>> ### SET
>> ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
>> ### @2=6 /* INT meta=0 nullable=1 is_null=0 */
>> 08AMTB0BAAAARgAAAF4DAACAADIjIGluc2VydCBkZWxheWVkIGludG8gdDEoYSkgdmFsdWVzKDcp
>> LCg4KSwoOSksKDEwKQ==
>> 08AMTBcBAAAAJgAAAIQDAAAQABgAAAAAAAEAAv/8AwAAAAcAAAA=
>> '/*!*/;
>> ### INSERT INTO test.t1
>> ### SET
>> ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
>> ### @2=7 /* INT meta=0 nullable=1 is_null=0 */
>> ......
>>
>>
>> Luis and Zhenxing, What's your suggestion?
>>
>> Best Regards,
>>
>> Daogang
>>
>>
>>
>>
>>
>
>
>
>
