MySQL Bugs: #74772: Running REPAIR TABLE [table] EXTENDED Causes MySQL to Restart Constantly

Bug #74772	Running REPAIR TABLE [table] EXTENDED Causes MySQL to Restart Constantly
Submitted:	10 Nov 2014 21:53	Modified:	16 Apr 2018 12:47
Reporter:	Gino Williams	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.5.18-log	OS:	Solaris
Assigned to:		CPU Architecture:	Any
Tags:	archive, partitions

Description:
We have recently encountered problems with MySQL restarting (mysqld_safe mysqld restarted) when trying to repair archive tables that have become corrupted. 

We have 53G of memory which isn't being exhausted by any heavy-lifting processing. Only 131M of space has been used out of a total of 1.1T.

In the past, we have been able to perform a REPAIR TABLE ... EXTENDED with no problems.

How to repeat:
The following commands have been run, all resulting in a consistent restart of the MySQL instance:
* repair table [table name];
* repair table [table name] extended;
* alter table [table name] repair partition p[0-12];
* alter table [table name] repair partition p[0-12] extended;
* mysqlcheck -u[user] -p[password] [schema name] --tables [table name]

Thank you for the bug report. Could you please check this issue with most recent released version 5.5.40?. Thanks.

Thank you for the bug report. Could you please check this issue with most recent released version 5.5.40?. Thanks.

Sorry I forgot to ask you to check in the server log-error a call stack of probably server crash. Thanks.

The problem was the same on 5.5.40. We created a new instance on a completely separate box to make sure that we were not upgrading MySQL on a possibly damaged hardware. Error logs did not show anything running during the restart other than the REPAIR TABLE statement.

It appears that there was a particular partition that was irreparably damaged. I dropped that partition, which allowed access to the table without initiating a repair behind the scenes. I reloaded the data for the date range (per the dropped partition) from raw files. No other partitions were corrupted. As a precautionary, I performed a full repair to completion without any restarts on the MySQL instance.

Clearly, a partition can become so corrupted that a repair will not work and may cause restarts to the MySQL instance during a repair or rebuild of a partition. Unfortunately, recovery in this instance may have to be manually intensive.

Is it possible that a partition can indeed become so corrupted that a full repair will never work? If this is the case, then I need to come up with a plan for replacing MySQL with a RDBMS that does both partitioning and table compression.

Hi,

The answer to your question is positive. If a disk or partition gets corrupted, then there is no cure for your tables, unless you have a backup. The only storage engine that is constantly checking whether data was written properly to the disk is our InnoDB storage engine. Provided, you do not turn off all the security settings, like double writing or similar.

Hence, that is our advice to you. Switch to InnoDB SE. Our manual has all explanations on how to do it and how to tune that engine.