MySQL Bugs: #68837: InnoDB recovery does not handle "truncate", leaves corrupt table

Bug #68837	InnoDB recovery does not handle "truncate", leaves corrupt table
Submitted:	2 Apr 2013 15:29	Modified:	22 Jul 2014 12:56
Reporter:	Jörg Brühe	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
We are using "Cacti" to gather performance figures of a server farm.
Cacti is configured to use a MySQL instance (version 5.5.20) to keep its transient data in InnoDB tables.
Cacti is doing a "truncate" on a table every two minutes.

Since recently (and for reasons still unknown), we suffer from performance slowdowns in the database instance. We still assume the base reasons are outside MySQL, but this doesn't matter.
These slowdowns are so severe that the MySQL server process eventually takes the escape route of voluntary termination, relying on "mysqld_safe" to restart it which should then cause recovery and so recreate a working instance.

Sadly, this recovery does not correctly handle an in-progress "truncate" operation but leaves us with a broken table.

I will see to attach a log extract to this report.

How to repeat:
Cause the MySQL server to crash while a "truncate" is in progress.

Suggested fix:
Fix InnoDB crash recovery to also handle an in-progress "truncate".

Extract of the server log: voluntary crash and "truncate" table damage

Attachment: crash-truncate.log (application/octet-stream, text), 42.38 KiB.

Thank you for the report.

Strictly speaking such reaction on `kill -9` is not a bug. Have you tried innodb_force_recovery as described at http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html?

There was no "kill -9" of any manual origin involved,
it was the "time exceeded" suicide of the MySQL server process.

Also, as it had been installed from RPM and started via the "mysql" service, it had been running under control of "mysqld_safe", so the server process restart happened without human intervention.

No, we did not try "innodb_force_recovery", we overcame the issue by forcefully dropping and re-creating the table. There were no data to get, as the application had been doing a "truncate" at the moment of crash.

We have totally changed our monitoring setup since then, from "cacti" and "nagios" to "icinga" and "graphite", so we neither met that issue in the last 12 months nor can we try to reproduce it in the original way.

Thank you for the feedback.

Since this bug is not repeatable anymore closing as "Can't repeat". If you meet this issue again feel free to reopen the report. We will need full error log file in this case.