mysqldump error output: mysqldump: Got error: 1146: Table 'user_db.table' doesn't exist when using LOCK TABLES


Enter Your Query:
Use '%' for wildcards and quotes for "exact phrases"


Top Level » MySQL / MariaDB

mysqldump error output: mysqldump: Got error: 1146: Table 'user_db.table' doesn't exist when using LOCK TABLESLast Modified: Oct 9, 2017, 6:00 pm
Some types of database corruption can cause the data to be lost, but will leave behind enough information to rebuild the empty table structure.
Only use this guide if you've given up trying to recover the table contents, and just want to fix the stable structure.

Lets say you get the above error for User database , and table throwing the backup error:

mysqldump error output: mysqldump: Got error: 1146: Table 'user_db.table' doesn't exist when using LOCK TABLES

  1. Check to see if there are any other data files, or if it's just the .frm file:

    cd /var/lib/mysql/user_db
    ls -la table.*

    If it's just the table.frm file, then the rest of the data is likely lost, but you may be able to rebuild the table.

  2. For that, we need to read the .frm file.  The mysqlfrm tool is required for that, eg:

    yum install mysql-utilities

    (or rpm, connector-python) Once installed, see if it can be read:

    mysqlfrm --diagnostic table.frm

    which might output the full CREATE TABLE syntax.  Save this somewhere save, until the end of of the last ; character.   Ignore any # lines, as they're just comments.

    Note, if you see "CHARACTER SET ", you can either delete those 3 words, or change to the correct charset, if you happen to know what it should be set to.

  3. Now, we need to remove the broken table.  Login to /phpMyAdmin and run the query:

    DROP TABLE user_db.table


  4. Lastly, run the CREATE TABLE query from above, to rebuild the table.

Although this doesn't rebuild the data, it will at least get the table back, which can be find in some cases (if you're lucky, like a temp cache table)

© 2018 JBMC Software, Suite 173  3-11 Bellerose Drive, St Albert, AB  T8N 1P7  Canada.  Mon-Fri 9AM-5PM MST