EmergencyEMERGENCY? Get 24/7 Help Now!

MySQL Error Message Nonsenses


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button

What MySQL honestly was never good at is giving good helpful error messages. Start with basics for example – The error message in case of syntax error gives you information about tokens near by but little details:

It would be much better if MySQL would give error give exact position of error (with complex auto generated queries line number is often not good enough) as well as give some better explanation on what is wrong.

The new parser for MySQL was spoke since 5.0 times but it never took off and I’m not seeing it on public road map either.

Though this is just tip of the iceberg of not so helpful or misleading error messages.

Incorrect information if file table.frm is perhaps my favorite one. How do you expect to decode from this message this means storage engine in question is unavailable ? This is especially strange because it should not be so hard to discover this issue – storage engine which is specified in .frm file is not available and give user friendly error message.

Another great set of errors is Got Error XYZ from storage engine which could mean all sort of things. For example this may be Operating system specific error code to tell you why operation could not be performed (read error, out of file descriptors etc). In fact you can run “perror X” to get the explanation but for some reason it is not done automatically.

Even more – the operating system error are often mixed with MySQL errors and in some cases error code may have two meanings. Though typically these do not overlap:

The same code instead of explanation is often seen in other combinations:

ERROR 1005 at line 20: Can’t create table ‘./test/test.frm’ (errno: 150)

May make you to think you have some kind of strange file system/OS error until you run perror:

What is also interesting is – this error 150 is in fact internal Innodb error code. Other storage engines may or may not use same error codes for same kind of error complicating automatic handling.

When we tested different transactional storage engines we surely saw lock related tables being far from consistent.

Let me give you another example. So Innodb has limit of 1023 open transactions which perform writes. I’m not excited to see there is a limit as such but what is especially annoying is absolutely misleading error messages produced when this limit is reached:

For me table is full means something like I’m out of disk space (or quota set for the table size (think MEMORY)) while in this case it is nothing like that and only going to error logs allows you to figure out what is the problem

I hope with pluggable storage engine interface Sun/MySQL spends some time to make things consistent. There should be general high level error codes which can be portable across storage engines – for example something with meaning of “Storage engine is out of resources” together with ability for storage engine to pass as much information back as needed to investigate and solve the error.

Going back to our foreign keys example it is not only you need to run perror to find out this error is foreign key related but you also need to run SHOW INNODB STATUS to see what exactly Innodb did not like about foreign key constraint definition.

Share Button
PREVIOUS POST
NEXT POST


Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.



Tags:

,

Categories:
Insight for DBAs


Comments

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.