- Plesk for Linux
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
stderr: mysqldump: Got error: 1932: "Table 'exampleDB.table' doesn't exist in engine" when using LOCK TABLES
- InnoDB tablespace might have been deleted and recreated but corresponding
.frmfiles of InnoDB tables from the database directory were not removed, or
.frmfiles were moved to another database
- Incorrect permissions and ownership on table's files in MySQL data directory
- The table data has been corrupted
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.
Connect to the server using SSH
Try to use
mysqldumpto 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
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
mysqlfor both owner and group:
Find data dir location:
# grep datadir /etc/my.cnf
# grep -iR datadir /etc/mysql*
/etc/mysql/mysql.conf.d/mysqld.cnf:datadir = /var/lib/mysql
# ls -la /var/lib/mysql/example_db/
# chown -R mysql:mysql /var/lib/mysql/example_db/
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>;
<TABLENAME>in the command above is a placeholder and should be replaced with the table name in the error message
If the issue is still persists, the most probably ibdata* file does not have the info about the table, however the orphaned
.frmfiles 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
.frmfile have to be removed.
Browse to database directory
# cd /var/lib/mysql/example_db/
# mv <TABLENAME>.frm /root/<TABLENAME>.frm
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
Please sign in to leave a comment.