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.
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.