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

Discussion in 'Linux Server' started by ehansen, Mar 7, 2012.

  1. ehansen

    ehansen New Member Staff Writer

    Messages:
    115
    Likes Received:
    11
    Trophy Points:
    0
    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:
    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:
    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:
    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.

Share This Page