I frequently see questions about how to understand the nature of errors in MySQL’s error log. Now, there is a lot of complexity to this — the flowchart would be quite large, as with any nontrivial piece of software. But there is one particular class of errors that is relatively easy to diagnose, if you pay close attention to the error message.
Often an error has a little number in it, as in this example from our forums:
120326 16:56:45 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_21b2_0.MYI'; try to repair it
120326 16:56:45 [ERROR] Got an error from unknown thread, storage/myisam/mi_write.c:223
120326 16:56:45 [ERROR] /usr/sbin/mysqld: Sort aborted: Error writing file '/tmp/MYK74Kpi' (Errcode: 28)
The gem in all of that mess is this bit: (Errcode: 28). Now, MySQL includes a little perror utility to translate this into something a human can understand:
$ perror 28
OS error code 28: No space left on device
That’s the key to understanding what really happened. MySQL created a temporary table, using the MyISAM storage engine, and when the disk filled up, MyISAM got an error. Little-known secret: MyISAM doesn’t handle a disk-full error gracefully 😉
Sometimes I’ve seen people having trouble with error codes that they don’t believe. Good examples are error 13 (permission denied) or 2 (No such file or directory). The typical reaction is “but it certainly has permission!” or “I can see the file/directory myself, of course it exists!” In cases such as these you need to dig more deeply. The error code is not wrong, I promise you. If it says permission is denied, then permission is denied; there is no bug — that error comes from the operating system. Typical causes are things like SELinux or AppArmor interfering.
So, if you see an error code, try to diagnose with that first. It’s often the easiest route to a solution.
PS: You may wish to get a copy of MySQL Troubleshooting by Sveta Smirnova — I have not read it yet, but I’ve heard really good things about it.