Bug #6008 MySQL does not create warnings when creating database and using IF NOT EXISTS

• Submitted: 10 Oct 2004 16:27
• Modified: 14 Sep 2005 0:39
• Reporter: Andrey Hristov
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.4+
• OS: Linux (Linux 2.6.4)
• Assigned to: Sergei Glukhov
• CPU Architecture: Any

[10 Oct 2004 16:27] Andrey Hristov

Description:
 Since 4.1 MySQL should emit a warning when IF [NOT] EXISTS is used. This is the case with DROP DATABASE IF EXISTS (see how to repeat) but not when CREATE DATABASE IF NOT EXISTS. The same is valid for CREATE TABLE opposed to DROP TABLE.

Thanks,
Andrey

How to repeat:
mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 4.1.4-beta-debug-log |
+----------------------+
1 row in set (0.05 sec)

mysql> drop database if exists foo;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1008 | Can't drop database 'foo'; database doesn't exist |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database foo;
Query OK, 1 row affected (0.02 sec)

mysql> create database if not exists foo;
Query OK, 0 rows affected (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1008 | Can't drop database 'foo'; database doesn't exist |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> use test;
Database changed

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

mysql> create table if not exists t42 (a int);
Query OK, 0 rows affected (0.00 sec)

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

[10 Oct 2004 19:14] Hartmut Holzgraefe

verified, i think a warning makes sense if the table already exists as the manual states:

  Note that there is no verification that the existing table has a structure 
  identical to that indicated by the CREATE TABLE statement.

[3 Nov 2004 7:36] Sergei Glukhov

ChangeSet
  1.2074 04/11/03
  Fix for bug #6008: MySQL does not create warnings when creating database and using IF NOT EXISTS

[5 Aug 2005 20:00] Jim Winstead

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/internals/18225

[12 Sep 2005 12:08] 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/internals/29654

[12 Sep 2005 12:25] Alexander Barkov

Ok to push

[12 Sep 2005 13:26] Sergei Glukhov

Fixed in 4.1.15

[14 Sep 2005 0:39] Paul DuBois

Noted in 4.1.15 changelog.