Bug #20863 If binlog format is changed between update and unlock of tables, wrong binlog
Submitted: 5 Jul 2006 8:44 Modified: 25 Sep 2006 20:07
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.1 OS:Linux (linux)
Assigned to: Mats Kindahl CPU Architecture:Any

[5 Jul 2006 8:44] Guilhem Bichot
Description:
set session binlog_format=statement;
create table t11 (data varchar(255));
lock tables t11 write;
set session binlog_format=row;
insert into t11 values("try_561_");
set session binlog_format=statement;
unlock tables;

Then the master's binlog will contain a table map event but no rows event, so slave will not be able to repeat the insertion.

This problem affects the mixed mode too (same causes):

set session binlog_format=mixed;
create table t11 (data varchar(255));
lock tables t11 write;
insert into t11 values(UUID());
unlock tables;

How to repeat:
see description.

Suggested fix:
The problem is that the flushing of the rows event is done by close_thread_tables(), which is not called here at the end of the INSERT, but at UNLOCK TABLES. And as when in UNLOCK TABLES we are not in row-based mode, THD::flush_pending_rows_event() does nothing.

Mere suggestion: THD::flush_pending_rows_event() could do some flushing as long as it sees that a pending event exists, even if not in row-based at the moment.
[5 Jul 2006 8:48] Guilhem Bichot
When you fix it, on top of a testcase with SET BINLOG_FORMAT, please also add the testcase for the mixed mode (you can find it in this bug report) to rpl_switch_stm_row_mixed.test.
[10 Jul 2006 14:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9000
[16 Aug 2006 12:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10541

ChangeSet@1.2234, 2006-08-16 14:55:57+02:00, mats@romeo.(none) +4 -0
  BUG#20863 (if binlog format is changed between update and unlock table, wrong binlogging):
  Fix to allow the binlog format to be changed between the LOCK and
  UNLOCK tables, as well as under MIXED mode.
[17 Aug 2006 7:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10573

ChangeSet@1.2250, 2006-08-17 09:31:46+02:00, mats@romeo.(none) +2 -0
  BUG#20863 (if binlog format is changed between update and unlock of table, wrong binlog):
  Post-merge fixes.
[23 Sep 2006 7:35] Lars Thalmann
Pushed into 5.1.12
[25 Sep 2006 20:07] Paul Dubois
Noted in 5.1.12 changelog.

If the binary logging format was changed between the times when a
locked table was modified and when it was unlocked, the binary log
contents were incorrect.