Bug #97522 Cannot delete non-existing table space
Submitted: 6 Nov 2019 20:56 Modified: 8 Nov 2019 13:44
Reporter: Bo Henriksen Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.11 OS:Microsoft Windows (Server 2016 Standard)
Assigned to: CPU Architecture:x86
Tags: drop schema, innodb, table space

[6 Nov 2019 20:56] Bo Henriksen
Description:
When dropping a schema, it sometimes happens that the INFORMATION_SCHEMA.files are not updated for a table.

This makes it impossible to recreate a schema and a table with the same name.

Details are as follows.

We are running a server with 500+ databases (with low access, primarily read only). Dropping of databases happens in a rate of between 1 and 10 databases pr. week.

5 times the last 18 month, an error has occurred while dropping a database.
Last time when dropping a database named "avid.sfk.25029", the following error is logged in the error-log:

[Warning] [MY-012111] [Server] Problem while dropping database. Can't remove database directory (Error dropping database (can't rmdir '.\avid@002esfk@002e25029', errno: 41 - Directory not empty)). Please remove it manually.

If the file is removed manually and the server is restarted, the following warning occurs in the log:

[Warning] [MY-010068] [InnoDB] InnoDB: Tablespace 222430, name 'avid.sfk.25029/vendtable', file '.\avid@002esfk@002e25029\vendtable.ibd' is missing!

The table cannot be removed, as the schema doesn't exist.
Trying to recreate the schema and table gives an error:

create schema `avid.sfk.25029`;
use `avid.sfk.25029`;
create table vendtable (`id` tinyint not null); # Returns: Error code: 1062. Duplicate entry 'avid.sfk.25029/vendtable' for key 'name'.

The error stems from the INFORMATION_SCHEMA.files table:
SELECT * from INFORMATION_SCHEMA.files where tablespace_name like "%vendtable%" 
Except the following fields, all other fields are NULL, including the FILE_ID.

FILE_NAME: ./avid@002esfk@002e25029/vendtable.ibd
TABLESPACE_NAME: avid.sfk.25029/vendtable
ENGINE: INNODB

Note that no other tables in INFORMATION_SCHEMA holds informations about "avid.sfk.25029" or "vendtable", so most of the information is actually deleted correct.

I cannot remove the tablespace:
* DELETE FROM INFORMATION_SCHEMA WHERE FILE_NAME LIKE... returns an access denied error.
* DROP TABLESPACE `avid.sfk.25029/vendtable` returns "Error Code: 3119. InnoDB: A general tablespace name cannot contain '/'.

I simply don't know how to get on with this error. It has now occurred 5 times the last 18 months.

How to repeat:
Create a schema and a table
Perform a DROP SCHEMA command, in which the .ibd file is not deleted
Create a schema and a table with the same name.
The last command should give the following error:

Error code: 1062. Duplicate entry '{SCHEMA_NAME}/{TABLE_NAME}' for key 'name'.

Suggested fix:
None.
[8 Nov 2019 13:44] Sinisa Milivojevic
Hello Mr. Henriksen,

Thank you for your bug report.

I have tested your problem with more then 500 databases. Some of the databases had tablespaces for a number of tables. I created and dropped all 500 schemas randomly.

With 8.0.18, I encountered no problem.

8.0.11 is an old release and since then many, many bugs have been fixed.