MySQL error handling on server side: a NO GO!

July 18, 2012

There is no reasonable way to catch and diagnose errors on server side. It is nearly impossible to know exactly what went wrong.

To illustrate, consider the following query:

INSERT INTO my_table (my_column) VALUES (300);

What could go wrong with this query?

  • We might hit a UNIQUE KEY violation
  • Or a FOREIGN KEY violation
  • my_column could be TINYINT UNSIGNED, and with strict sql_mode this makes for out-of-range
  • Or, similarly, it could be an ENUM (2,3,5,8)

Is that it? Not remotely:

  • This could be a read-only MyISAM table
  • We may have issued a LOCK TABLES my_table READ -- this violates our lock
  • Or this could be an InnoDB table, and this INSERT would make for a deadlock
  • Or we have read_only=1 configuration
  • Or the user is not allowed access to this table
  • Or the table does not exist
  • Or the column does not exist

Or... I'm pretty sure there could be many other issues.

Now, if I write a Java program, perhaps using Hibernate, I get the error nicely wrapped up in a SQLException object, with easy access to error code and error message.

But can I have the same on server side? No.

Take a look at the following code:

CREATE PROCEDURE some_procedure ()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @error_found = 1;
  INSERT INTO my_table (my_column) VALUES (300);
  IF @error_found THEN -- Any what error exactly is this? What's the message? END IF;
END;

If I wanted to take specific action for specific errors, I would need to:

  DECLARE CONTINUE HANDLER FOR 1146 SET @error_found = 1146;
  DECLARE CONTINUE HANDLER FOR 1147 SET @error_found = 1147;
  DECLARE CONTINUE HANDLER FOR 1148 SET @error_found = 1148;
  DECLARE CONTINUE HANDLER FOR 1149 SET @error_found = 1149;
  ...

But if I can't expect in advance the specific error, yet wish to note it down, that would mean defining hundreds and hundreds of HANDLERs, never being able to actually cover all cases since new codes are introduced in every version, sometimes in minor versions...

Weren't SINGAL and RESIGNAL introduced in 5.5?

They were, but they do nothing to help here. You can RESIGNAL an error - but that doesn't mean you get to be told what the error actually was!

But, what's the problem, anyway?

There's a variety of stuff I would like to do on server side, not via external Python/Perl/Java/Ruby/Shell scripts. Consider the event scheduler: I mean, what's the point? It's nearly useless if there's so much that you cannot do on server side. You cannot recognize errors, you cannot get enough metadata (see below). It's only good for a fraction of the jobs you would like to perform.

In common_schema/QueryScript I provide with scripting capabilities. But how about error handling? I've written a completely different error handling approach inĀ common_schema (this is not released yet, tons of documentation to produce). But since common_schema works on server side, it is limited to whatever server side programming allows. And this, as explained, is really very little to work with.

What would have been nice

There's the error_count session variable. Doesn't actually do anything useful. It would have been nice to have the following session STATUS VARIABLEs:

  • last_error_code
  • last_error_message

And if a query made for multiple errors, pick one (just make both variables consistent).

Or, please, make some way to parse SHOW commands on server side! (also refer to this). If only I could parse the SHOW ERRORS command, that would solve everything!

MySQL 5.0 introduced INFORMATION_SCHEMA, albeit an incomplete one. Shortly after, SHOW commands were excluded from server side cursors. But that left us with so many missing parts. I've opened a bug report/feature request. Would you please support it?

tags: , , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

6 Comments to "MySQL error handling on server side: a NO GO!"

  1. Davi Arnaut wrote:

    GET DIAGNOSTICS?

  2. shlomi wrote:

    Davi,

    Thanks!
    But also: such brief comment makes it appear as if there is such a feature.
    It's great to learn that this feature is available in 5.6. But it's also important to know that 5.6 is not GA at this stage. It will take years to come before 5.5 and 5.1 are gone from production systems. It would be great to have such issues resolved in those versions as well. Ok, 5.1 is stretching it, I suppose.

    I'm very happy with many new 5.6 features. Many of these are Metadata related. It will take time before I even catch up with all the new featureset. Thanks for noting down this new one!

  3. Roland Bouman wrote:

    http://bugs.mysql.com/bug.php?id=11660,
    "Expose either SQLState, mysql_error() or other diagnostics in stored procedures", 30 Jun 2005 11:53, status: verified.

    I'm gonna go shopping, ttl :p

  4. shlomi wrote:

    @Roland,

    Wow. Here are a couple comments on that report, that are of interest. Just to put one in the scale of time.

    -----------------------------------
    [19 Oct 2006 18:53] Marc Grue

    Urgently needing this feature! Hope this feature will make it into 5.2!!

    Aside from the suggested @@SQLSTATE and @@SQLSTATE_MESSAGE, another global @@MYSQLERROR (the MySql error number) would be highly appreciated with it's more detailed mysql-specific error mapping.

    -----------------------------------
    [14 Mar 2007 9:11] Peter Schwager

    This is by far the most SERIOUS weak point of MYSQL from my point of view. We have written complex SP and are not able to proberly track exceptions due to this bug.

  5. Log Buffer #278, A Carnival of the Vanities for DBAs | The Pythian Blog wrote:

    [...] is no reasonable way to catch and diagnose errors on server side. It is nearly impossible to know exactly what went [...]

  6. Things that can’t (and some that can) be done from within a MySQL stored routine | code.openark.org wrote:

    [...] MySQL error handling on server side: a NO GO! [...]

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org