Recover table using .ibd/.frm files after MySQL crashed during OPTIMIZE TABLE?

MySQL & MariaDB Other MySQL® Questions
1

Hi

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)

2

Hi Wodin

Sorry that you are having these issues. The difficulty from our point of view is that version 5.5 is a very old release that’s no longer maintained, so we would not be able to investigate this issue via the Forum as the software’s no longer maintained.

Because you are effectively using Percona Server for MySQL standalone, you might well be running into an upstream bug or Percona Server bug too (not necessarily a XtraDB Cluster bug) so there are a lot of places the problem could be lurking.

Are you able to upgrade to 5.7 and see if the problem still remains? This is the earliest release that we now support.

If it’s a business production system and you could use our professional support in making that upgrade, then I can put you in touch with someone who could look at the options with you. You are welcome to email me about that if it’s an option for you lorraine.pocklington@percona.com