| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.0.18 | OS: | Linux (Debian Linux) |
| Assigned to: | CPU Architecture: | Any | |
[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
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...