MySQL Bugs: #15522: create ... select and with merge tables drops underlying table

Bug #15522	create ... select and with merge tables drops underlying table
Submitted:	6 Dec 2005 15:51	Modified:	24 Oct 2007 3:08
Reporter:	Sergei Golubchik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S1 (Critical)
Version:	5.0.21-BK, 4.0	OS:	Any
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
This is a "create ... select" part of the bug#5390

How to repeat:
drop table if exists t1, t2, t3;
create table t1(a int);
create table t2(a int);
insert into t1 values (0), (1);
insert into t2 values (2);
create table t3 (a int) engine=merge union=(t1, t2) insert_method=first;

create table if not exists t1 select * from t3

I've got the following results on 5.0.21-BK on Linux:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.21    |
+-----------+
1 row in set (0.01 sec)

mysql> drop table if exists t1, t2, t3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2(a int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (0), (1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> create table t3 (a int) engine=merge union=(t1, t2) insert_method=first;
Query OK, 0 rows affected (0.01 sec)

mysql> create table if not exists t1 select * from t3;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> create table if not exists t1 select * from t3;
ERROR 1105 (HY000): MyISAM table 't1' is in use (most likely by a MERGE table).
Try FLUSH TABLES.
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> create table if not exists t1 select * from t3;
ERROR 1017 (HY000): Can't find file: 't3' (errno: 2)

create table if not exists t1 select * from t3

can even DROP an existing table.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31417

ChangeSet@1.2531, 2007-07-22 10:47:11+02:00, istruewing@chilla.local +2 -0
  Bug#15522 - create ... select and with merge tables
  Added test case.
  The fix for Bug#20662 does also fix this one.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35189

ChangeSet@1.2531, 2007-10-09 13:10:51+02:00, istruewing@stella.local +2 -0
  Bug#15522 - create ... select and with merge tables
  Added test case.
  The fix for Bug#20662 does also fix this one.

Queued to 6.0-engines, 5.2-engines, 5.1-engines, 5.0-engines

Pushed into 5.1.23-beta

Pushed into 5.0.52

The pushes for 5.0.52, 5.1.23 are only for a test case. Adding this bug number to the changelog entry for Bug#20662, which is where the fix actually occurred.