MySQL: Is it possible to shorten error messages?

Question

I have heard a lot of people complain about this, and justifiably so. Many MySQL error messages are ridiculously long:

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

This gets especially annoying in environments that only show you the first half of that string. So the question is: Is it possible to get a shorter version of that string? Something like: Syntax error near... - which is really the juicy part of that message.

Lanate

So less is more? Most people don't read them anyway, they just post immediately on SO ;) – Scarify

I agree. "Syntax error near..." would be fine – Chromatid

@Strawberry: It would at least make some of the question titles shorter! – Scarify

@Log1c: And you're telling me why? – Scarify

@TheBlueDog, I thought you -1 the question. So just a small comment. Don't mind it :) – Blocked

@Log1c: No, wasn't me. – Scarify

Seriously mysql, what's so hard about "Syntax error at on line 2: ..." – Breeden

Answer 1

Note:The steps provided here are only for Linux, you might be using some other OS then use respective editor and commands

MySQL stores error message file at /usr/share/mysql/english/errmsg.sys where english is the language you want to use.

Note:You need to have super user privileges

Step 1. Take backup of existing errmsg.sys (so that you can revert if some problem occured

  $sudo cp /usr/share/mysql/english/errmsg.sys ~/errmsg.sys.bkp

Step 2. Open /usr/share/mysql/english/errmsg.sys in vi editor.

$sudo vi /usr/share/mysql/english/errmsg.sys

Step 3. Search for "You have an" in errmsg.sys

in vi editor for searching try this way-->  /You have an [press enter]

It will get you to the string "You have an error...." as show in screen-shot

Step 4. Edit that error message as per your need. I've deleted string You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the and kept just right syntax to use

Check below screen-shot.

Step 5. Save and Exit.

in vi editor to save and exit-->   :x! [press enter]     here ! is added to override read-only file

Step 6. Restart mysql service.

$sudo mysql restart

step 7. check error message (I'm checking in phpMyAdmin)

In this answer I've updated error message 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... similarly you can update other standard error message as well.

Hope it helped ! :D

Appose

Thanks, I will definitely try this one on my private machine. Not sure if the admins would approve of hacking this into the server though... – Lanate

When I tried these steps on FreeBSD, I got this error and was unable to save/quit: Error: /usr/local/share/mysql/english/errmsg.sys: Illegal byte sequence; /usr/local/share/mysql/english/errmsg.sys: WARNING: FILE TRUNCATED. But I was able to download the file, edit it with vi on my Mac as per the instructions above and then re-upload it and copy into place. – Desertion

Actually, I spoke too soon. Editing that file broke all kinds of MySQL stuff on FreeBSD. I restored my backup and would not recommend editing that file (at least on FreeBSD). – Desertion

Didn't work for me, it broke my error message if I changed anything. Running MySql 5.7 on WAMP. – Breeden

Answer 2

A Solution for Windows:

Is this a problem?

MySQL syntax error messages are prepended with a long string of text that is essentially useless:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use"

In fact, it is so long that it's difficult to use in MySQL's Workbench program because the errors display on a single line:

As you can see, the actual meat of the message is somewhere to the right, unavailable to us. The only way to get the whole error is to left click the message to select it, then right-click on it and choose "Copy Response", and then paste it somewhere to see the full message. This is widely considered to be a bad user experience.

So yes, it is a problem.

How do we fix it? Well, there is a right way to do it (modify the source and rebuild, see instructions here), and there is a fast and quick way, which I only recommend for development machines, because while there probably aren't any side effects, who really knows?

Here's the fast and quick way:

This is a solution for Windows only. Something similar may work on Mac, but I have no idea.

You'll need a hex editor. A free one like HxD or an open source option like Frhed will work.

Follow these steps:

1. In your MySQL installation directory, first make a backup of the file share\english\errmsg.sys just in case.
2. In the hex editor, open the errmsg.sys file.
3. Search for the string "You have an error"
4. In the right-hand side of the hex editor, start at the first letter of the message, and type over the existing text. I replaced my text with a simple "Syntax error"
5. Immediately after your new message, add a hex 00 character by switching back to the left-hand side of the hex editor and entering the 00 there. That ASCII NULL character tells MySQL that the message is complete. You can just ignore everything after that or replace it with spaces if you need to be that obsessive. (I was).

Here is what it should look like: (highlighted for your convenience)

In my editor, the NULL character displays on the right as a period. Don't let that confuse you.

6. Save the file
7. Restart the MySQL service

At this point, your syntax error messages will be much shorter. The only catch is, for this error, you cannot remove the word "near" after your message. So in our case, the message will now be "Syntax error near" etc.

Here's what it should look like if you've done everything right:

This is original work, posted here for the first time. Hope this helps!

Transistor