MySQL error handling on server side: a NO GO!

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?