Dump of the MySQL database hosted on the Plesk server fails: mysqldump table doesn't exist when using LOCK TABLES

Symptoms

• Dump of the MySQL database hosted on the Plesk server fails:

  CONFIG_TEXT: mysqldump: Got error: 1146: Table '<TABLENAME>' doesn't exist when using LOCK TABLES

• Backups have the following warning related to the database:

  CONFIG_TEXT: WARNING: mysql 'exampleDB'
  Unable to execute SQL: Table 'exampleDB.<TABLENAME>' doesn't exist in engine. SQL query: SHOW FULL COLUMNS IN `<TABLENAME>`

• Migration fails with the following error:

  PLESK_ERROR: Failed to copy content of database 'exampleDB'
  Migration tools tried to perform operation in 3 attempts: Command execution failed on the source server 'source' (203.0.113.2) with non-zero exit code.
  command: MYSQL_PWD="$(cat /etc/psa/.psa.shadow)" mysqldump --no-defaults -h localhost -P 3306 -uadmin --quick --quote-names --add-drop-table --default-character-set=utf8 --set-charset --routines --events exampleDB > /root/plesk_migrator/plesk_migrator-dy0onpkt6k9v4ydtwlfpf507xswuqmyh/db-dumps/exampleDB.sql
  exit code: 2
  stdout:
  stderr: mysqldump: Got error: 1932: "Table 'exampleDB.table' doesn't exist in engine" when using LOCK TABLES

Cause

1. InnoDB tablespace might have been deleted and recreated but corresponding .frm files of InnoDB tables from the database directory were not removed, or .frm files were moved to another database
2. Incorrect permissions and ownership on table's files in MySQL data directory
3. The table data has been corrupted

Resolution

Note: The steps below cover only most common use cases for Plesk on Linux installations. This administrative task might be done by Plesk Professional Services team on your behalf. For more information, contact Plesk Professional Services team.

1. Connect to the server using SSH

2. Try to use --skip-lock-tables parameter with mysqldump to skip lock tables, like in the example below:

   # mysqldump --skip-lock-tables -u<db_user> -p<db_user_password> database_name > /root/database_dump.sql

3. If the step above does not help, check permissions and ownership on table's files in MySQL data directory for the database that fails to dump (e.g. example_db), it should be mysql for both owner and group:

   • Find data dir location:

     RHEL/CentOS

     # grep datadir /etc/my.cnf
     datadir=/var/lib/mysql

     Debian/Ubuntu

     # grep -iR datadir /etc/mysql*
     /etc/mysql/mysql.conf.d/mysqld.cnf:datadir = /var/lib/mysql

   • Check permissions:

     # ls -la /var/lib/mysql/example_db/

   • Fix permissions:

     # chown -R mysql:mysql /var/lib/mysql/example_db/

4. If it is still not possible to dump database try to repair the table in the error using native MySQL repair tool:

   # plesk db

   MYSQL_LIN: mysql> use example_db;
   mysql> REPAIR TABLE <TABLENAME>;

   Note: <TABLENAME> in the command above is a placeholder and should be replaced with the table name in the error message

5. If the issue is still persists, the most probably ibdata* file does not have the info about the table, however the orphaned .frm files still persists on the file system. Remove .frm files as below:

   • Verify that table is corrupted:

     # plesk db

     MYSQL_LIN: mysql> use database example_db;
     mysql> desc <TABLENAME>;

     If the command above fails with the error, it means that ibdata* does not have the information about the table and the .frm file have to be removed.

   • Browse to database directory /var/lib/mysql/example_db/ and move .frm file:

     # cd /var/lib/mysql/example_db/
     # mv <TABLENAME>.frm /root/<TABLENAME>.frm

6. If none of the steps above helps and there are no valid backups to restore, the only available option to save the database is to dump it with innodb_force_recovery option: How to fix InnoDB corruption cases for the MySQL database? – I. Force InnoDB Recovery