Corrupted innodb table crashing mysql instance how to recover table?

MySQL & MariaDB Other MySQL® Questions
1

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

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: [url]http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html[/url]
InnoDB: for more information.

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.

5

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

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: [URL]https://launchpad.net/mydumper[/URL]

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: [url]http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html[/url])

7

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

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.

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.

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:

11

Hi,

I had mysql 5.5 in my server windows 2008 R2 which has a data of about 50gb unfortunately i mysql DB has crashed. When i tried to start it shows a error 1067 , server could not be started , stopped unexpectedly. i have tried to reinstall files except the data dircetory but didnt work I have tried to recover by using innodb_force_recovery = 1,3,5,6 .Plz help me how to overcome this situation