mysqldump table doesn't exist when using LOCK TABLES

Created:

2016-11-16 13:02:07 UTC

Modified:

2017-08-16 10:54:06 UTC

19

Was this article helpful?


Have more questions?

Submit a request

mysqldump table doesn't exist when using LOCK TABLES

Applicable to:

  • Plesk

Symptoms

MySQL database dump fails with error:

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

Cause

  1. Probably InnoDB data files were 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. Table is corrupted
  4. Table created in uppercase

Resolution

  1. Run the following command and check if the table is listed:
    mysql> show tables

    If not, move .frm file out from the database directory.
  2. Check permissions and ownership on table's files in mysql data directory: on Linux, owner should be mysql user
  3. Try to repair the table:
    mysql> REPAIR TABLE example
  4. Set lower_case_table_names
  5. Use --skip-lock-tables parameter with mysqldump
Have more questions? Submit a request
Please sign in to leave a comment.