MySQL Bugs: #75325: table appears in show tables but can't drop it

Bug #75325	table appears in show tables but can't drop it - INNODB
Submitted:	29 Dec 2014 0:47	Modified:	8 May 2018 20:12
Reporter:	David MacMillan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.6	OS:	Windows (7.0 SP1)
Assigned to:		CPU Architecture:	Any

Description:
A partitioned INNODB table with one or more missing portition files (perhaps due to a failed disk or ssd) will be listed with 'SHOW TABLES' but can not be dropped with "DROP TABLE tablename". (It is unknown to me whether this same situation can arise with non-partitioned tables.) It is also impossible to DROP the database containing this tablename when in this state. When in this state, the partitions still show up in information_schema.innodb_sys_tables. CREATE TABLE tablename throws an error 1050 that tablename already exists but trying to DROP that tablename also fails. An ALTER command fails with ERROR 1696 - failed to read from the .par file.

How to repeat:
Create a partitioned INNODB table and delete one or more of the partitions. 

Suggested fix:
The DROP TABLE tablename command should clean up any table references, including partitions belonging to the DROPed tablename that are still in information_schema.innodb_sys_tables or other metadata tables. DROP TABLE tablename should leave the system in a state where CREATE TABLE tablename should work.

Thank you for the bug report. Please provide the output of SHOW CREATE TABLE offended_table_name\G and the complete version of the server 5.6.XX. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have found that creating tables in schema 'a' and then renaming them to schema 'b' leaves INNODB_SYS_TABLES entries belonging to schema 'a' for some objects. This means the schema can't be dropped, which seems to me to be very similar/related behaviour to the bug's title.

Issuing:
root@localhost [myoldschema]> rename myoldschema.tablename to mynewschema.tablename;

Leaves this (anonymised):

root@localhost [information_schema]> select * from INNODB_SYS_TABLES;
+----------+-----------------------------------------------------------------+------+--------+-------+-------------+------------+---------------+
| TABLE_ID | NAME                                                            | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+-----------------------------------------------------------------+------+--------+-------+-------------+------------+---------------+
|       14 | SYS_DATAFILES                                                   |    0 |      5 |     0 | Antelope    | Redundant  |             0 |
|       11 | SYS_FOREIGN                                                     |    0 |      7 |     0 | Antelope    | Redundant  |             0 |
|       12 | SYS_FOREIGN_COLS                                                |    0 |      7 |     0 | Antelope    | Redundant  |             0 |
|       13 | SYS_TABLESPACES                                                 |    0 |      6 |     0 | Antelope    | Redundant  |             0 |
|     1033 | myoldschema/FTS_0000000000000401_0000000000000e39_DOC_ID        |    1 |      5 |   971 | Antelope    | Compact    |             0 |
|     1034 | myoldschema/FTS_0000000000000401_0000000000000e39_INDEX_1       |    1 |      8 |     0 | Antelope    | Compact    |             0 |
|     1035 | myoldschema/FTS_0000000000000401_0000000000000e39_INDEX_2       |    1 |      8 |     0 | Antelope    | Compact    |             0 |
|     1036 | myoldschema/FTS_0000000000000401_0000000000000e39_INDEX_3       |    1 |      8 |     0 | Antelope    | Compact    |             0 |
|     1037 | myoldschema/FTS_0000000000000401_0000000000000e39_INDEX_4       |    1 |      8 |     0 | Antelope    | Compact    |             0 |
|     1038 | myoldschema/FTS_0000000000000401_0000000000000e39_INDEX_5       |    1 |      8 |     0 | Antelope    | Compact    |             0 |
|     1039 | myoldschema/FTS_0000000000000401_0000000000000e39_INDEX_6       |    1 |      8 |     0 | Antelope    | Compact    |             0 |
|     1026 | myoldschema/FTS_0000000000000401_ADDED                          |    1 |      4 |   964 | Antelope    | Compact    |             0 |
|     1029 | myoldschema/FTS_0000000000000401_BEING_DELETED                  |    1 |      4 |   967 | Antelope    | Compact    |             0 |
|     1030 | myoldschema/FTS_0000000000000401_BEING_DELETED_CACHE            |    1 |      4 |   968 | Antelope    | Compact    |             0 |
|     1031 | myoldschema/FTS_0000000000000401_CONFIG                         |    1 |      5 |   969 | Antelope    | Compact    |             0 |
|     1027 | myoldschema/FTS_0000000000000401_DELETED                        |    1 |      4 |   965 | Antelope    | Compact    |             0 |
|     1028 | myoldschema/FTS_0000000000000401_DELETED_CACHE                  |    1 |      4 |   966 | Antelope    | Compact    |             0 |
[...]

Any attempt to drop the original schema now hangs and never returns.

Sorry if this is considered a different issue. Also, my apologies if there should be some markup around the pasted content.

.. and this RENAME is valid according to http://dev.mysql.com/doc/refman/5.6/en/rename-table.html "As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another".

(however I think that "on the same file system" is not a very accurate description).

-- Peter
-- not a MySQL/Oracle person

I wasn't able to repeat following the how to repeat instructions, please if you have a repeatable test case provide it. Thanks.