Description:
Per documentation, DROP TEMPORARY TABLE should not result in commit.
http://dev.mysql.com/doc/refman/5.5/en/drop-table.html
"DROP TABLE automatically commits the current active transaction, unless you use the TEMPORARY keyword."
With MIXED binlog format, we found that when the temporary table is created as a MyISAM table, and when the transaction has some non-deterministic statement, the DROP TEMPORARY TABLE statement would sometimes cause a commit.
This is not a problem with SBR. Not a problem with InnoDB.
This is not reproduced on the latest 5.6.17.
How to repeat:
This unexpected behavior caught attention when it resulted in replication failure. The master starts a transaction, which runs several loops on "drop temporary table...; create temporary table...; DML...". The early commit caused the "drop temporary table" statements to be logged into binlog first, leaving some "create table" statements logged together and caused replication failure.
my.cnf
----------
binlog-format=MIXED
Create a SQL file:
-----------------
use test;
create table company (id int AUTO_INCREMENT, name varchar(20), PRIMARY KEY (id)) ENGINE=InnoDB
insert into company(name) values ("a"), ("b");
set autocommit = 0;
drop temporary table if exists tmp_test;
create temporary table tmp_test (id int, name varchar(20)) engine = myisam;
insert into tmp_test (name) select name from company where id = 2;
drop temporary table if exists tmp_test;
create temporary table tmp_test (id int, name varchar(20)) engine = myisam;
insert into tmp_test (name) select name from company limit 1;
drop temporary table if exists tmp_test;
create temporary table tmp_test (id int, name varchar(20)) engine = myisam;
insert into tmp_test (name) select name from company limit 1;
set autocommit = 1;
Run the SQL and examine binlog output:
-------------------------------------
BEGIN
DROP TEMPORARY TABLE IF EXISTS `test`.`tmp_test` /* generated by server */
COMMIT
BEGIN
DROP TEMPORARY TABLE IF EXISTS `test`.`tmp_test` /* generated by server */
create temporary table tmp_test (id int, name varchar(20)) engine = myisam
insert into tmp_test (name) select name from company where id = 2
DROP TEMPORARY TABLE IF EXISTS `test`.`tmp_test` /* generated by server */
create temporary table tmp_test (id int, name varchar(20)) engine = myisam
create temporary table tmp_test (id int, name varchar(20)) engine = myisam
COMMIT
Suggested fix:
As stated in the documentation, DROP TEMPORARY TABLE should not cause a commit.
