MySQL Bugs: #39157: Failure to create Falcon table with same name after failed CREATE TABLE SELECT

Bug #39157	Failure to create Falcon table with same name after failed CREATE TABLE SELECT
Submitted:	1 Sep 2008 14:10	Modified:	10 Sep 2008 22:43
Reporter:	Dmitry Lenev	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.7-bk	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	regression

Description:
Attempt to create non-existing Falcon table with the same name as was already used as target in unsuccessful CREATE TABLE SELECT statement fails with error 156. In other words CREATE TABLE SELECT for Falcon tables does not work atomically as it is for other engines.

How to repeat:
# Snippet for MySQL's test suite which reproduces the problem
--source include/have_falcon.inc
# For some reason it is important that source table is also Falcon table
create table t1 (i int) engine=falcon;
insert into t1 values (1), (1);
# Fails as expected
--error ER_DUP_ENTRY
create table t2 (pk int primary key) engine=falcon select i as pk from t1;
# Emits error 156 altough it should not
create table t2 (j int) engine= falcon;

Thank you for the bug report. Verified on current source tree and not repeatable on 6.0.2 released version.

c:\dbs>c:\dbs\6.0\bin\mysql -uroot --port=3600 --prompt="mysql 6.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.7-alpha-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 6.0 > create database h1;
Query OK, 1 row affected (0.03 sec)

mysql 6.0 > use h1
Database changed
mysql 6.0 > create table t1 (i int) engine=falcon;
Query OK, 0 rows affected (0.14 sec)

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

mysql 6.0 > create table t2 (pk int primary key) engine=falcon select i as pk from t1;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql 6.0 > create table t2 (j int) engine= falcon;
ERROR 1005 (HY000): Can't create table 'h1.t2' (errno: 156)
mysql 6.0 > exit
Bye

C:\temp\mysql-6.0.2-alpha-win32>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.2-alpha-community-nt-debug MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database h1;
Query OK, 1 row affected (0.00 sec)

mysql> use h1
Database changed
mysql> create table t1 (i int) engine=falcon;
Query OK, 0 rows affected (0.78 sec)

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

mysql> create table t2 (pk int primary key) engine=falcon select i as pk from t1;
ERROR 1582 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> create table t2 (j int) engine= falcon;
Query OK, 0 rows affected (0.08 sec)

mysql>

Vlad,  This bug is probably recognizing an error condition and cleaning up the system table afterwards.

This is duplicate of Bug#34892 Transaction handling in select_create::abort let's Falcon fail.