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:
1 2 | mysql> select * from user oder by pwd; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'by pwd' at line 1 |
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:
1 2 3 | [pz@sl1 ~]$ perror 124 OS error code 124: Wrong medium type MySQL error code 124: Wrong index given to function |
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:
1 2 | [pz@sl1 ~]$ perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed |
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:
1 2 | mysql> insert into yt values (66666); ERROR 1114 (HY000): The table 'yt' is full |
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
1 2 | 070223 13:26:01InnoDB: Warning: cannot find a free slot for an undo log. Do you have too InnoDB: many active transactions running concurrently? |
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.
100% agree. The perror program is the classic example of tool that should have never been created because… well,’ it’s bloody obvious! Why some automatic program should tell me a number that I have to put into another program to know what the error actually means? Simply give me the reason!
Well… This tool proved helpful for other programs as well which may also just print OS error code without good explanation. Though perhaps same applies to them – their messages should have been more user friendly.
Strange about the SUN/MySQL thing. . . I’ve always said the Java compiler is as good as the MySQL SQL error reporting is bad. Sure, “Cannot resolve symbol” may seem cryptic here or there but sure as hell more hepful than Error 1054, or whatever.
I’m not holding out any hope, though.
You will also get the error
ERROR 1114 (HY000): The TABLE ‘yt’ IS full
If you try to add an index to a yt table if it is a memory table.
Got error 124 when reading table ‘.//’
I dont have more info but does that mean wrong index was give to some function? function name or other related info would have been more helpful.