September 17, 2014

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.

About Baron Schwartz

Baron is the lead author of High Performance MySQL.
He is a former Percona employee.

Comments

  1. Nils says:

    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?

  2. 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.

  3. Jon Olav Hauglid says:
  4. Great! Thanks for commenting on this.

  5. 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?

Speak Your Mind

*