mysqldump innodb snapshot failing

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?

drop table is one of the commands that is immediately visible to all transactions

So there is no way to take that exact snapshot if drop table is occurring?

The mysqldump manpage directly addresses this issue, acaiado:


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.

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.