Announcement

Announcement Module
Collapse
No announcement yet.

Corrupted innodb table crashing mysql instance how to recover table ?

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

  • Corrupted innodb table crashing mysql instance how to recover table ?

    Hello,

    Running a simple query against corrupted innodb table is crashing mysql instance .

    table test.xyz got corrupt during crash and truncate table command was in progressing when mysql crash , now
    running a simple select * query is also crashing db .
    mysql> use test;
    Database changed
    mysql> select * from xyz;
    ERROR 2013 (HY000): Lost connection to MySQL server during query
    mysql>
    mysql>
    mysql> select * from xzy;
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
    ERROR:
    Can't connect to the server


    Error Log :

    [ERROR] Build InnoDB index translation table for Table ./test/xyz failed
    [ERROR] Table ./test/xyz has no primary key in InnoDB data dictionary, but has one in MySQL! If you created the table with a MySQL version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MySQL internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.
    [Warning] Table ../test/xyz key_used_on_scan is 0 even though there is no primary key inside InnoDB.
    [ERROR] Innodb could not find key n 0 with name PRIMARY from dict cache for table test.xyz

    Any suggestion on how to recreate table will be highly appreciated .

  • #2
    Try to start mysql with innodb_force_recovery = 6

    Comment


    • #3
      Thanks I start mysql with innodb-force-recovery 6 , that helped but now I 'm seeing lot of below errors in error.log when operating mysql in normal mode .

      What this means and how to fix this.

      InnoDB: Error: page 669 log sequence number 47260934757837
      InnoDB: is in the future! Current system log sequence number 129155434634.
      InnoDB: Your database may be corrupt or you may have copied the InnoDB
      InnoDB: tablespace but not the InnoDB log files. See
      InnoDB: http://dev.mysql.com/doc/refman/5.5/...-recovery.html
      InnoDB: for more information.

      Comment


      • #4
        There could be multiple reasons for these errors depending of the damage due to mysql crash, one is innodb log files are corrupt and not in sync with innodb tablespace.

        As you said mysql crashed during a table being truncated so check if that table and its data is available and accessible using select queries, also check .ibd file exist for that table.
        if all data is ok then take a dump, delete/move mysql directory, create new mysql structure using mysql_install_db and restore the database.

        Comment


        • #5
          It's a huge database of 4+ TB , restore is not possible .Is there any other solution for this problem.

          Comment


          • #6
            SQL dump and restore is the safest solution here, though it would indeed be terribly slow for that large data set. If you have the data split over many tables though, you can try with mydumper/myloader tools which are a lot faster then starndard mysqldump. You can find this tool here: https://launchpad.net/mydumper

            You can also try this: stop mysql, move innodb redo logs to other location and start mysql so that new logs are created. (The InnoDB logs change procedure is described on the bottom of this doc: http://dev.mysql.com/doc/refman/5.5/...iguration.html)

            Comment


            • #7
              moving innodb redo log files does'nt help I tried it. After mysql start same error popped up again.

              Comment


              • #8
                If I understand correctly, you did drop / recreate that table while innodb-force-recovery mode was active, right? So no more inconsistency in InnoDB table dictionary?
                Regarding the LSN in the future messages - those are just warnings as a result of previous InnoDB logs being discarded during recovery mode, and they should stop once the old position will be reached in the future again.

                Comment


                • #9
                  Yes correct , i dropped and recretetd the table with innodb-force-recovey = 6. database is not complaining anymore about any particualr table corruption .

                  But this Error ,seems more scary as day your database may be corrupted . And what if there is any crash in future and there are more surprises because of this error.

                  InnoDB: Error: page 669 log sequence number 47260934757837
                  InnoDB: is in the future! Current system log sequence number 129155434634.
                  InnoDB: Your database may be corrupt or you may have copied the InnoDB
                  InnoDB: tablespace but not the InnoDB log files. See
                  InnoDB: http://dev.mysql.com/doc/refman/5.5/...-recovery.html
                  InnoDB: for more information.

                  Comment


                  • #10
                    InnoDB is a storage engine of MySQL. It support foreign key (Declarative Referential Integrity). To repair and recover the corrupt table you need a powerful repair tool. MySQL Repair Tool can easily recover any type of corruption in MySQL database. For more in formation:
                    Last edited by scott.nemes; 04-15-2014, 11:41 AM. Reason: Removed questionable link

                    Comment

                    Working...
                    X