MySQL Bugs: #44529: Cannot drop database with stale temporary tables

Bug #44529	Cannot drop database with stale temporary tables
Submitted:	28 Apr 2009 19:07	Modified:	5 Dec 2009 12:19
Reporter:	Andrew Hutchings	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Any
Assigned to:	Martin Skold	CPU Architecture:	Any

Description:
If you have stale ndb temporary tables you cannot drop the database correctly across all mysqld nodes.

How to repeat:
In the following mysql1 is mysqld on server 1, shell2 is server 2 (in its mysqld datadir). 

1. Setup a cluster with 2 mysqld nodes
2. mysql1> CREATE DATABASE test;
3. mysql1> CREATE TABLE testing (a int) ENGINE=ndb;
4. shell2> touch "test/#sql-1c17_25d0.ndb"
5. mysql1> DROP DATABASE test;

Query OK, 0 rows affected (0.22 sec)

In mysql2 log files:
090428 20:59:26 [ERROR] NDB: drop database test: error Error dropping database (can't rmdir './test', errno: 39) 1010(ndb: 0) 1 1

Suggested fix:
Not sure there is one, we don't want to drop a database that has MyISAM tables on other mysqld nodes in I guess.  Possibly add to documentation?

This has to do with Alter table

In the special case that this is a ndb-table (as indicated by the .ndb-file)
We can check if the table is in the database, and if not anyway
drop the database

The following patch seems to fix the problem. It declares that
.ndb files may be removed if found stray when dropping a database.
This should be safe since, since if for any reason a table still exists
in ndb it will be recreated through autodiscovery. However, it should have
been dropped by ndb since all known tables are supposed to be dropped
before the database directory is actually cleaned-up.

=== modified file 'sql/sql_db.cc'
--- sql/sql_db.cc       2009-09-30 18:51:17 +0000
+++ sql/sql_db.cc       2009-10-09 13:23:42 +0000
@@ -32,7 +32,7 @@
 
 #define MAX_DROP_TABLE_Q_LEN      1024
 
-const char *del_exts[]= {".frm", ".BAK", ".TMD",".opt", NullS};
+const char *del_exts[]= {".frm", ".BAK", ".TMD", ".opt", ".ndb", NullS};
 static TYPELIB deletable_extentions=
 {array_elements(del_exts)-1,"del_exts", del_exts, NULL};

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/commits/89964

3164 Martin Skold	2009-11-10
      Bug#44529 Cannot drop database with stale temporary tables: define .ndb files to be cleaned away at drop table
      modified:
        mysql-test/suite/ndb/r/ndb_multi.result
        mysql-test/suite/ndb/t/ndb_multi.test
        sql/sql_db.cc

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/commits/90091

3223 Martin Skold	2009-11-11 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_multi.result
        mysql-test/suite/ndb/t/ndb_multi.test
        sql/sql_db.cc

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/commits/90100

3150 Martin Skold	2009-11-11 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_multi.result
        mysql-test/suite/ndb/t/ndb_multi.test
        sql/sql_db.cc
        storage/ndb/src/kernel/vm/SimulatedBlock.cpp

Pushed into 5.1.39-ndb-7.1.0 (revid:martin.skold@mysql.com-20091111144910-ahjdwoa1v2oocp6d) (version source revid:martin.skold@mysql.com-20091111144910-ahjdwoa1v2oocp6d) (merge vers: 5.1.39-ndb-7.1.0) (pib:13)

Please inform if this is or will be pushed into 6.2/6.3/7.0 trees?

Thanks!

Martin informed me on IRC that this bug was pushed to 6.3+.

Should be documented in 6.3.29, 7.0.10.

Set status back to Documenting.

Documented bugfix in the NDB-6.3.29 and 7.0.10 changelogs as follows:

        DROP DATABASE failed when there were stale temporary NDB tables
        in the database. This situation could occur if mysqld crashed
        during execution of a DROP TABLE statement after the table
        definitions had been removed from NDBCLUSTER but before the
        corresponding .ndb file had been removed from the crashed SQL
        node's data directory. Now when mysqld executes DROP DATABASE,
        it checks for these files and removes them if there are no
        corresponding table definitions in NDBCLUSTER.

Closed.