MySQL Bugs: #12224: Strange results with 'create/drop table if (not) exists'

Bug #12224	Strange results with 'create/drop table if (not) exists'
Submitted:	27 Jul 2005 20:48	Modified:	16 Aug 2005 16:36
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.7-beta, 5.0.9-beta, 5.0.10-beta	OS:	Linux (Linux, Windows, Solaris)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
This feature request is based on bug report http://bugs.mysql.com/bug.php?id=12024, which has been marked as 'not a bug':

There are some strange results when doing a 'create table if not exists', mostly
if it's executed in a stored procedure.

If I run the query

create table if not exists textx select 'x';

twice, I have one table with 2 rows containing the value 'x'. I would expect
that there is only one 'x' in it, because the second query should have no
affect.

There are more strange results when I execute a stored procedure which creates
or drops a table using if (not) exists. Example 1 shows the problems when I
tried to create a table using a SP, example 2 when I tried to drop a table. In
example 2 I get an error message on my second try to run the SP, although I
would expect that it just ignores the fact that the table doesn't exist.

How to repeat:
-- EXAMPLE 1 --

DELIMITER //

DROP PROCEDURE IF EXISTS sptest //
CREATE PROCEDURE sptest ()
BEGIN
  create table if not exists testx select 'x';
END //

DELIMITER ;

mysql> call sptest();
ERROR 1146 (42S02): Table 'mpopp_test.testx' doesn't exist
mysql> create table if not exists testx select 'x';
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create table if not exists testx select 'x';
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from testx;
+---+
| x |
+---+
| x |
| x |
+---+
2 rows in set (0.05 sec)

mysql> call sptest();
Query OK, 0 rows affected (0.05 sec)

mysql> call sptest();
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select * from testx;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: mpopp_test

+---+
| x |
+---+
| x |
| x |
+---+
2 rows in set (0.42 sec)

-- EXAMPLE 2 --

mysql> CREATE PROCEDURE sptest ()
    -> BEGIN
    ->   drop table if exists testx;
    -> END //
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> DELIMITER ;
mysql> call sptest();
Query OK, 0 rows affected (0.05 sec)

mysql> call sptest();
ERROR 1146 (42S02): Table 'mpopp_test.testx' doesn't exist

Suggested fix:
create table if not exists would make me expect that the complete command is being ignored, if the table already exists. That a row is still being inserted does not seem logical. Also, a 'drop table if exists' in a SP shouldn't produce an error message, when the SP is executed (Example 2). Instead, the drop table should also be ignored, if the table does not exist.

Besides it shouldn't happen that the server goes temporarily away when calling the Stored Procedure in Example 1.

Thank you for your interesting bug report.

As for your suggested fix for Example 1, you are simply wrong. It is clearly explained in http://dev.mysql.com/doc/mysql/en/create-table.html:

"In MySQL 3.23 or later, you can use the keywords IF NOT EXISTS so that an error does not occur if the table exists. Note that there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement. Also, if you use IF NOT EXISTS in a CREATE TABLE ... SELECT statement, any records selected by the SELECT part are inserted regardless of whether the table already exists."

Your suggestion on Example 2 is also contrary to the manual (http://dev.mysql.com/doc/mysql/en/drop-table.html):

"In MySQL 3.22 or later, you can use the keywords IF EXISTS to prevent an error from occurring for tables that don't exist. As of MySQL 4.1, a NOTE is generated for each non-existent table when using IF EXISTS."

Dropping non-existent table in plain SQL produce either error or warning:

mysql> drop table ttt;
ERROR 1051 (42S02): Unknown table 'ttt'
mysql> drop table if exists ttt;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> show warnings;
+-------+------+---------------------+
| Level | Code | Message             |
+-------+------+---------------------+
| Note  | 1051 | Unknown table 'ttt' |
+-------+------+---------------------+
1 row in set (0,00 sec)

So, these are documented features, even if they seems not logical to you.

But your Example 1 really shows bugs:

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.0.10-beta |
+-------------+
1 row in set (0.12 sec)

mysql> use test
Database changed
mysql> delimiter //
mysql> DROP PROCEDURE IF EXISTS sptest //
RE sptest Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql> CREATE PROCEDURE sptest ()
    -> BEGIN
    ->   create table if not exists testx select 'x';
    -> END //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> call sptest();
ERROR 1146 (42S02): Table 'test.testx' doesn't exist

That is bug #1, because, as the following statement shows, it should simply work without errors or warnings:

mysql> create table if not exists testx select 'x';
Query OK, 1 row affected (0.29 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from testx;
+---+
| x |
+---+
| x |
+---+
1 row in set (0.13 sec)

mysql> call sptest();
Query OK, 0 rows affected (0.01 sec)

mysql> 050728 16:30:05  mysqld restarted

This is bug #2 (server restart)

mysql> call sptest();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 0 rows affected (0.42 sec)

mysql> 050728 16:31:12  mysqld restarted

mysql> select * from testx;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+---+
| x |
+---+
| x |
+---+
1 row in set (0.39 sec)

mysql> call sptest();
Query OK, 0 rows affected (0.00 sec)

mysql> 050728 16:32:04  mysqld restarted

One more restart...

mysql> show create table testx;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+-------+-----------------------------------------------------------------------
-----------------------------+
| Table | Create Table
                             |
+-------+-----------------------------------------------------------------------
-----------------------------+
| testx | CREATE TABLE `testx` (
  `x` varchar(1) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-----------------------------+
1 row in set (0.02 sec)

So, this is how it "works" on 5.1.10 with MyISAM tables on Solaris 2.9.

On Windows with 5.0.9-nt and InnoDB table I've got:

mysql> call sptest();
Query OK, 0 rows affected (0.05 sec)

mysql> call sptest();
Query OK, 0 rows affected (0.02 sec)

mysql> show create table testx;
+-------+-----------------------------------------------------------------------
--------------------------------------------------+
| Table | Create Table
                                                  |
+-------+-----------------------------------------------------------------------
--------------------------------------------------+
| testx | CREATE TABLE `testx` (
  `x` varchar(1) character set latin1 NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------+
1 row in set (0.07 sec)

mysql> select * from testx;
+---+
| x |
+---+
| x |
| x |
| x |
+---+
3 rows in set (0.00 sec)

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.9-beta-nt |
+---------------+

That is, normal, expected (from the manual) results.

On 5.0.7-beta and Linux with MyISAM - server restart, as you shown.

So, it may even depend on table type.

I was told in my last bug report that those behaviors are documented and not really bugs, so I signed this one as feature request. If it's really SQL standard (I don't know) it's OK, but otherwise I think, it should be changed to make it more logical.

That the server goes away seems to be really a bug because that should never happen, whether the statement itself is correct or not.

The fact that CREATE TABLE IF NOT EXISTS ... SELECT is implicitly handled like INSERT ... SELECT, if the table exists already, is by design. We spent a lot of effort to make this working. I admit that it is not intuitive, but I guess someone requested for it. This behaviour is in place since long and it is unlikely that we will change it. If you have a nice idea for a syntax extension for requesting not to insert if the table exists, then we will think about it. Might it even be worth to spend a new SQL MODE for it?

The stored procedures problems are another thing. It needs to be fixed eventually. If we do not support DDL in stored procedures for now, then we should reject it, but not crash.

Issues with SPs have been fixed by the fix for BUG#8072, BUG#8766, BUG#9563, BUG#11126. I've checked with the current bk tree.

Indeed. I cannot repeat it anymore with 5.0.11.