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:
1 2 3 4 | 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:
1 2 | $ 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.
Is there any good reason why you need to use perror first instead of MySQL writing the error message into the log along with the error code?
Maybe, maybe not, I don’t know. But I’m not trying to point out how the server ought to work. I’m explaining how to work with the server as it is.
This feature is implemented and will be in version 5.6.
http://bugs.mysql.com/bug.php?id=48348
http://dev.mysql.com/doc/refman/5.6/en/news-5-6-4.html
Great! Thanks for commenting on this.
After i check by command ps -ef | grep mysqld
mysql 1590 1477 0 16:47 ? 00:00:00 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=/var/lib/mysql/srv111.tamnq.com.err –pid-file=/var/lib/mysql/srv111.tamnq.com.pid
How can i fix it?