•  

Announcement

Announcement Module
Collapse
No announcement yet.

mysqldump innodb snapshot failing

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

  • mysqldump innodb snapshot failing

    #1
    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?
    Tags: None

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

    Comment


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

      Comment


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

        Comment


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

          Comment


          • #6
            good idea

            Comment

            Working...
            X