MySQL Bugs: #3147: DROP DATABASE requires DROP TABLE on all tables first

Bug #3147	DROP DATABASE requires DROP TABLE on all tables first
Submitted:	11 Mar 2004 16:22	Modified:	19 Sep 2004 22:59
Reporter:	[ name withheld ]	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.16	OS:	Linux (Linux Mandrake 10)
Assigned to:		CPU Architecture:	Any

Description:
I've found in the 4.0.16+ (up to 4.0.18) version of MySQL that DROP DATABASE  
won't do anything - I need to first drop all tables and then DROP DATABASE.  
This goes for mysqladmin too. These tables have no constraints.   
  
The documentation still clearly states "DROP DATABASE drops all tables in the 
database and deletes the database." 
   
I've looked through the change log, but I can't see when this behaviour was  
changed. Previously, DROP DATABASE would clear the database out.  Sorry, I 
don't actually know which version I did have installed - it came with Mandrake 
9.2 (it was at least 4.0+) 
 
 

How to repeat:
[richard@richardpc maint-0-6]$ mysql -urounduptest -prounduptest 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 67 to server version: 4.0.18'-Max' 
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
 
mysql> create database rounduptest; 
Query OK, 1 row affected (0.00 sec) 
 
mysql> use rounduptest; 
Database changed 
mysql> create table testing (testing int) type=BDB; 
Query OK, 0 rows affected (0.12 sec) 
 
mysql> commit; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> Bye 
[richard@richardpc maint-0-6]$ mysql -urounduptest -prounduptest 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 68 to server version: 4.0.18'-Max' 
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
 
mysql> drop database rounduptest; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> Bye 
[richard@richardpc maint-0-6]$ mysql -urounduptest -prounduptest 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 69 to server version: 4.0.18'-Max' 
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
 
mysql> create database rounduptest; 
ERROR 1007: Can't create database 'rounduptest'. Database exists 
mysql> Bye 
 

Suggested fix:
Please either fix "DROP DATABASE" or fix the documentation.

I tested your sample on Suse 9.0 and I wasn't able to repeat:

miguel@hegel:~> /usr/local/mysql/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.18-log

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

mysql> create database rounduptest;
Query OK, 1 row affected (0.00 sec)

mysql> use rounduptest;
Database changed
mysql> create table testing (testing int) type=BDB;
Query OK, 0 rows affected (0.65 sec)

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

mysql> exit;
Bye
miguel@hegel:~> /usr/local/mysql/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.0.18-log

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

mysql> drop database rounduptest;
Query OK, 0 rows affected (0.07 sec)

mysql> exit;
Bye
miguel@hegel:~> /usr/local/mysql/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.18-log

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

mysql> create database rounduptest;
Query OK, 1 row affected (0.00 sec)

mysql>

What can I say? It's repeatable using both 4.0.16 and 4.1.18 here. I even 
tried it just now with the -uroot argument that you used. Tried it as root 
too. No change. 
 
Happy to look into this more, if given hints about what to look into.

I can reproduce this bug on Slackware 9.1 (Linux 2.4 x86) and Slackware
9.x-current (Linux 2.6 x86) with MySQL version 4.0.17 and 4.0.18 either
compiled from source or using the binaries from the download page..

Here's a different way to reproduce this bug:

$ mysql -u root --password=blah
mysql> create database test;
Query OK, 1 row affected (0.02 sec)
mysql> use test;
Database changed
mysql> create table test1 (id int);
Query OK, 0 rows affected (0.00 sec)
mysql> create table test2 (id int);
Query OK, 0 rows affected (0.00 sec)
mysql> create table test3 (id int);
Query OK, 0 rows affected (0.00 sec)
mysql> Bye

$ mysql -u root --password=blah     
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> Bye

$ mysql -u root --password=blah
mysql> use test;
Database changed
mysql> show tables;
+----------------+                 <----
| Tables_in_test |                 <----
+----------------+                 <----
| test3          |                 <----
+----------------+                 <----
1 row in set (0.00 sec)
mysql> drop table test3;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database test;
ERROR 1008: Can't drop database 'test'. Database doesn't exist
mysql> Bye

After dropping the database, the table files for test3 are still present in /var/lib/mysql/test/ (test3.MYD, test3.MYI, test3.frm).

