mysqldump error 1146 table doesn’t exist – How to fix it

We may come across the mysqldump error 1146 table doesn’t exist while we perform the database dump.

As part of our Server Management Services, we assist our customers with several MySQL queries.

Today, let us see how to fix this error in Plesk and Directadmin.

mysqldump: Got error: 1146: Table doesn‘t exist

Recently, while performing the database dump, some of our users noticed the error:

mysqldump: Got error: Table ‘myDatabase.table’ doesn‘t exist when using LOCK TABLESCopy Code

In order to check, we go to MySQL:

 mysql -u admin -p
Copy Code

Then we query for the tables:

show tables;
Copy Code

Here, we can find the table. However, when we query for that particular table:

select * from table
Copy Code

We get the same error:

ERROR 1146 (42S02): Table 'myDatabase.table' doesn't exist
Copy Code

We can try to repair it via:

mysqlcheck -u admin -p --auto-repair --check --all-databases
Copy Code

However, the error may prevail:

Error : Table 'myDatabase.table' doesn't existCopy Code

The major causes of this can be:

• InnoDB tablespace might have been 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
• Incorrect permissions and ownership on table files in the MySQL data directory
• A corrupt table data.

How to fix it?

Moving ahead, let us see how our Support Techs fix this error in both Plesk and DirectAdmin.

Solution: Plesk

1. Initially, we tried to connect to the server using SSH.
2. Then we try to use

   --skip-lock-tablesCopy Code

    parameter with 

   mysqldumpCopy Code

   to skip lock tables.
   For example,

   #mysqldump –skip-lock-tables -u<db_user> -p<db_user_password> database_name > /root/database_dump.sqlCopy Code

3. If it does not help, we check permissions and ownership on the table’s files in the MySQL data directory for the database that fails to dump. It should be

   mysqlCopy Code

    for both owner and group:
   • Find data dir location:
     RHEL/CentOS

     #grep datadir /etc/my.cnf
     datadir=/var/lib/mysqlCopy Code

     Debian/Ubuntu

     #grep -iR datadir /etc/mysql*
     /etc/mysql/mysql.conf.d/mysqld.cnf:datadir = /var/lib/mysqlCopy Code

   • Check permissions:

     # ls -la /var/lib/mysql/example_db/Copy Code

   • Fix permissions:

     # chown -R mysql:mysql /var/lib/mysql/example_db/Copy Code

4. If it is still not possible, we try to repair the table in the error using the native MySQL repair tool:

   # plesk db
   
   mysql> use example_db;
   mysql> REPAIR TABLE <TABLENAME>;Copy Code

   Note: We need to replace the 

   <TABLENAME>Copy Code

   with the table name in the error message.

5. If the issue still persists, most probably ibdata* file does not have the info about the table. However, the orphaned

   .frmCopy Code

   files still persist on the file system. We remove it:
   • To verify whether the table is corrupt or not, we run:

     # plesk db
     
     mysql> use database example_db;
     mysql> desc <TABLENAME>;Copy Code

     If this command fails with the error, it means that

     ibdata*Copy Code

     does not have the information about the table and we need to remove the

     .frmCopy Code

     file.
   • To do so, we browse to the database directory

     /var/lib/mysql/example_db/Copy Code

      and move 

     .frmCopy Code

      file:

     # cd /var/lib/mysql/example_db/
     # mv <TABLENAME>.frm /root/<TABLENAME>.frm

6. If these options fail and we have no valid backups to restore, the only available option to save the database is to dump it with the innodb_force_recovery option

Solution: DirectAdmin

Suppose, we get the error for the User database and Table:

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

1. In this case, we 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.*Copy Code

   If it’s just the table.frm file, then the rest of the data is likely lost. However, we may be able to rebuild the table.

2. To do so, we need to read the .frm file. We need the mysqlfrm tool for that, eg: yum install mysql-utilities. Once we install it, we check if it can be read:

   mysqlfrm –diagnostic table.frmCopy Code

   This can output the full CREATE TABLE syntax.  We save this somewhere, until the end of the last ; character.

   We can either delete the “CHARACTER SET “,  or change it to the correct charset.

3. Then, we have to remove the broken table.  To do so, we login to /phpMyAdmin and run the query:

   DROP TABLE user_db.tableCopy Code

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

[Finding it hard to fix? We’d be happy to assist you]

Conclusion

In short, we saw how our Support Techs fix the MySQL error for our customers.