Announcement Module
No announcement yet.

mysqldump innodb snapshot failing

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

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

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


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


      • #4
        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.


        • #5
          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.


          • #6
            good idea