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

[maria b]

Hello,

We recently encountered a very strange issue on a production MySQL/MariaDB server and I'm trying to understand what could have caused it.

We have a Laravel application. A few days ago we noticed that a table seemed to be missing. After selecting the database and running:

SHOW TABLES;

the table did not appear in the list.

However, we were still able to query it:

SELECT * FROM table_name;

and the query returned data successfully.

At that point we executed:

FLUSH TABLES;

After that, the SELECT statement stopped working.

We then inspected the database directory under:

/var/lib/mysql/<database_name>/

and discovered that this was not limited to a single table. A significant number of tables were in an inconsistent state:

* Some tables had both .frm and .ibd files.
* Some tables had only a .frm file.
* Some tables had only a .ibd file.

We attempted to restore the database from the previous day's backup, but to our surprise the same inconsistencies were already present there. My understanding is that mysqldump only exports tables that MySQL can see through its metadata, so tables that only had orphaned .ibd files would not have been included in the dump ¿?

Eventually we had to restore from a backup that was about 5 days old, where all expected tables and files were present and consistent.

My questions are:

1. What kinds of events can lead to this type of inconsistency between .frm and .ibd files? - (the disk storage wasn't full)

2. Do people typically implement checks in production to detect mismatches between .frm and .ibd files before backups are taken? Cuz we always assumed that a successful mysqldump was enough

Additional environment information:

Operating System: Debian 12

Database Server: MariaDB 10.11.14

Version output:
mariadb Ver 15.1 Distrib 10.11.14-MariaDB, for debian-linux-gnu (x86_64)

innodb_file_per_table is enabled:
SHOW VARIABLES LIKE 'innodb_file_per_table';

Result:
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+

Version details:
SHOW VARIABLES LIKE 'version%';

Result:

version = 10.11.14-MariaDB-0+deb12u2-log
version_comment = Debian 12
version_compile_machine = x86_64
version_compile_os = debian-linux-gnu


Any ideas or similar experiences would be greatly appreciated!!