How to diagnose errors in the MySQL error log

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:

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:

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.

Share this post

Comments (5)

  • Nils Reply

    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?

    March 27, 2012 at 12:12 am
  • Baron Schwartz Reply

    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.

    March 27, 2012 at 6:36 am
  • Jon Olav Hauglid Reply April 3, 2012 at 9:40 am
  • Baron Schwartz Reply

    Great! Thanks for commenting on this.

    April 3, 2012 at 10:02 am
  • Thiet ke website du lich Reply

    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/ –pid-file=/var/lib/mysql/

    How can i fix it?

    May 10, 2014 at 5:56 am

Leave a Reply