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 http://bugs.mysql.com/bug.php?id=65670 however there is no mysql server vs. client mismatch.

Any ideas what might be going on?

Comments

  • 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:

    <cite>Quote:</cite>
    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.