MySQL – Debugging Table Crashes in MySQL/MariaDB

mariadbMySQL

I have a MyISAM table that keeps crashing. How would I figure out why it is crashing? The error messages say nothing other than that it crashed. Is there a log setting I could activate?

This is mariadb 10.0.17 on Arch Linux.

Best Answer

Turns out I ran into this 5-yr-old bug: http://bugs.mysql.com/bug.php?id=54828 in combination with a leftover (no idea from where) zero-byte file.

How I debugged it: I set up the query log:

set global general_log 'mysql.log';
set global general_log = 'ON';

which turned out to write to a file far below /tmp/systemd-private... because I'm running systemd. tail -f that file showed the crash happened when the application I'm running executed "OPTIMIZE TABLE".

Invoking that manually showed error message:

Can't create new tempfile: '/var/lib/mysql/zxnifltlapzlcumb/items.TMM'
Operation failed    

and that happened because a month-old zero-byte file already was in that place. I deleted it, and the problem went away.