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
