MySQL Bugs: #71110: "create temporary table select * from table" does not produce consistent data

Bug #71110	"create temporary table select * from table" does not produce consistent data
Submitted:	8 Dec 2013 16:31	Modified:	9 Dec 2013 19:00
Reporter:	Toni Sanavullah	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	mysql-5.6.14	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	repeatable read

Description:
For the tx_isolation with REPEATABLE-READ, when a temporary table is created within a transaction, it picks up committed transaction by other session.

How to repeat:
CREATE TABLE `t1` (
  `id` int(11) NOT NULL DEFAULT '0',
  `a` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

insert into t1 values ( 1, "a" );
insert into t1 values ( 2, "b" );

Open two different mysql session:

Session 1:                                                                              Session2:

start transaction;

 select * from t1;

mysql> select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
                                                                                             insert into t1 values ( 3, "c" );
                                                                                             commit;

select * from t1;

mysql> select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
create temporary table tmp_t1 select * from t1;

select * from tmp_t1;

mysql> select * from tmp_t1;
+----+------+
| id | a    |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
select * from t1;
mysql> select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+

//you will see that the rows from tmp_t1 and t1 tables are different.
//tmp_t1 has committed transactions from session 2.

Suggested fix:
"create temporary table tmp_t1 select * from t1" should produce same results as "select * from t1" for .repeatable-read.

I did not test on my environment(s) here but docs http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html say: 

"ALTER TABLE, 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 commit occurs, neither can the statement be rolled back. Therefore, use of such statements will violate transaction atomicity: For example, if you use CREATE TEMPORARY TABLE and then roll back the transaction, the table remains in existence."

Peter
(not a MySQL/Oracle person)
-- and just listening!

Thank you for the report.

Although Peter is correct and you should not issue CREATE TABLE statement before closing transaction, our manual does not explicitly says if CREATE TEMPORARY TABLE ... SELECT should see results of committed transaction. It only says when CREATE TABLE commits and does not commit current transaction.

Most likely this is user manual issue, but I verify it as server bug for now.