MySQL Bugs: #26418: Slave out of sync after CREATE/DROP TEMPORARY TABLE + ROLLBACK on master

Bug #26418	Slave out of sync after CREATE/DROP TEMPORARY TABLE + ROLLBACK on master
Submitted:	15 Feb 2007 18:00	Modified:	18 Jul 2007 16:46
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
The file with expected results for testcase rpl_ddl is
partially wrong. It contains the following bug
around CREATE/DROP TEMPORARY TABLE.

Simplified testcase derived from rpl_ddl.test
==============================================
Master:
SET AUTOCOMMIT = 1;
DROP DATABASE IF EXISTS mysqltest1;
CREATE DATABASE mysqltest1;
CREATE TEMPORARY TABLE mysqltest1.tmp (f1 BIGINT);
CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE="InnoDB";
SET AUTOCOMMIT = 0;

-------- switch to slave --------
SET AUTOCOMMIT = 1;

-------- switch to master --------
INSERT INTO mysqltest1.t1 SET f1= 1;
DROP TEMPORARY TABLE mysqltest1.tmp;
ROLLBACK;
SHOW CREATE TABLE mysqltest1.tmp;
ERROR 42S02: Table 'mysqltest1.tmp' doesn't exist
SELECT * FROM mysqltest1.t1;
f1

-------- switch to slave --------
SELECT * FROM mysqltest1.t1;
f1
1                 <----------- This record should not be here.

Content of log master-bin.*
...
use `test`; BEGIN
use `test`; INSERT INTO mysqltest1.t1 SET f1= 1
use `test`; DROP TEMPORARY TABLE mysqltest1.tmp
use `test`; ROLLBACK
...

Content of log slave-bin.*
...
use `test`; INSERT INTO mysqltest1.t1 SET f1= 1
          <------ I assume this INSERT should not be here.
use `test`; DROP TEMPORARY TABLE mysqltest1.tmp
...

Master-Slave combinations (checked at different times with different
                           Changesets):
   InnoDB - MyISAM (rpl_ddl)          This bug
   Falcon - MyISAM (not pushed test)  This bug
   NDB - MyISAM (rpl_ndb_ddl)         correct !!

My environment:
- MySQL 5.1 last ChangeSet@1.2434, 2007-02-13
- Linux openSUSE 10.2 (X86-64), Core2Duo (x86 64 Bit)
I guess that older MySQL versions suffer probably from the same bug.

How to repeat:
Please use the attached testcase.
cd mysql-test
cp <rpl_mlx.test> t
echo "Dummy result" > r/rpl_mlx.test
./mysql-test-run.pl rpl_mlx
Inspect r/rpl_mlx.reject

test script

Attachment: rpl_mlx.test (application/octet-stream, text), 2.04 KiB.

Pushed into 5.1.18-beta

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/28975

ChangeSet@1.2550, 2007-06-18 13:51:04+03:00, gkodinov@magare.gmz +14 -0
  Bug #26418: Slave out of sync after 
   CREATE/DROP TEMPORARY TABLE + ROLLBACK on master
  
  To compensate for that fact that CREATE TEMPORARY 
  TABLE is processed outside the current client 
  transaction the replication master records the log for
  roll-backed transactions as well if they happen to
  contain CREATE/DROP TEMPORARY TABLE.
  This causes problems if there are DML statements 
  in the same transaction on tables that have 
  different engine types between the slave and the master.
  In such cases the storage engine on the slave may not
  be able to rollback the changes in the DML target tables.
  
  DROP TEMPORARY TABLE doesn't cause an implicit
  commit. It is also rolled back by a ROLLBACK statement.
  It is an ordinary statement in that respect.
  
  Fixed the bug by not sending to the replication slave 
  the transaction log of a rolled back transaction 
  (followed by a ROLLBACK) when there is DROP TEMPORARY 
  TABLE in the transction.
  
  Also replaced repeating code with a function call.
  Added a separate test case (to be merged later to the
  updated rpl_dll.test).

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/29016

ChangeSet@1.2550, 2007-06-18 19:42:18+03:00, gkodinov@magare.gmz +14 -0
  Bug #26418: Slave out of sync after 
   CREATE/DROP TEMPORARY TABLE + ROLLBACK on master
  
  The transaction ability of the storage engines of
  the tables on the replication master and the replication
  slave must generally be the same.
  When the storage engine type of the slave is 
  non-transactional then transactions on the master that 
  mix update of transactional and non-transactional tables
  should be avoided because they will cause inconsistency of
  the data between the master's transactional table and the
  slave's non-transactional table.
  
  The effect described by this bug is actually expected.
  A detailed test case is added (to be merged later to
  the updated rpl_ddl.test), as there was no coverage 
  by the existing tests. 
  Some code cleanup is also added by this change.

Pushed into 5.1.21-beta

No changelog entry needed, but I've updated the replication chapter to amplify on the dangers of replicating transactional master tables to non-transactional slave tables when the master side mixes updates of transactional and non-transactional tables.