Having had my VM running my (dev) mysql crash during an ALTER TABLE, I am now currently experiencing the error ERROR 1813 (HY000): Tablespace for table .... exists. Please DISCARD the tablespace before IMPORT when I try to build a table that claims it does not exist when I do the following...
MariaDB [mydb]> SELECT * FROM xxx_tb;
ERROR 1146 (42S02): Table 'mydb.xxx_tb' doesn't exist
and then
MariaDB [mydb]> CREATE TABLE xxx_tb (
-> XXXId int(10) unsigned NOT NULL AUTO_INCREMENT,
-> YYYYCd char(3) NOT NULL,
-> PRIMARY KEY (XXXId)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ERROR 1813 (HY000): Tablespace for table '`mydb`.`xxx_tb`' exists. Please DISCARD the tablespace before IMPORT.
I can see the files xxx_tb.idb and xxx_tb.frm do already exist (although the .frm is 0 bytes long).
If I try to follow the DISCARD hint then I get...
MariaDB [mydb]> DROP TABLE xxx_tb;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mydb]> ALTER TABLE xxx_tb DISCARD TABLESPACE;
ERROR 1146 (42S02): Table 'mydb.xxx_tb' doesn't exist
Trying to remove the .idb and .frm files after stopping mysql (sudo /etc/init.d/mysql stop) and then restarting mysql seems to make no difference (same problems).
Note, I can obviously see Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT but this has no obvious solution and the ones I've tried do not solve it for me (the chat above is covering the most rated solution on that link.. but it does not work here) - I'm loathed to try to the nuclear option of rebuilding the database (of over 170 tables) just because of 1 table corruption - but this seems to be the only option facing me at this point. It seems that there is something in mysql that is holding onto the existence of my table outside of the .frm and .ibd files (ibdata1? or ib_logfile0/1?).
How can I remove the existence of this old xxx_tb table so that I can build a replacement?
