MySQL Bugs: #6682: CREATE TABLE .. SELECT failing but entering binary log

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

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.

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.)

bk commit into 4.0 tree (jimw:1.2002)

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.

(To clarify, the fix will be in MySQL 4.0.23.)