EmergencyEMERGENCY? Get 24/7 Help Now!

How to diagnose errors in the MySQL error log

 | March 26, 2012 |  Posted In: Insight for DBAs, Percona Software

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST
Baron Schwartz

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

5 Comments

  • 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?

  • 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

  • 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?

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.