MySQL Bugs: #30521: strange: CREATE TEMPORARY TABLE .. AS SELECT is only partially atomic

Bug #30521	strange: CREATE TEMPORARY TABLE .. AS SELECT is only partially atomic
Submitted:	20 Aug 2007 20:49	Modified:	14 Dec 2007 20:18
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
# SHOW that CREATE TEMPORARY TABLE .. AS SELECT is
# at least partially atomic.
# That means it does not consist of two logically
# separated statements (1. CREATE + 2. INSERT)
CREATE TEMPORARY TABLE t1 (UNIQUE(f1)) ENGINE = InnoDB AS
SELECT 1 AS f1 UNION ALL SELECT 1;
--> ERROR 23000: Duplicate entry '1' for key 'f1'
SELECT * FROM t1;
--> ERROR 42S02: Table 'test.t1' doesn't exist

# SHOW that CREATE TEMPORARY TABLE .. AS SELECT is
# partially not atomic.
SET AUTOCOMMIT = 0;
CREATE TEMPORARY TABLE t1 (UNIQUE(f1)) ENGINE = InnoDB AS
SELECT 1 AS f1;
SELECT * FROM t1;
f1
1
ROLLBACK;
# If CREATE TEMPORARY TABLE .. AS SELECT would be
# full atomic I would see the error message
# "Table 'test.t1' doesn't exist" for the next statement.
SELECT * FROM t1;
f1

The second effect is strange and seems to be not covered
by the manual.
I assume that we have this effect in several MySQL
versions and on all platforms.

How to repeat:
Please use the attached testcase ml040.test.

Suggested fix:
IMHO there are three thinkable fixes:
a) Make DDL commands transactional.
   Great, but I am aware that this causes a lot of work, 
   problems around connections having tables altered but the
   changes not committed ...
   There might be huge blockings within the server.
   And most customers do not really need this feature. 
b) CREATE TEMPORARY TABLE .. AS SELECT .. does an implicit
   COMMIT at end of successful execution like
   CREATE TABLE.
c) Document the current behaviour within the manual.

I prefer in the moment b)

test script

Attachment: ml040.test (application/octet-stream, text), 798 bytes.

We want to allow CREATE TEMPORARY TABLE in stored functions.
An explicit or implicit commit is not allowed in stored functions.
Other options are infeasible.
Should be documented in the manual.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Revised description for
http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html:

CREATE TABLE and DROP TABLE do not commit a transaction if the
TEMPORARY keyword is used. (This does not apply to other operations
on temporary tables such as CREATE INDEX, which do cause a commit.)
However, although no implicit occurs, neither can the statement be
rolled back. Therefore, use of such statements will violate
transaction atomicity: For example, if you use CREATE TEMPOARARY
TABLE and then roll back the transaction, the table remains in
existence.