Not the answer you need?
Register and ask your own question!

mysqldump innodb snapshot failing

acaiadoacaiado EntrantCurrent User Role Beginner
I have a cronjob set to take hourly snapshots of our database. About once a week maybe I get an error during the mysqldump that looks like this.

Error: Couldn't read status information for table foo_table ()
mysqldump: Couldn't execute 'show create table `foo_table`': Table 'foo_dbname.foo_table' doesn't exist (1146)

It's an innodb database, and I am using the --single-transaction flag which I thought locked the db at the state it is in when the command gets executed.

The error looks similar to this however there is no mysql server vs. client mismatch.

Any ideas what might be going on?


  • gmousegmouse Mod Squad Inactive User Role Beginner
    drop table is one of the commands that is immediately visible to all transactions
  • acaiadoacaiado Entrant Current User Role Beginner
    So there is no way to take that exact snapshot if drop table is occurring?
  • RemiBRemiB Entrant Current User Role Beginner
    The mysqldump manpage directly addresses this issue, acaiado:

    While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

    I don't believe that there is any way for --single-transaction to work with a concurrent DROP TABLE (or other listed statement).

    I'd suggest either rescheduling the DROP TABLE statement to occur before or after the backup, or you can convert to a --lock-tables style of table locking; but note that has serious implications for concurrent access to the tables involved.
  • przemekprzemek Percona Support Engineer Percona Staff Role
    What I would recommend is to setup a slave dedicated for backups purposes. Then your backup cronjob just stops slave, notes current position against master, does mysqldump and starts slave. Easy and eliminates any backup's influence to a production server.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.