Bug #6682 CREATE TABLE .. SELECT failing but entering binary log
Submitted: 17 Nov 2004 14:17 Modified: 3 Dec 2004 17:40
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.0.23 OS:
Assigned to: Jim Winstead CPU Architecture:Any

[17 Nov 2004 14:17] Dean Ellis
Description:
A failed CREATE TABLE .. SELECT enters the binary log in the case of mixed storage engines (ie: MyISAM and InnoDB).

How to repeat:
CREATE TABLE t1 ( a int PRIMARY KEY ) TYPE=InnoDB;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);

Connection 1:
BEGIN;
SELECT a FROM t1 WHERE a= 5 FOR UPDATE;

Connection 2:
CREATE TABLE t2 TYPE=MyISAM SELECT a FROM t1 FOR UPDATE;

After lock_wait_timeout, statement enters binary log, however t2 is dropped on master due to the failed statement; on replication slave these tables are created successfully and without error, causing the slave to halt and so forth.

Suggested fix:
CREATE ... SELECT should not enter binary log on failure even if target table was supposed to be non-transactional.
[25 Nov 2004 20:11] Jim Winstead
This doesn't require InnoDB to reproduce. Another test case:

create table t1(a int);
insert into t1 values(1),(1);
create table t2(a int, unique(a)) select a from t1;

The second create table will fail with a duplicate entry error (assuming MyISAM as the default 
table type), but will still appear in the binary log. (This wouldn't cause a problem in replication, 
though, since such a query would fail equally on the slave and master. Just noting this because it 
makes it easier to write a test case to catch the underlying error of a query being logged that 
should not be.)
[25 Nov 2004 22:37] Jim Winstead
bk commit into 4.0 tree (jimw:1.2002)
[3 Dec 2004 17:39] Jim Winstead
The patch was pushed, and will be in MySQL 4.0.24, MySQL 4.1.8, and MySQL 5.0.3.

This happened because there was special logic to force logging of partially-successful 'SELECT .. 
INSERT' queries that involved non-transactional tables, and 'CREATE TABLE ... SELECT' is 
implemented as 'CREATE TABLE; SELECT ... INSERT' under the hood (basically). This logic is now 
bypassed in the 'CREATE TABLE ... SELECT' case.
[3 Dec 2004 17:40] Jim Winstead
(To clarify, the fix will be in MySQL 4.0.23.)