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

[ehansen]

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:

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.