GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

My InnoDB Table is corrupted from optimize table.

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

  • My InnoDB Table is corrupted from optimize table.

    My server autorun optimize table everyday and today, it run and failed for 6 hours until I found out. Percona Server become cannot start. It keep saying page is corrupt so, I set innodb_force_recovery=1 and it can run again. I do recover by using below method

    http://www.mysqlperformanceblog.com/2008/07/04/recovering-in nodb-table-corruption/

    It helped me recover some of database out but 20% is unrecoverable and it's table user which is the most important.

    My question is "Did optimize table should not be run everyday to make the database run faster?" and "Do optimize table can make the database corrupt?" My server is Joyentcloud which is run on OpenSolaris with Percona Server 5.5. I suspect if it's hardware problem from hosting itself or it's some mysql bug. Do you have any suggestion?

  • #2
    If it's getting corrupted, there is probably a problem with the storage system. It is very unlikely that it's a MySQL bug.

    You are not supposed to be able to corrupt your database! You are not doing anything wrong.

    I would not optimize the table every day unless you can measure that it improves performance. See http://www.xaprb.com/blog/2010/02/07/how-often-should-you-us e-optimize-table/

    Comment


    • #3
      Do it possible that cronjob accidentally run optimize twice while the first one still running and end up corrupt table?

      Comment


      • #4
        No. The second one will block on the first one and wait until it is finished. Again, it should be absolutely impossible for you to corrupt data. It has to be a bug in the server or in the storage system. My bet in this case is the storage.

        Comment


        • #5
          Can Innodb Recovery Toolkit help in this issue? But I cannot compile it on Solaris anyway.

          Comment


          • #6
            You can copy the data to another system and recover it there. InnoDB's data file format is platform and architecture independent.

            Comment


            • #7
              Wow. Never know that. Thanks!

              Comment

              Working...
              X