Mysql – How to trace the root cause of a MySQL database table that crashed

myisamMySQLmysql-5.6troubleshooting

Are there any standard practices or methods for tracing the cause of a MySQL table crash?

If I repair a table that had crashed, how do I determine what caused it to crash in the first place?

Storage Engine: MyISAM
MySQL Version: 5.6.38

I'm specifically interested in learning how to troubleshoot this common issue, not particularly in the methods to avoiding it.

Best Answer

The cause is almost always a non-graceful shutdown -- abort, power failure, etc. So, I suggest you already know what is causing each occurrence.

It goes something like this...

  1. When a MyISAM table is opened, it is flagged (in the .MYI file) that it is open.
  2. In a graceful shutdown, the index blocks from the key_buffer are flushed (to the .MYI) and the the flag is cleared. Almost always the index file is now intact.

So, if MySQL is aborted or there is a power failure or ..., the flag is still set. When restarting the flag(s) is noticed. Often there is no real damage, but you still need to do at least a REPAIR...quick. But in some cases, the only real recovery is to rebuild the index(es).

For this (and many other reasons), MyISAM is deprecated and destined for the scrap heap. Meanwhile, little or no effort is being put into MyISAM, especially by Oracle, which has it disabled in version 8.0. (MyISAM is likely to live a little longer in MariaDB.)

Another nasty in MyISAM is with something like

UPDATE tbl SET x = x + 1;

and that is being applied to all rows. If there is a crash in the middle of the execution of the UPDATE, some rows will be changed, some will not. You have no way to know how far the update got before the crash. The table is not corrupt, but the data is effectively corrupt. There is no flag indicates this kind of corruption.

Another nasty:

UPDATE tbl SET str = CONCAT(str, 'xyz');

That is, increase the size of some column in some row. This is likely to require two writes to the data (.MYD) because the row is now longer and won't fit where it had been. If only one write gets to disk before a crase, then the data file is corrupted. REPAIR may or may not be able to fix this, and is likely not to recover at least one row.

Etc.

Note: Everything I have said in this Answer probably applies to all versions of MyISAM even back to before version 4.0. MyISAM is antique technology that has not had much done to it in a loooong time.

Related Question