Description:
It is possible to create a binlog that contains statements like:
DROP TABLE IF EXISTS foo;
INSERT INTO foo VALUES (1);
COMMIT;
CREATE TABLE foo (id int) ENGINE=InnoDB;
Since MySQL logs all of a transaction's statements in one chunk, and DDL statements (like CREATE or DROP TABLE) are not transactional, it is easy to cause many different forms of this problem.
How to repeat:
I tested this on MySQL 4.1.16 and 5.0.18, on FreeBSD.
-- In window 1:
flush logs;
show master status;
create table i1 (id int unsigned primary key) engine=innodb;
begin;
insert into i1 values (1);
insert into i1 values (2);
-- In window 2:
drop table if exists i1;
-- In window 1:
commit;
-- In window 2:
create table i1 (id int unsigned primary key) engine=innodb;
show master status;
The resulting binlog contains the buggy statements record. I.e., the
DROP TABLE is logged before the INSERT statements on that table.
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#060124 17:54:17 server id 33410 log_pos 4 Query thread_id=5 exec_time=0 error_code=0
use test;
SET TIMESTAMP=1138154057;
create table i1 (id int unsigned primary key) engine=innodb;
# at 98
#060124 17:54:43 server id 33410 log_pos 98 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1138154083;
drop table if exists i1;
# at 156
#060124 17:54:53 server id 33410 log_pos 156 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1138154093;
BEGIN;
# at 196
#060124 17:54:17 server id 33410 log_pos 98 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1138154057;
insert into i1 values (1);
# at 256
#060124 17:54:17 server id 33410 log_pos 98 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1138154057;
insert into i1 values (2);
# at 316
#060124 17:54:53 server id 33410 log_pos 316 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1138154093;
COMMIT;
# at 357
#060124 17:55:02 server id 33410 log_pos 357 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1138154102;
create table i1 (id int unsigned primary key) engine=innodb;
Suggested fix:
n/a