strace:ing mysqld shows that it never tries to delete the files for table test3. It does not try to remove the database directory 'test' either, and yet the SQL query returns OK.. Funneh.

Now let's try the same thing with InnoDB tables...

mysql> use test;
Database changed
mysql> create table foo1 (id int) type=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> create table foo2 (id int) type=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> create table foo3 (id int) type=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+                 <----
| Tables_in_test |                 <----
+----------------+                 <----
| foo2           |                 <----
| foo3           |                 <----
+----------------+                 <----
2 rows in set (0.00 sec)

mysql> drop table foo2;
ERROR 1051: Unknown table 'foo2'   <----
mysql> drop table foo3;
ERROR 1051: Unknown table 'foo3'   <----
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| foo2           |
| foo3           |
+----------------+
2 rows in set (0.00 sec)
mysql> create table foo2 (id int) type=innodb;
ERROR 1050: Table 'foo2' already exists
mysql> insert into foo2 (id) values (1);
ERROR 1016: Can't open file: 'foo2.InnoDB'. (errno: 1)
mysql> Bye

MySQL screws up, and I have to manually remove the table files.
(rm -rf /var/lib/mysql/test/foo*)

Sorry, still can't repeat :

Your MySQL connection id is 1 to server version: 4.0.19-debug

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

SLAVE> CREATE DATABASE accounting;
Query OK, 1 row affected (0.14 sec)

SLAVE>
SLAVE> USE accounting;
Database changed
SLAVE> CREATE TABLE `pricing` (`id` int(10) unsigned NOT NULL
    -> auto_increment, PRIMARY KEY  (`id`)) TYPE=MyISAM COMMENT='pricing policies' AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.18 sec)

SLAVE> CREATE TABLE `test`  (`id` int(10) unsigned NOT NULL
    -> auto_increment, PRIMARY KEY  (`id`)) TYPE=MyISAM COMMENT='pricing policies' AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.01 sec)

SLAVE> show tables;
+----------------------+
| Tables_in_accounting |
+----------------------+
| pricing              |
| test                 |
+----------------------+
2 rows in set (0.00 sec)

SLAVE> DROP DATABASE accounting;
Query OK, 0 rows affected (0.01 sec)

SLAVE> show tables;
ERROR 12 (00000): Can't read dir of './accounting/' (Errcode: 2)

Which is normal, the 'accounting' database dir was indeed deleted.
(Linux Mandrake 9.2 on x86).

Sorry, the output I showed was for the testcase of BUG#3594 which is a duplicate of this.
I have also tested the test1/test2/test3 testcase and could not repeat the bug:

Your MySQL connection id is 5 to server version: 4.0.19-debug-log

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

MASTER> create database test;
Query OK, 1 row affected (0.00 sec)

MASTER> use test;
Database changed
MASTER> create table test1 (id int);
Query OK, 0 rows affected (0.01 sec)

MASTER> create table test2 (id int);
Query OK, 0 rows affected (0.00 sec)

MASTER> create table test3 (id int);
Query OK, 0 rows affected (0.00 sec)

MASTER> Bye
[guilhem@gbichot2 1]$ mysql1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.19-debug-log

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

MASTER> drop database test;
Query OK, 0 rows affected (0.00 sec)

MASTER> Bye
[guilhem@gbichot2 1]$ mysql1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.19-debug-log

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

MASTER> use test;
ERROR 1049 (00000): Unknown database 'test'

When you successfully repeat the bug (using MyISAM tables), can you please take a look at the error log of your mysqld, and see if there are no complaints from InnoDB. That may be the problem: maybe there are "orphan" tables into InnoDB (tables which exist in InnoDB but not in MyISAM, because of weird things which happened before, maybe long ago) and so things screw up. In other words, please tell me if you can repeat the bug when you run your mysqld with --skip-innodb.

This looks like the problem discussed in
http://bugs.mysql.com/bug.php?id=3594
http://lists.mysql.com/internals/13792 (with patch fix)

Phil
=--=

But BUG#3594 was apparently specific to 2.6 kernels; and the person who reported this bug mentioned it happened on 2.4 (Slackware) too. In any case, an upgrade to 4.0.20 should help him know if this is this (already fixed in 4.0.19) bug.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".