Bug #3594 Mysql fails to drop database if one of the table is named 'pricing'
Submitted: 28 Apr 2004 21:42 Modified: 10 May 2004 12:45
Reporter: Andrey Lebedev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.18 OS:Linux (Debian Linux)
Assigned to: CPU Architecture:Any

[28 Apr 2004 21:42] Andrey Lebedev
Description:
Mysql fails to drop database if one of the table is named 'pricing'. That what i can tell from what i have investigated. Very weird...

In "How to repeat" section is maximum simplified test case.

How to repeat:
I've created user accounting with password accounting and granted all privileges on table accounting to that user. Then I created file 'accounting.sql' with the following contents:

  CREATE DATABASE accounting;

  USE accounting;
  CREATE TABLE `pricing` (
    `id` int(10) unsigned NOT NULL auto_increment,
    PRIMARY KEY  (`id`)
  ) TYPE=MyISAM COMMENT='pricing policies' AUTO_INCREMENT=1 ;

  DROP DATABASE accounting;

Then I've ran the following commands from console:

  Accounting$ mysql -u accounting -paccounting < accounting.sql
  Accounting$ mysql -u accounting -paccounting < accounting.sql 
  ERROR 1007 at line 1: Can't create database 'accounting'. Database exists

You can see, that script run correctly at the first time, but database was not dropped, so ran error was raised the second time script. There is database directory left in data area:

  $/var/lib/mysql# ls -al accounting
  total 24
  drwx------    2 mysql    mysql        4096 Apr 28 22:22 ./
  drwxr-xr-x   10 mysql    mysql        4096 Apr 28 22:22 ../
  -rw-rw----    1 mysql    mysql           0 Apr 28 22:22 pricing.MYD
  -rw-rw----    1 mysql    mysql        1024 Apr 28 22:22 pricing.MYI
  -rw-rw----    1 mysql    mysql        8550 Apr 28 22:22 pricing.frm

I have to drop table 'pricing' manually or delete that directory to finally get rid of that database.

The thing get worse if table engine used is InnoDB. In that case you can't even drop table after failed drop database command. There is pricing.frm file left in data dir and error in error log produced:

040428 22:07:27  InnoDB: Error: table accounting_test/pricing already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
InnoDB: You can look further help from section 15.1 of
InnoDB: http://www.innodb.com/ibman.html

The funniest part is if I rename table name from 'pricing' to something else, like 'test', database dropped fine...

Suggested fix:
unfortunately, nothing i can suggest here.. can't believe 'pricing' is a reserved word...
[29 Apr 2004 12:40] Alexander Keremidarski
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

mysql>   DROP DATABASE accounting;
Query OK, 1 row affected (0.09 sec)
[29 Apr 2004 20:53] Andrey Lebedev
OK, another try: can you repeat this transcript:

~$ mysql -u accounting -paccounting
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 accounting;
Query OK, 1 row affected (0.00 sec)

mysql> USE accounting;
Database changed
mysql> 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.01 sec)

mysql>  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.00 sec)

mysql> SHOW TABLES;
+----------------------+
| Tables_in_accounting |
+----------------------+
| pricing              |
| test                 |
+----------------------+
2 rows in set (0.00 sec)

mysql> DROP DATABASE accounting;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+----------------------+
| Tables_in_accounting |
+----------------------+
| pricing              |
+----------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE pricing;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> exit
Bye
[7 May 2004 23:13] mysql spam
Duplicate of this bug http://bugs.mysql.com/bug.php?id=3147 
...which is also marked as "Can't repeat", quite interesting indeed. 
 
PostgreSQL anyone? Oh yeah I forgot, I'm forced to use MySQL. Damn it. 
 
Here's a workaround: 
Drop all tables before doing DROP DATABASE. :P 
 
Oh and get used to doing that, cause this bug ain't getting fixed.
[8 May 2004 13:05] Guilhem Bichot
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).
[8 May 2004 13:14] Guilhem Bichot
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.
[8 May 2004 16:14] Andrey Lebedev
Unfortunately there is absolutely nothing printed to error log during the test... 

More, I've removed mysql completely from my system, deleted /var/lib/mysql dir and then reinstalled it again and still experiensing this problem (inno-db is not enabled in debian by default).

However I must admit I can reproduce this bug only with my home machine. At my job database was dropped fine everytime (database dropping is needed for unit testing, so it was dropped really lot of times at job). At home vice versa - it is 100% reproducable here.

