Symptoms
You may come across the following errors during backup:
The backup process encountered the following error:
[2020-12-05 02:12:21 +0200] mysqldump: Couldn't execute 'show fields from `erhard_example_cal_events_p3`': Incorrect file format 'erhard_example_cal_events_p3' (130)
or
[2022-11-29 02:29:59 -0500] mysqldump: Couldn't execute 'show fields from `prefix_table`': Can't find file: './user_db/prefix_table.MYI' (errno: 2 "No such file or directory") (1017)
Description
The error means that mysqldump was not able to view the table's fields. As we can see below the table that reported the issue has a size of 0 which means that a corruption occurred and the table is empty.
# ll /var/lib/mysql/thisisanexample | grep events
-rw-r----- 1 mysql mysql 17328 Jul 20 23:17 erhard_example_cal_events.frm
-rw-r----- 1 mysql mysql 260 Jul 20 23:17 erhard_example_cal_events.MYD
-rw-r----- 1 mysql mysql 2048 Jul 20 23:17 erhard_example_cal_events.MYI
-rw-r----- 1 mysql mysql 12966 Jul 20 23:17 erhard_example_cal_events_p2.frm
-rw-r----- 1 mysql mysql 80 Jul 20 23:17 erhard_example_cal_events_p2.MYD
-rw-r----- 1 mysql mysql 2048 Jul 20 23:17 erhard_example_cal_events_p2.MYI
-rw-r----- 1 mysql mysql 30360 Nov 22 03:46 erhard_example_cal_events_p3.frm
-rw-r----- 1 mysql mysql 0 Nov 22 03:46 erhard_example_cal_events_p3.MYD
-rw-r----- 1 mysql mysql 0 Nov 22 03:46 erhard_example_cal_events_p3.MYI
Workaround
Restore the table manually from backup or restore a whole account backup of the affected user.
Additional resources:
Restore a single database from a MySQL data directory backup
Restore an account from a backup file on the server
If you do not know which database holds the table that is having issues. Use the following query:
mysql> select table_name,table_schema as dbname FROM INFORMATION_SCHEMA.TABLES WHERE table_name='mytable_mytable';
+-----------------+--------------+
| table_name | dbname |
+-----------------+--------------+
| mytable_mytable | myfavouritedatabase |
+-----------------+--------------+
1 row in set (0.06 sec)
Please note, in the above example, mytable_mytable needs to be replaced with the correct table name.