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

    chek New Member

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

    [​IMG]
  3. Darley Stephen

    Darley Stephen New Member

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

Share This Page