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

[6 Dec 2005 15:51] Sergei Golubchik
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
[12 Apr 2006 11:53] Valeriy Kravchuk
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)
[23 Jun 2006 9:45] Ingo Strüwing
create table if not exists t1 select * from t3

can even DROP an existing table.
[23 Jul 2007 19:06] Bugs System
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.
[9 Oct 2007 11:11] Bugs System
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.
[12 Oct 2007 8:15] Ingo Strüwing
Queued to 6.0-engines, 5.2-engines, 5.1-engines, 5.0-engines
[22 Oct 2007 17:03] Bugs System
Pushed into 5.1.23-beta
[22 Oct 2007 17:05] Bugs System
Pushed into 5.0.52
[24 Oct 2007 3:08] Paul DuBois
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.