Magic Innodb Recovery self healingPeter Zaitsev
We have certain type of the table corrupting with Innodb, as it is limited to only one particular index on one particular table type it is likely to be Innodb bug but Heikki currently could not find what could be causing it.
Happily as we have data stored in many tables of same format rather than one monster table these rare corruptions did not cause too much problems to us – as it was not clustered index we always could repair table by running ALTER TABLE with limited impact to production system (as only one table of about a hundred will be locked)
So everything kind of worked (of course we still hoped new MySQL release will have this Innodb bug fixed) until today we got MySQL to crash during recovery process right after 17% of log records were applied.
Such corruption is one of the worst onces, because you can’t really recover data on per table basics.
Also if MySQL crashes during recovery lower values of innodb_force_recovery normally would not work. Reading Documentation one may think you need to set it as far as 6 (SRV_FORCE_NO_LOG_REDO) if crash happens during redo stage of log recovery. As it is very nasty option I wanted to check if this is really required by trying lower recovery settings.
Happily I was able to restart MySQL with innodb_force_recovery=4 (SRV_FORCE_NO_IBUF_MERGE). So in my case probably Insert buffer merge was affecting corrupted page rather than log replay itself so I got lucky.
My initial plan was to now dump potentially corrupted tables (in this particular corruption instance Innodb did not print table name in question), drop tables, restart Innodb without innodb_force_recovery and load them back. But just yesterday one of the customers told me he had Innodb magically healing itself after he was able to complete recovery with innodb_force_recovery=4. So I decided to check if I’m to get lucky second time today.
And Indeed Restarting Innodb with innodb_force_recovery=0 allowed it to start normally and I could rebuild affected tables my
So when dealing with Innodb recovery for bad corruptions you may want to:
– Start with lower innodb_force_recovery settings and see which one allows you to start.
– After recovery succeeds you can try restarting with lower recovery setting as it may allow you to recover more data or avoid full dump and restore.
Another trick which helped me in previous instances, but not this one is to do full system restart before attempting recovery. In few instances problems were caused by Kernel bug, inconsistence in OS cache or RAID cache or something else which made problem to disappear after system restart.