Description:
For some reason, we may miss ibd and frm files, but the related records in system table is not deleted. Then we even can't create a new table with same table name.
For example:
root@test 02:06:05>create database zwx;
Query OK, 1 row affected (0.00 sec)
root@test 02:07:07>use zwx;
Database changed
root@zwx 02:07:09>create table t1 (a int, b int);
Query OK, 0 rows affected (0.01 sec)
root@zwx 02:07:16>create table t2 (a int, b int);
Query OK, 0 rows affected (0.00 sec)
$sudo ls -lh /u01/my575/data/zwx
total 220K
-rw-rw---- 1 mysql dba 61 Jan 21 14:07 db.opt
-rw-rw---- 1 mysql dba 8.4K Jan 21 14:07 t1.frm
-rw-rw---- 1 mysql dba 96K Jan 21 14:07 t1.ibd
-rw-rw---- 1 mysql dba 8.4K Jan 21 14:07 t2.frm
-rw-rw---- 1 mysql dba 96K Jan 21 14:07 t2.ibd
$sudo rm /u01/my575/data/zwx/t1.frm
$sudo rm /u01/my575/data/zwx/t1.ibd
restart the instance
root@zwx 02:10:23>show tables;
+---------------+
| Tables_in_zwx |
+---------------+
| t2 |
+---------------+
1 row in set (0.00 sec)
root@zwx 02:10:31>create table t1 (a int, b int);
ERROR 1050 (42S01): Table '`zwx`.`t1`' already exists
Because zwx.t1 exists in system table, It will failed to create a new table.
$sudo ls -lh /u01/my575/data/zwx/
total 176K
-rw-rw---- 1 mysql dba 61 Jan 21 14:07 db.opt
-rw-rw---- 1 mysql dba 64K Jan 21 14:10 t1.ibd ———>> Even failed to create t1 , a newly created IDB file is not deleted.
-rw-rw---- 1 mysql dba 8.4K Jan 21 14:07 t2.frm
-rw-rw---- 1 mysql dba 96K Jan 21 14:07 t2.ibd
How to repeat:
Described above
Suggested fix:
1. Provide a way to delete invalid records from system tables.
2. Don't live ibd file there if failed to create table.
