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

    Likes Received:
    Trophy Points:
    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.

    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:
    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:
    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.
  2. chek

    chek New Member

    Likes Received:
    Trophy Points:
    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"


Share This Page