mysqldump table doesn't exist when using LOCK TABLES

Created:

2016-11-16 13:02:07 UTC

Modified:

2017-04-22 09:32:12 UTC

6

Was this article helpful?


Have more questions?

Submit a request

mysqldump table doesn't exist when using LOCK TABLES

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 show tables and check if table is listed. If not, move .frm file out from the database directory
  2. Check permissions and ownership on table's files in mysql data directory: ideally owner should be mysql user and permissions should be set to 660
  3. Repair the table
  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.