Re: What could cause .frm and .ibd mismatches across multiple tables?

[Nick R]

Hello,

This normally indicates that the InnoDB metadata and the physical table files became inconsistent.

1. Why this can happen

MariaDB/MySQL crash or unclean shutdown.
Interrupted ALTER, DROP, RENAME or other table operation.
Manual copying, deleting or restoring .frm / .ibd files.
Filesystem or storage issue.
Partial or incorrect database restore.

2. Why SELECT worked before FLUSH TABLES

Most likely the table was still open/cached in memory. After FLUSH TABLES, MariaDB had to reopen it from disk, and the existing inconsistency was then exposed.

3. About mysqldump

A successful mysqldump does not guarantee that all physical table files are healthy. It dumps the tables that MariaDB can access through the SQL layer, so orphaned or inconsistent .ibd files may not be included.

4. What should be checked

MariaDB error log
journalctl -u mariadb
Kernel/storage logs with journalctl -k
Any recent DDL changes or restore operations

5. For future backups

Do not rely only on the dump completing successfully. Periodically restore the backup on a test server and confirm that all expected tables are present and readable.

Also, comparing .frm and .ibd files alone is not the best health check. The MariaDB/InnoDB logs and actual restore testing are more reliable.