We may encounter mysqldump error 1146 table doesn’t exist while we perform the database dump.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related MySQL queries.
In this context, we shall look into how to fix this error in Plesk and Directadmin.
Recently, while performing the database dump, some of our users notice the error:
mysqldump: Got error: Table ‘myDatabase.table’ doesn‘t exist when using LOCK TABLES
In order to check, we go to MySQL:
mysql -u admin -p
Then we query for the tables:
Here, we can find the table. However, when we query for that particular table:
select * from table
We get the same error:
ERROR 1146 (42S02): Table 'myDatabase.table' doesn't exist
We can try to repair it via:
mysqlcheck -u admin -p --auto-repair --check --all-databases
However, the error may prevail:
Error : Table 'myDatase.table' doesn't exist
1. Initially, we try to connect to the server using SSH
2. Then we try to use --skip-lock-tables parameter with mysqldump to skip lock tables.
#mysqldump –skip-lock-tables -u<db_user> -p<db_user_password> database_name > /root/database_dump.sql
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 mysql for both owner and group:
i. Find data dir location:
#grep datadir /etc/my.cnf
#grep -iR datadir /etc/mysql*
/etc/mysql/mysql.conf.d/mysqld.cnf:datadir = /var/lib/mysql
ii. Check permissions:
# ls -la /var/lib/mysql/example_db/
iii. Fix permissions:
# chown -R mysql:mysql /var/lib/mysql/example_db/
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>;
Note: We need to replace the <TABLENAME> with 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 .frm files still persist on the file system. We remove it:
i. To verify that table is corrupt or not, we run:
# plesk db
mysql> use database example_db;
mysql> desc <TABLENAME>;
If this command fails with the error, it means that ibdata* does not have the information about the table and we need to remove the .frm file.
ii. To do so, we browse to the database directory /var/lib/mysql/example_db/ and move .frm 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.
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:
ls -la table.*
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.frm
This can output the full CREATE TABLE syntax. We save this somewhere, until the end of the last ; character.
Note, we can either delete the “CHARACTER SET “, or change it to the correct charset.
3. Then, we remove the broken table. To do so, we login to /phpMyAdmin and run the query:
DROP TABLE user_db.table
4. Finally, we run the CREATE TABLE query from above, to rebuild the table.
This article covers methods to resolve mysqldump: Got error: 1146: Table doesn't exist. To fix this error, you can simply save the database and dump it with innodb_force_recovery option. Most InnoDB corruptions are hardware-related. Corrupted page writes can be caused by power failures or bad memory. The issue also can be caused by using network-attached storage (NAS) and allocating InnoDB databases on it.