enterprise-support team mailing list archive

[Miguel Angel Nieto <1322034@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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.

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

** Affects: mysql-server
     Importance: Unknown
         Status: Unknown

** Affects: percona-server
     Importance: Undecided
         Status: New

** Bug watch added: MySQL Bug System #72666
   http://bugs.mysql.com/bug.php?id=72666

** Also affects: mysql-server via
   http://bugs.mysql.com/bug.php?id=72666
   Importance: Unknown
       Status: Unknown

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1322034

Title:
  DROP TEMPORARY TABLE with myisam causes implicit commit with unsafe
  sql

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1322034/+subscriptions