There are the same versions of mysql on both machines and both running debian. The only difference - I'm using debian unstable version at home and testing branch at job.

Here is system information (home box), that debian's reportbug script produces:

===========================================================================

Package: mysql-server
Version: 4.0.18-8
Severity: normal
Tags: sid

-- System Information:
Debian Release: testing/unstable
  APT prefers unstable
  APT policy: (500, 'unstable'), (500, 'testing')
Architecture: i386 (i686)
Kernel: Linux 2.6.5-1-k7
Locale: LANG=C, LC_CTYPE=en_US.UTF-8

Versions of packages mysql-server depends on:
ii  adduser                     3.52         Add and remove users and groups
ii  debconf                     1.4.25       Debian configuration management sy
ii  libc6                       2.3.2.ds1-12 GNU C Library: Shared libraries an
ii  libdbi-perl                 1.41-1       The Perl5 Database Interface by Ti
ii  libgcc1                     1:3.3.3-7    GCC support library
ii  libmysqlclient12            4.0.18-8     mysql database client library
ii  libssl0.9.7                 0.9.7d-1     SSL shared libraries
ii  libstdc++5                  1:3.3.3-7    The GNU Standard C++ Library v3
ii  mysql-client                4.0.18-8     mysql database client binaries
ii  passwd                      1:4.0.3-28   Change and administer password and
ii  perl                        5.8.4-1      Larry Wall's Practical Extraction
ii  psmisc                      21.4-1       Utilities that use the proc filesy
ii  zlib1g                      1:1.2.1-5    compression library - runtime

-- debconf information:
* mysql-server/start_on_boot: true
* mysql-server/mysql_install_db_notes:
  mysql-server/nis_warning:
* mysql-server/postrm_remove_database: true

===========================================================================
And here is system info on a debian box where i can't reporduce the bug:
===========================================================================
Package: mysql-server
Version: 4.0.18-5
Severity: normal

-- System Information:
Debian Release: testing/unstable
  APT prefers testing
  APT policy: (600, 'testing')
Architecture: i386 (i686)
Kernel: Linux 2.4.24-1-686
Locale: LANG=C, LC_CTYPE=C

Versions of packages mysql-server depends on:
ii  adduser                     3.52         Add and remove users and groups
ii  debconf                     1.4.22       Debian configuration management sy
ii  libc6                       2.3.2.ds1-11 GNU C Library: Shared libraries an
ii  libdbi-perl                 1.41-1       The Perl5 Database Interface by Ti
ii  libgcc1                     1:3.3.3-6    GCC support library
ii  libmysqlclient12            4.0.18-5     mysql database client library
ii  libssl0.9.7                 0.9.7d-1     SSL shared libraries
ii  libstdc++5                  1:3.3.3-6    The GNU Standard C++ Library v3
ii  libwrap0                    7.6.dbs-2    Wietse Venema's TCP wrappers libra
ii  mysql-client                4.0.18-5     mysql database client binaries
ii  passwd                      1:4.0.3-26   Change and administer password and
ii  perl                        5.8.3-3      Larry Wall's Practical Extraction
ii  psmisc                      21.4-1       Utilities that use the proc filesy
ii  zlib1g                      1:1.2.1-5    compression library - runtime

-- debconf information:
  mysql-server/nis_warning:
  mysql-server/skip_networking: false
  mysql-server/want_chroot: false
* mysql-server/start_on_boot: true
* mysql-server/postrm_remove_database: false
  mysql-server/fix_privileges_warning:
* mysql-server/mysql_install_db_notes:
[8 May 2004 17:40] Sergei Golubchik
And what filesystem the datadir resides on ?

(trying to check whether http://lists.mysql.com/internals/13765 and the trhead therein can be of any relevance)
[9 May 2004 22:42] Andrey Lebedev
ext3 in both cases.

Thanks for that link, now I guess I see where is the problem (if mysql still assumes that . and .. entries goes first in directory listing):

root@kedhome:/var/lib/mysql/accounting# ls -Ua
./  pricing.frm  pricing.MYI  test.MYD  ../  pricing.MYD  test.MYI  test.frm

and after DROP DATABASE accounting:

root@kedhome:/var/lib/mysql/accounting# ls -Ua
./  pricing.frm  pricing.MYI  ../  pricing.MYD

That assumption is false for my home system and true for my job system (where i can't reproduce the bug).
[10 May 2004 12:45] Sergei Golubchik
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:

thanks for confirming.
Then, it's fixed in the source tree already.
Fix should be in 4.0.19