Description:
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
Description: 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