Bug #4551 Temporary InnoDB tables not replicated properly with CREATE TABLE .. SELECT
Submitted: 14 Jul 2004 21:38 Modified: 18 Jul 2004 14:37
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.0.21 OS:
Assigned to: Guilhem Bichot

[14 Jul 2004 21:38] Dean Ellis
Description:
A temporary InnoDB table created with a CREATE ... SELECT statement which results in duplicate key violations does not get created on the slave, though the table is created on the master.  Subsequent DROP TABLE for that temporary table fails on the slave, halting the slave SQL thread.

MyISAM temporary tables replicate properly.

How to repeat:
Replicate the following:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int );
INSERT INTO t1 VALUES (1),(1);
CREATE TEMPORARY TABLE t2 ( PRIMARY KEY (a) ) TYPE=INNODB SELECT a FROM t1;
DROP TABLE t2;

Suggested fix:
Presumably the CREATE TABLE should succeed on the slave (as it does on the master).
[18 Jul 2004 14:37] Guilhem Bichot
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Thank you Dean. Nice to have provided such a straight-forward test case.
ChangeSet@1.1915, 2004-07-18 14:34:35+02:00, guilhem@mysql.com