I’ve posted this to [url]https://dba.stackexchange.com/questions/243012/how-do-i-use-the-ibd-frm-files-to-recover-an-innodb-table-that-went-missing-wh[/url] but have not received any interest yet. Any help would be greatly appreciated.
Basically, I’m running percona-xtradb-cluster-server-5.5 (but currently standalone). I ran OPTIMIZE TABLE on an InnoDB table, and mysqld crashed.
I now have the following files:
-rw-rw---- 1 mysql mysql 8804 2018-03-21 17:44:04 #sql2-70b6-680e082.frm -rw-rw---- 1 mysql mysql 8804 2019-07-16 16:16:25 #sql-70b6_680e082.frm -rw-rw---- 1 mysql mysql 16777216 2019-07-16 16:29:16 #sql2-70b6-680e082.ibd -rw-rw---- 1 mysql mysql 16777216 2019-07-16 16:29:22 tablename.ibd
The two .frm files are identical. The two .ibd files both seem to contain (some?) data and the tablename.ibd file has the later timestamp.
The error log contains the following error:
InnoDB: Assertion failure in thread 140135523792640 in file dict0dict.c line 2697 InnoDB: Failing assertion: UT_LIST_GET_LEN(table->referenced_list) == rbt_size(table->referenced_rbt) ... InnoDB: Error: table 'dbname/#sql-70b6_680e082' InnoDB: in InnoDB data dictionary has tablespace id 63476110, InnoDB: but the tablespace with that id has name ./dbname/tablename.ibd.
Is there a way to recover from this using the above .frm/.ibd files? I have seen info about using ALTER TABLE xxx DISCARD TABLESPACE and ALTER TABLE xxx IMPORT TABLESPACE, but I’m not sure exactly how to proceed and whether this will work.
I have attached the full error from error.log.
mysql-optimize-table-crash.txt (13.4 KB)