MySQL Bugs: #54788: DROP DATABASE can remove hidden blob tables without removing parent tables

Bug #54788	DROP DATABASE can remove hidden blob tables without removing parent tables
Submitted:	24 Jun 2010 21:41	Modified:	28 Jul 2010 9:41
Reporter:	Andrew Hutchings	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	6.3,7.0	OS:	Any
Assigned to:	Andrew Hutchings	CPU Architecture:	Any
Tags:	Contribution

Description:
When issuing DROP DATABASE mysqld does the following:

1. drop all tables in the database that .frm files can be found for
2. call ndbcluster_drop_database_impl
2a. get a list of UserTable
2b. drop all tables in that list if they are in the database we are dropping

At 2a includes blob tables in the list, so these can get dropped without the parent tables and the error code generated is silently ignored.

How to repeat:
1. Create a few cluster tables which have blobs in test database
2. in one console load "mysql -uroot test -A"
3. in another console delete the .frm and .ndb for a cluster table in the test database
4. in the mysql console "drop database test;"

If you are using a debug build you will hit assert and the table is safe:

mysqld: DictCache.cpp:184: NdbTableImpl* GlobalDictCache::get(const char*, int*): Assertion `! is_ndb_blob_table(name)' failed.

In non-debug the blob columns are dropped but the parent table retained.  You can then only see the table in ndb_show_tables and can't even drop with ndb_drop_table.

As a side effect any backups made will fail when reading the .ctl file.

Workaround would be to drop every table individually before dropping the database.

Actually, not quite.  Because the ndbcluster_drop_database_impl will still be called and will delete the hidden blobs for any table mysqld doesn't know.

The workaround is either:

A.
1. SHOW TABLES;
2. DROP DATABASE;

B.
1. Delete all tables individually
2. Remove the directory for the database from the FS.

C.
Write a script to combine ndb_show_tables and ndb_drop_table (I am guessing using grep and awk).

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

3630 Andrew Hutchings	2010-06-26
      Bug #54788  	DROP DATABASE can remove hidden blob tables without removing parent tables
      
      1. Fix ndbcluster_drop_database_impl so that only real tables are listed
      2. Fix DictCache so that instead of assert errors are generated when blob provided
      3. Test case for #1

As stated by Andrew, Drop database will
a) Look for all table files in database directory
b) Drop those tables
c) Get a list of all tables stored by Ndb
d) Drop the tables in the database being dropped
e) Propagate the drop DB to other MySQLDs

Normally step a) drops tables correctly.

Tables can be hidden from a particular MySQLD due to :
1) Being occluded by a local table (e.g. a MyISAM table created locally before some other Api created the Ndb table)
2) .frm files being deleted.

In this case a) will not drop the table.

Then the table will be found in step c), which uses NdbApi to get a list of schema objects.

The list of schema objects includes Blob part tables. The Drop table code should ignore these, but does not. This is the first bug.

The order of the schema object list is not particularly deterministic, so sometimes a table's blob part tables will be listed before the table itself.

In this case, the Blob part tables will be dropped first. Ideally it should not be possible to drop Blob part tables, but it is, this is the second bug.

When the table dropping loop reaches the main table, it will attempt to load the table definition from the cluster. As the table now has no part tables, this load fails, resulting in a 'Table does not exist' error.

The drop database code ignores the table does not exist error, which is reasonable as another client could concurrently be dropping a table.

An added complication comes from the different concepts of NdbDictionary::getTable() and getTableGlobal(). MySQLD uses the getTableGlobal() variant. Most NdbApi programs use the getTable() variant which allows a Blob part table to be retrieved by an Api. The getTableGlobal() variant has assertions against this, but in a release build (with assertions off), it can successfully retrieve a Blob part table which it can then delete.

Proposed patch against 6.2

Attachment: bug54788-6.2.patch (text/x-patch), 7.88 KiB.

I've proposed a second patch.

This has 3 parts, similar to the original patch.

1) Testcase (enhanced ndb_database) which shows bad behaviour prior to fixes
2) NdbApi level fix which transforms bad MySQLD behaviour (attempt to delete Blob part tables) into an error
3) MySQLD level fix which avoids attempting to delete Blob part tables.

The main change to the original patch is that rather than NdbApi is changed to improve the encapsulation of Blob part tables.

From outside NdbApi, Blob part tables can no longer be :
  - Created
  - Dropped
  - Altered to/from

Blob part tables can still be retrieved by NdbApi for normal DML operations, queries etc, using getTable().  It is no longer possible to retrieve them using getTableGlobal().

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

3116 Frazer Clement	2010-07-26
      Bug#54788 DROP DATABASE can remove hidden blob tables without removing parent tables
      
      1) Drop database code should not attempt to directly drop Blob part tables
         (NDB$BLOB_T_C)
      2) NdbApi should not allow Blob part tables to be dropped by applications
         This is true for normal NdbApi usage, but not for the 'Global dict cache' 
         mode used by MySQLD.
      3) NdbApi should allow a main table with dropped Blob part tables to be 
         'opened', so that it can be dropped.  Otherwise we are left with an
         indestructable table.
      
      A new testcase is added which reproduces the problem fixed by (1 + 2) above.
      There is no automated testcase for fix 3, but it has been tested manually.

Fix pushed to :
 6.2.19
 6.3.36
 7.0.17
 7.1.6

Documented bugfix in the NDB-6.2.19, 6.3.36, 7.0.17, and 7.1.6 changelogs, as follows:

        It was possible for a DROP DATABASE statement to remove NDB
        hidden blob tables without removing the parent tables, with the
        result that the tables, although hidden to MySQL clients, were
        still visible in the output of ndb_show_tables but could not be
        dropped using ndb_drop_table.

Closed.