Bug #16766 binlog stores updates to already-dropped table
Submitted: 25 Jan 2006 2:40 Modified: 25 Jan 2006 16:52
Reporter: Timothy Smith Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.1.16, 5.0.18 OS:Any (all)
Assigned to: CPU Architecture:Any

[25 Jan 2006 2:40] Timothy Smith
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
[25 Jan 2006 16:52] Timothy Smith
This bug is a duplicate of bug #989.  Also, see bug #12347 for more details.