1

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.

  • @ypercubeᵀᴹ I'm aware of the reason. InnoDB has crash recovery built into the Storage Engine's initialization, I know this would be a better option. What I want to know is how do I determine the cause of a given crash and if this is possible. – UncaughtTypeError Jan 9 '18 at 8:38
  • I'm open to improving this question if it is too broad, any advisement to achieve this aim will be appreciated and considered valuable. – UncaughtTypeError Jan 9 '18 at 8:54
2

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.

  • So if I understand you correctly, there is essentially no way to trace the route cause of a table crash (in the case of MyISAM), we can only assume it is as result of a non-graceful shutdown? – UncaughtTypeError Jan 9 '18 at 19:27
  • @UncaughtTypeError - Yes. – Rick James Jan 9 '18 at 21:40
  • Thank you, Rick - I appreciate the time and effort put into this thorough and detailed answer. – UncaughtTypeError Jan 9 '18 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.