mysqldump innodb snapshot failing

[acaiado]

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 [URL]MySQL Bugs: #65670: mysqldump 5.5.25 client fails to dump mysql db from remote 5.0.96 server however there is no mysql server vs. client mismatch.

Any ideas what might be going on?

[gmouse]

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

[acaiado]

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

[RemiB]

The mysqldump manpage directly addresses this issue, acaiado:

Quote:

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.

[przemek]

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.