Bug #59561 Slave gets out of sync if binlog direct is on and tables share the same name
Submitted: 17 Jan 2011 17:03
Reporter: Alfranio Correia Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5+ OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: binlog_direct_non_transactional_updates, out of sync, replication
Triage: Triaged: D2 (Serious)

[17 Jan 2011 17:03] Alfranio Correia
Description:
Combining binlog_direct_non_transactional_updates and tables that share the same name, i.e.,

  1. binlog_direct_non_transactional_updates= ON;
  
  2. temporary table and regular table that share the same table *name*;

  3. the temporary table has transactional engine while regular table
  has not;

may lead to inconsistencies if the temporary table is dropped within a transaction and the regular table is updated within the same transaction.

This happens because the drop on the temporary table does not commit the transaction and the update on the regular table is logged ahead of the transaction due to binlog_direct_non_transactional_updates.

Note: It is not recommended to set binlog_direct_non_transactional_updates ON because this may cause inconsistencies under certain scenarios. Do so when
you are aware of what should be done to avoid possible inconsistencies.
See docs for further details:
(http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html)

How to repeat:
--source include/have_binlog_format_statement.inc
--source include/master-slave.inc
--source include/have_innodb.inc

connection master;
SET @@SESSION.binlog_direct_non_transactional_updates=ON;

CREATE TABLE t1(c1 INT) ENGINE= 'MyIsam';
CREATE TEMPORARY TABLE t1(c1 INT) ENGINE= 'Innodb';

BEGIN;
  INSERT INTO t1 VALUES(1);
  DROP TEMPORARY TABLE t1;
  INSERT INTO t1 VALUES(1);
COMMIT;

SELECT *, "MASTER" FROM t1;

sync_slave_with_master;

SELECT *, "SLAVE" FROM t1;

connection master;
DROP TABLE t1;
SHOW BINLOG EVENTS;
sync_slave_with_master;

--source include/rpl_end.inc

------ RESULT ------

SELECT *, "MASTER" FROM t1;
c1	MASTER
1	MASTER
SELECT *, "SLAVE" FROM t1;
c1	SLAVE