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 Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.4+ OS:Linux (Linux 2.6.4)
Assigned to: Sergey Glukhov

[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] Sergey 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] Sergey Glukhov
Fixed in 4.1.15
[14 Sep 2005 0:39] Paul Dubois
Noted in 4.1.15 changelog.