Bug #55605 Erroneous CREATE TABLE IF NOT EXISTS not written to binlog makes servers diverge

• Submitted: 28 Jul 2010 14:12
• Modified: 30 Jul 2010 12:47
• Reporter: Elena Stepanova
• Status: Duplicate
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.1, 5.5, 5.1-wl5370
• OS: Any
• CPU Architecture: Any
• Triage: Needs Triage: D3 (Medium)

[28 Jul 2010 14:12] Elena Stepanova

Description:
If a table t already exists, and CREATE TABLE IF NOT EXISTS t SELECT .. fails with an error, such as duplicate key, after having inserted some rows, the event is not written into the binary log, thus causing difference between master and slave data.

For the provided test case:
...
# Table contents on master:
SELECT * FROM t;
i
1
2
# Table contents on slave:
SELECT * FROM t;
i
SHOW BINLOG EVENTS;
...

The problem existed in 5.1 and 5.5, and still exists in 5.1-wl5370 (is not applicable to 5.5-wl5370 as there the statement does not insert anything on master).

How to repeat:
--source include/master-slave.inc

--disable_warnings
DROP TABLE IF EXISTS t, t1;
--enable_warnings

CREATE TABLE t (i INT, UNIQUE INDEX i(i));
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1),(2),(1);

--error ER_DUP_ENTRY
CREATE TABLE IF NOT EXISTS t
  AS SELECT * FROM t1;

--echo # Table contents on master:
SELECT * FROM t;

--sync_slave_with_master

--echo # Table contents on slave:
SELECT * FROM t;

--connection master
SHOW BINLOG EVENTS;
DROP TABLE t, t1;

--exit

[30 Jul 2010 12:47] Elena Stepanova

As Libing noted, this bug is a duplicate of Bug#51501, setting the status accordingly.