Corrupted index in innodb database

[amo...@gmail.com]

Hello

I am woundering if they is any good and working way to fix corrupted database indexes.

Well my database is big 388GiB and have around 4 milion rows. It is innodb and

when everytime i want to come to corrupted index i lost connection.

This is few eerors i get:

server1:~ # mysqlcheck -u root -p --auto-repair --databases my-database
Enter password:
my-databaset.table
Warning  : InnoDB: The B-tree of index "PRIMARY" is corrupted.
error    : Corrupt

Repairing tables
my-databaset.table
note     : The storage engine for the table doesn't support repair

Can please anyone help me.

[Aleksandr Kuzminsky]

Innodb offers pretty limited set of ways to fix corrupt table spaces. General approach is to dump records, drop table/index and recreate it back.

Now, how to dump records. There is innodb_force_recovery option with possible values from 1 to 6. Each value skips something in order to let you dump records. You can start with 1, try to start MySQL, dump records with mysqldump. Values 5 and 6 may cause partial data loss, so make sure you have a copy of files. 

If MySQL doesn’t start or you cannot dump successfully the table even with value 6 then you can try undrop for innodb. Check out twindb.com for guidance. 

There is also one method I use sometimes. You can select ranges of primary keys in the corrupt table and insert into a new one. Check out fetch_data.sh from the undrop toolkit for the idea. 

Aleksandr Kuzminsky,

TwinDB

Skype: aleksandr.kuzminsky

Phone: +1 (844) 533-0409 ext 101

--
You received this message because you are subscribed to the Google Groups "TwinDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to twindb+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/twindb/32d3b010-7faa-4a1e-a389-5ddde53a4546%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[amo...@gmail.com]

Thank you for all your help.

Well i have try with all 6 innodb_force_recovery and didnt have any luck.

Now i am woundering how to select range of primary keys.

Where to find examples. or how to do this?

[amo...@gmail.com]

Well now i look into log file again and get this error message:

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f31d4004bb0): is an invalid pointer
Connection ID (thread ID): 81
Status: NOT_KILLED

Is there any way to fix this.

I find few lines with different query pointer number. Is really problem here or can be anywhere else?

[Aleksandr Kuzminsky]

Check out https://github.com/twindb/undrop-for-innodb/blob/master/fetch_data.sh