MySQL Bugs: #4866: CREATE DATABASE IF NOT EXISTS generates error 17 if datbase already exists

Bug #4866	CREATE DATABASE IF NOT EXISTS generates error 17 if datbase already exists
Submitted:	3 Aug 2004 10:27	Modified:	11 Aug 2004 18:46
Reporter:	Håkan Elmqvist	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.3b-beta	OS:	widows xp
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
C:\>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 217 to server version: 4.1.3b-beta

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

mysql> CREATE DATABASE IF NOT EXISTS dallas;
ERROR 1006 (HY000): Can't create database 'dallas' (errno: 17)
mysql>

This does not happen with 3.23.53a:
C:\>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 107269 to server version: 3.23.53a
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE DATABASE IF NOT EXISTS dallas;
Query OK, 1 row affected (0.03 sec)
mysql> CREATE DATABASE IF NOT EXISTS dallas;
Query OK, 0 rows affected (0.03 sec)

The same behaviour is seen with other clients.
Matthias Fichtner's MySQL Client API for Borland Delphi generates an exception.
H

How to repeat:
mysql> CREATE DATABASE IF NOT EXISTS dallas;
mysql> CREATE DATABASE IF NOT EXISTS dallas;

Suggested fix:
no idea

I can't repeat:

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.3b-beta-nt-max

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

mysql> create database if not exists dallas;
Query OK, 1 row affected (0.14 sec)

mysql>

However I saw that error when I tried to drop a database and verified
that into the database directory there is a directory called comm,
could you please verifiy into your data directory if exists a directory
with that or similar name?

I simply do this:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corporation

C:\Documents and Settings\hå>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 339 to server version: 4.1.3b-beta

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

mysql> show databases;
+-------------+
| Database    |
+-------------+
| audiogram   |
| dallas      |
| energihemma |
| f?retag     |
| mysql       |
+-------------+
5 rows in set (0.00 sec)

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

mysql> create database adam;
ERROR 1006 (HY000): Can't create database 'adam' (errno: 17)
mysql> show databases;
+-------------+
| Database    |
+-------------+
| adam        |
| audiogram   |
| dallas      |
| energihemma |
| f?retag     |
| mysql       |
+-------------+
6 rows in set (0.00 sec)

mysql>

H

I cannot repeat this using 4.1.3 on windows 2000:

root@192.168.1.28:(none)~> create database dallas;
Query OK, 1 row affected (0.02 sec)

root@192.168.1.28:(none)~> create database if not exists dallas;
Query OK, 0 rows affected (0.00 sec)

root@192.168.1.28:(none)~> select version();
+--------------------+
| version()          |
+--------------------+
| 4.0.20a-nt-max-log |
+--------------------+
1 row in set (0.01 sec)

root@192.168.1.28:(none)~> create database dallas;
ERROR 1007 (00000): Can't create database 'dallas'. Database exists

Dear Matthew,
I installed MySQL with mysql-4.1.3b-beta-win.zip on my laptop and exactly the same thing happens:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corporation

C:\Documents and Settings\he>cd \mysql\bin

C:\mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.3b-beta-max

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

mysql> create database if not exists a;
Query OK, 1 row affected (0.30 sec)

mysql> create database if not exists a;
ERROR 1006 (HY000): Can't create database 'a' (errno: 17)

mysql> show databases;
+----------+
| Database |
+----------+
| a        |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

This happens when using the 95/98 binary, mysqld-opt.exe.  I get errno: 17 back (win 32 error: 
File Exists) when doing normal create database a second time as well:

| version_compile_os              | Win95/Win98                         |

root@192.168.1.28:(none)~> create database if not exists dallas;
Query OK, 1 row affected (0.00 sec)

root@192.168.1.28:(none)~> create database if not exists dallas;
ERROR 1006 (HY000): Can't create database 'dallas' (errno: 17)
root@192.168.1.28:(none)~> create database dallas;
ERROR 1006 (HY000): Can't create database 'dallas' (errno: 17)

Dear User,

In any event, you should be using mysqld-nt or mysqld-max-nt on NT/XP/2000/2003.

Best Regards

Sorry, 
but using mysqld-nt does not help:

C:\mysql\bin>mysqld-opt.exe --remove
Service successfully removed.

C:\mysql\bin>mysqld-nt.exe --install
Service successfully installed.

C:\mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.3b-beta-nt

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

mysql> create database if not exists b;
ERROR 1006 (HY000): Can't create database 'b' (errno: 17)
mysql> create database if not exists c;
Query OK, 1 row affected (0.02 sec)

mysql> create database if not exists c;
ERROR 1006 (HY000): Can't create database 'c' (errno: 17)

What should I do? Program around the problem?
Rgds
H

Dear user, 

Are you sure that you are not connecting to the 9x binary?
What binary do you see running in your task manager?  I am *not* able to repeat this problem
when using mysqld-nt or mysqld-max-nt.

Best Regards

I don't know what a 9x binary is. 
I used mysql-4.1.3b-beta-win.zip to install the server and installed it as a system service with:

C:\mysql\bin>mysqld-nt.exe --install
Service successfully installed.

It was launced with the MS-management console.
My taskmgr says mysqld-nt.exe.
H
Ps I did not see your message of  Aug 3 until now. No I have no directory "comm" or similar:

C:\mysql\data>dir
 Volymen i enhet C har ingen etikett.
 Volymens serienummer är 743F-FE9B

 Innehåll i katalogen C:\mysql\data

2004-08-04  10:31    <KAT>          .
2004-08-04  10:31    <KAT>          ..
2004-08-04  10:31    <KAT>          mysql
2004-08-04  10:31    <KAT>          test
2004-08-04  17:26               932 itx2.err
2004-08-04  17:24        10 485 760 ibdata1
2004-08-04  17:26         5 242 880 ib_logfile0
2004-08-04  10:33         5 242 880 ib_logfile1
2004-08-04  17:24               435 mysql.err
2004-08-04  10:42    <KAT>          a
2004-08-04  11:43    <KAT>          b
2004-08-04  17:26             2 448 innodb.status.1232
2004-08-04  17:27    <KAT>          c
               6 fil(er)          20 975 335 byte
               7 katalog(er)  86 273 294 336 byte ledigt

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

I was able to reproduce this using 4.1.3b on Win2k3, however was unable to reproduce it using a fresh build of bk source.  I tried the debug and -nt targets on Win2k3.  I also tried with current bk source on Linux.

hmm.. I found for me. it was simply a matter of removing the 'create database' query string near the top of my mysql file. I also had to drop all my existing tables first. 

For a good screen cap of the process see..

www.projectmilestone.com/How to restore your backup mysql db using frontend tool - for web.pdf 

Or if the above doesn't work...

http://projectmilestone.com/How%20to%20restore%20your%20backup%20mysql%20db%20using%20fron...