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:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.5.20 OS:Any
Assigned to: CPU Architecture:Any

[2 Apr 2013 15:29] Jörg Brühe
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".
[2 Apr 2013 15:31] Jörg Brühe
Extract of the server log: voluntary crash and "truncate" table damage

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

[21 Jul 2014 19:58] Sveta Smirnova
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?
[22 Jul 2014 12:45] Jörg Brühe
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.
[22 Jul 2014 12:56] Sveta Smirnova
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.