I.T. Security and Linux Administration

Mar 12 2012   10:59PM GMT

Resolving MySQL error 1146: “table doesn’t exist” when doing backup



Posted by: Eric Hansen
Tags:
Corrupted data
MySQL

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.

5  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • arunmano86

    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.

    10 pointsBadges:
    report
  • arunmano86
    ODRL is expressed in schema-valid XML syntax. This syntax is formally specified by the XML Schemas defined in Appendix A and Appendix B. These are the normative references for the ODRL expression language and data dictionary (respectively). Note: If the human language needs to be specified for any elements containing string values, then the use of the standard "xml:lang" attribute is recommended including the XML namespace.
    10 pointsBadges:
    report
  • arunkumarcoimbatore86
    The ODRL Security Model can be expressed using XML. Example 12 shows the asset element now including the encryption element with appropriate digest and content encryption key (cek) values.

    Example 13 shows a rights expression that has been digitally signed. The rights expression element has been allocated the "id" of "MyRightsData". (Note: this "id" attribute is not the same as the "uid" element used in the "context" element, although they both could have the same value.) The id is used by the "Reference" element inside the Signature element. The "transforms" element also indicates the algorithms required to process the signed XML expression.

    Both Example 12 and Example 13 need to include a mixture of XML namespaces (to support ODRL, XML Signature, and XML Encryption) and these are clearly indicated with the namespaces prefixes.

    10 pointsBadges:
    report
  • jenlampton
    what do you do when the table shows up when you do a show tables; but when you ask mysql to drop table tablename; it tells you: Unknown table 'tablename' ?
    10 pointsBadges:
    report
  • larry321
    Thanks for solution with good explanation. We recently encountered this MYSQL database corruption issue in our mysql application and fixed with the a third party solution given here
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: