Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: Is it possible to shorten error messages?

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.

like image 742
Bet Lamed Avatar asked Apr 23 '14 14:04

Bet Lamed


2 Answers

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:

MySQL Workbench long error message

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)

Hex editing MySQL errmsg.sys

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

  1. Save the file
  2. 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:

MySQL Workbench short error message

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

like image 150
pbarney Avatar answered Sep 30 '22 11:09

pbarney


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 enter image description here

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. enter image description here

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)

enter image description here

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

like image 42
Ravi Dhoriya ツ Avatar answered Sep 30 '22 12:09

Ravi Dhoriya ツ