Blog

Tuesday, 19 June 2018 21:38

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

Written by 
Rate this item
(0 votes)

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

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

Backups have the following warning related to the database:

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:

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

 


RESOLUTION:

Note: The steps below cover only most common use cases for Plesk on Linux installations.

Connect to the server using SSH.

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

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/

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> 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

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> 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

Read 7167 times