MySQL Bugs: #15319: Cannot drop a corrupt BDB table / Bad error message

Bug #15319	Cannot drop a corrupt BDB table / Bad error message
Submitted:	29 Nov 2005 16:39	Modified:	31 Aug 2006 14:38
Reporter:	Ingo Strüwing	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: BDB	Severity:	S3 (Non-critical)
Version:	5.0.17/4.1.16 BK	OS:	Linux (Linux)
Assigned to:	Elliot Murphy	CPU Architecture:	Any

Description:
During the tests for 5.1.3, a BDB crash on 64 bit left over a corrupted BDB t1 table. It could not be dropped. The whole test suite failed over and over, trying to drop t1.

I could not repeat this in 5.1 (on 32bit Linux), but succeeded in 5.0.17.

When you corrupt a BDB table file (by truncating it to zero size), you cannot drop it anymore with SQL commands. You need to remove the file with operating system commands.

Also the failing drop tells "Unknown table", though the table exists.

How to repeat:
BUILD/compile-pentium-debug-max

mysqld_safe &

mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-debug

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

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table t1 (c1 int) engine=bdb;
Query OK, 0 rows affected (0.33 sec)

#
# Corrupt the data file. Replace $DATADIR with your data directory.
# This truncates the file to zero, but does not delete it.
#
mysql> system > $DATADIR/test/t1.db

#
# This should never give an "Unknown table" error message (it has IF EXISTS).
# It is definitely wrong here.
#
mysql> drop table if exists t1;
ERROR 1051 (42S02): Unknown table 't1'

#
# Anyway, after "Unknown table", a create table should succeed.
# But as I said, the above error message is very wrong.
#
mysql> create table t1 (c1 int) engine=bdb;
ERROR 1050 (42S01): Table 't1' already exists

#
# Grr. Let's try to repair the table.
#
mysql> repair table t1;
+---------+--------+----------+---------------------------------+
| Table   | Op     | Msg_type | Msg_text                        |
+---------+--------+----------+---------------------------------+
| test.t1 | repair | error    | Got error 1 from storage engine |
+---------+--------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
# perror 1: Operation not permitted
# So this is not supported.

#
# Anyway, try again to get rid of the table.
#
mysql> drop table if exists t1;
ERROR 1051 (42S02): Unknown table 't1'

#
# ... or to create a new one.
#
mysql> create table t1 (c1 int) engine=bdb;
ERROR 1050 (42S01): Table 't1' already exists

#
# Try with the most brute repair command we have.
#
mysql> repair table t1 use_frm;
+---------+--------+----------+---------------------------------+
| Table   | Op     | Msg_type | Msg_text                        |
+---------+--------+----------+---------------------------------+
| test.t1 | repair | error    | Got error 1 from storage engine |
+---------+--------+----------+---------------------------------+
1 row in set (0.01 sec)
# perror 1: Operation not permitted
# So this is not supported too.

#
# And again ...
#
mysql> drop table if exists t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> create table t1 (c1 int) engine=bdb;
ERROR 1050 (42S01): Table 't1' already exists

#
# So only restoring (or removing) the table file on disk helps.
# We should be able to drop a corrupted table with SQL commands.
# Also the error messages don't tell what's going on.
#

mysql>  use test;
Database changed
mysql> create table t1 (c1 int) engine=bdb;
Query OK, 0 rows affected (0.07 sec)

mysql> system > /home/miguel/dbs/5.0/var/test/t1.db
mysql> drop table if exists t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> create table t1 (c1 int) engine=bdb;
ERROR 1050 (42S01): Table 't1' already exists
mysql> repair table t1;
+---------+--------+----------+---------------------------------+
| Table   | Op     | Msg_type | Msg_text                        |
+---------+--------+----------+---------------------------------+
| test.t1 | repair | error    | Got error 1 from storage engine |
+---------+--------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> drop table if exists t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> create table t1 (c1 int) engine=bdb;
ERROR 1050 (42S01): Table 't1' already exists
mysql> drop table if exists t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> create table t1 (c1 int) engine=bdb;
ERROR 1050 (42S01): Table 't1' already exists
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.17-debug |
+--------------+
1 row in set (0.00 sec)

051129 12:31:50 [Note] libexec/mysqld: ready for connections.
Version: '5.0.17-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
051129 14:46:04 [ERROR] bdb:  /home/miguel/dbs/5.0/var/././test/t1.db: unexpected file type or format
051129 14:48:47 [ERROR] bdb:  /home/miguel/dbs/5.0/var/././test/t1.db: unexpected file type or format
051129 14:48:47 [ERROR] bdb:  /home/miguel/dbs/5.0/var/././test/t1.db: unexpected file type or format
051129 14:49:02 [ERROR] bdb:  /home/miguel/dbs/5.0/var/././test/t1.db: unexpected file type or format
051129 14:49:46 [ERROR] bdb:  /home/miguel/dbs/5.0/var/././test/t1.db: unexpected file type or format

miguel@hegel:~/dbs/mytest> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.16-log

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

mysql> use test;
Database changed
mysql> create table t1 (c1 int) engine=bdb;
Query OK, 0 rows affected (0.05 sec)

mysql> system > /home/miguel/dbs/mytest/var/test/t1.db
mysql> drop table if exists t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

Version: '4.1.16-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
051129 15:36:32 [ERROR] bdb:  /home/miguel/dbs/mytest/var/././test/t1.db: unexpected file type or format

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/32934

Turns out that the fix is not correct. If we go behind the scenes to delete the corrupt .db file, bdb then gets confused when we call DB_ENV->txn_checkpoint() (which we do from FLUSH LOGS) and throws an undocumented error.