Resolving MySQL error 1146: "table doesn't exist" when doing backup

E

ehansen

Guest
While I'm not the biggest saint in the IT world when it comes to doing backups ([religious figure]-bless the fact OpenVZ has a simple container-back up function), when you do perform a backup one of the worse things that can possibly happen (besides a corrupted backup) is the backup not being created due to an error. Even though I wasn't doing a back up at the time I ran into this issue, I thought it would be helpful as MySQL still has a pretty strong hold on the database market, especially on *nix systems.

Error
When running mysqldump to back up a database, you get this error:
mysqldump: Got error: 1146: Table 'db_name.table_name' doesn't exist when using LOCK TABLES

This error can be for any number of reasons. I've ran into this because /var was 80+% full (very, very horrible situation). While clearing /var is pretty easy (if you're brave, run this command: for i in `find /var/log -type f -iname .log`; do rm -rf $i; done), it won't always be that easy. The real tricky part is when you get this error on a table or database you thought you already deleted. Welcome, this article.

Error Checking
To make sure that the table does exist and there's no issues, you can run mysqlcheck:
Code:
mysqlcheck -u mysql_username -p database_name
This will check and repair any database and tables fed to it. However, if you receive something like:
database_name.table_name
Error: Table 'database_name.table_name' doesn't exist
status: Operation failed

Solution
There's one quick way to resolve this, as this usually deals w/ a corrupt database or table, and if you don't have a previous (working) backup then you'll not be able to get around it any other way besides restructuring and re-entering the data. What you do now is simply delete the table by doing this:
mysql -u mysql_user -p
mysql> use database_name
mysql> show tables; # If the table that's been giving you grief shows here, then you can try to run a SELECT query on it to see if any data is there, but if you get an error saying the table doesn't exist, then...
mysql> drop table table_name;
mysql> quit

This guide is short, but it can definitely save you a lot of time. However, it's always suggested to create a daily snapshot of your server. My favorite command of late is mysqldump -u mysql_user -p database_name | bzip2 > database_name.sql.bz2

BZip2 typically has the best compression ratio for ASCII/text data I've found, and generally the best compression period for my causes.

This issue alone is a very time-consuming problem to experience, especially when its not involving a table that wasn't properly disposed of. Permission issues can pose a problem as well as a nearing-full /var. The part with /var is why I always suggest creating a separate partition for that directory and setting up logwatch as it will notify you daily the partition information (df -h). If anyone is running into this issue and /var is fine as well as no corrupted data, leave a comment and I'll do my best to help you out.
 


When i do "mysqldump", I got - "Table doesn't exist (1146)"
when i do "mysql> show tables;" - it shows all tables w/o errors or smth
When i do "mysql> drop table" - i got "ERROR 1051 (42S02): Unknown table"

hD372348F
 
This issue will also occur after restoring databases from a, backup of all mysql data folders)
One possible pre-solution to this issue is ,
1. should copy the mysql data folder (/var/lib/mysql -- linux OR C:\ProgramData\MySQL\MySQL Server 5.1\data -- windows)
2. in the new server stop the mysql and paste all the folders pertaining to different databases , except the main "mysql" database.
3. make sure to copy and paste "ibdata1" file from the backed up folder into the new server "mysql data folder".
4. make sure to give permission to "mysql" user for that file (windows user need not worry).
5. restart mysql you should be able to see all the backed up databases correctly configured , and you should be able to access the table data without getting that error (1146).
 

Members online


Top