MariaDB crashes on mismatched tablespace ID for InnoDB

Question

I'm having an issue with some corrupted InnoDB tables on a stand-alone MariaDB 10.1 server running on Debian Stretch. I've encountered this issue in the past, but never found a working solution, so I ended up just rebuilding the DB instance. I'm trying to see if there's a way to recover without a rebuild.

Here's the situation:

We have a Drupal site that uses InnoDB for pretty much all of the tables, and there are a handful of tables that are used for caching (named cache_). The contents of the tables are temporary, so the data within them doesn't matter. What happened is that multiple cache_ tables became corrupted somehow and cause MariaDB to crash whenever they are accessed (even something as simple as SHOW TABLES). My guess is the corruption occurred because of TRUNCATE operations that somehow caused a blip in MariaDB when they were rebuilt.

Some key MariaDB settings:

• ignore_builtin_innodb
• plugin_load = ha_innodb.so
• innodb_file_per_table = 1

Sample log messages are below (trimmed for size):

[ERROR] InnoDB: tablespace id is 1479582 in the data dictionary but in file ./mydb/cache.ibd it is 1480371!
[ERROR] InnoDB: Trying to do i/o to a tablespace which exists without .ibd data file. i/o type 10, space id 1479582, page no 0, i/o length 16384 bytes
.. (above repeated 100 times) ..
[ERROR] InnoDB: Unable to read tablespace 1479582 page no 0 into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. You can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/5.6/en/ for more details. Aborting...
.. (skip the crash report lines) ...
InnoDB: Error: table 'mydb/cache'
InnoDB: in InnoDB data dictionary has tablespace id 1479582,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name mydb/cache and id 1480371, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

From a technical standpoint, I understand why the error is happening (the internal tablespace ID in the .ibd file doesn't match what the global InnoDB data dictionary thinks it should be). From my research, a TRUNCATE operation on a table will recreate the table with a new tablespace ID. The MySQL manual states the TRUNCATE will not change the tablespace ID (I believe starting in 5.7, I'm having trouble finding the reference again). MariaDB doesn't seem to have this patch incorporated (you can verify by looking at the INFORMATION_SCHEMA.INNODB_SYS_TABLES table before and after a TRUNCATE).

Things I've tried which all result in a crash:

• Pretty much anything on the trouble shooting page referenced in the logs (see above)
• TRUNCATE cache
• DROP TABLE cache
• ALTER TABLE cache DISCARD TABLESPACE
• Deleting the .idb and keeping the .frm file
• Setting innodb_force_load_corrupted=1 in config
• Doing all the above with innodb_force_recovery set anywhere from 1 to 6

Ultimately, the reason behind the corruption is not as important to me as is being able to clean up the corrupted tables. I have no problem removing them completely, but any operations on the table cause the DB server to crash. Most of the other solutions I've found either involve a different type of corruption that one of the above commands can fix, or they're using MySQL and it behaves slightly differently.

Answer 1

Suggest you turn off caching.

Often, having a "cache in front of a cache" provides little or no performance benefit.

Meanwhile, upgrade from 10.1; there are likely to be stability fixes in 10.2 or 10.3.