MySQL: tablespace exists, but it is impossible to create table
Problem
CREATE TABLE produces the error:
ERROR 1813 (HY000) tablespace 'SCHEMA_NAME.TABLE_NAME' exists.
Statement
.ibd file exists, but .frm is missing.
Solution
note
Referring tables can be harmed. Provide backup before you proceed to the steps below.
Assume the issue is for the table named ORPHANTABLE and located in /var/lib/mysql/wfdb.
MySQL: create a separate schema
create schema fix;
use fix;
create table ORPHANTABLE ( id int, s varchar(30));
Linux: copy frm-file to original location
# copy the new frm-file:
cp /var/lib/mysql/fix/ORPHANTABLE.frm /var/lib/mysql/wfdb/
chown mysql:mysql /var/lib/mysql/wfdb/ORPHANTABLE.frm
# remove the old ibd-file:
rm /var/lib/mysql/wfdb/ORPHANTABLE.ibd
MySQL
use wfdb;
drop table ORPHANTABLE;
drop schema fix;
Linux: ensure no files are left and restart MySQL service
ls -l /var/lib/mysql/wfdb/ORPHANTABLE*
service mysql restart