{"id":5082,"date":"2012-07-18T06:45:15","date_gmt":"2012-07-18T04:45:15","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5082"},"modified":"2012-07-18T06:45:15","modified_gmt":"2012-07-18T04:45:15","slug":"mysql-error-handling-on-server-side-a-no-go","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mysql-error-handling-on-server-side-a-no-go","title":{"rendered":"MySQL error handling on server side: a NO GO!"},"content":{"rendered":"<p>There is no reasonable way to catch and diagnose errors on server side. It is nearly impossible to know exactly <em>what went wrong<\/em>.<\/p>\n<p>To illustrate, consider the following query:<\/p>\n<blockquote>\n<pre>INSERT INTO my_table (my_column) VALUES (300);<\/pre>\n<\/blockquote>\n<p>What could go wrong with this query?<\/p>\n<ul>\n<li>We might hit a <strong>UNIQUE KEY<\/strong> violation<\/li>\n<li>Or a <strong>FOREIGN KEY<\/strong> violation<\/li>\n<li>my_column could be <strong>TINYINT UNSIGNED<\/strong>, and with strict <strong>sql_mode<\/strong> this makes for out-of-range<\/li>\n<li>Or, similarly, it could be an <strong>ENUM (2,3,5,8)<\/strong><\/li>\n<\/ul>\n<p>Is that it? Not remotely:<\/p>\n<ul>\n<li>This could be a read-only <strong>MyISAM<\/strong> table<\/li>\n<li>We may have issued a <strong>LOCK TABLES my_table READ<\/strong> &#8212; this violates our lock<\/li>\n<li>Or this could be an <strong>InnoDB<\/strong> table, and this <strong>INSERT<\/strong> would make for a deadlock<\/li>\n<li>Or we have <strong>read_only=1<\/strong> configuration<\/li>\n<li>Or the user is not allowed access to this table<\/li>\n<li>Or the table does not exist<\/li>\n<li>Or the column does not exist<\/li>\n<\/ul>\n<p>Or&#8230; I&#8217;m pretty sure there could be many other issues.<\/p>\n<p>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.<\/p>\n<p>But can I have the same on server side? <em>No<\/em>.<\/p>\n<p>Take a look at the following code:<!--more--><\/p>\n<blockquote>\n<pre>CREATE PROCEDURE some_procedure ()\r\nBEGIN\r\n  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @error_found = 1;\r\n  INSERT INTO my_table (my_column) VALUES (300);\r\n  <strong>IF @error_found THEN -- Any what error exactly is this? What's the message? END IF;<\/strong>\r\nEND;<\/pre>\n<\/blockquote>\n<p>If I wanted to take specific action for specific errors, I would need to:<\/p>\n<blockquote>\n<pre>  DECLARE CONTINUE HANDLER FOR 1146 SET @error_found = 1146;\r\n  DECLARE CONTINUE HANDLER FOR 1147 SET @error_found = 1147;\r\n  DECLARE CONTINUE HANDLER FOR 1148 SET @error_found = 1148;\r\n  DECLARE CONTINUE HANDLER FOR 1149 SET @error_found = 1149;\r\n  ...<\/pre>\n<\/blockquote>\n<p>But if I can&#8217;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&#8230;<\/p>\n<h4>Weren&#8217;t SINGAL and RESIGNAL introduced in 5.5?<\/h4>\n<p>They were, but they do nothing to help here. You can <strong>RESIGNAL<\/strong> an error &#8211; but that doesn&#8217;t mean you get to be told what the error actually was!<\/p>\n<h4>But, what&#8217;s the problem, anyway?<\/h4>\n<p>There&#8217;s a variety of stuff I would like to do on server side, not via external Python\/Perl\/Java\/Ruby\/Shell scripts. Consider the <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/events.html\">event scheduler<\/a>: I mean, what&#8217;s the point? It&#8217;s nearly useless if there&#8217;s so much that you cannot do on server side. You cannot recognize errors, you cannot get enough metadata (see below). It&#8217;s only good for a fraction of the jobs you would like to perform.<\/p>\n<p>In <a href=\"http:\/\/code.google.com\/p\/common-schema\/\">common_schema\/QueryScript<\/a> I provide with scripting capabilities. But how about error handling? I&#8217;ve written a completely different error handling approach in\u00a0<em>common_schema<\/em> (this is not released yet, tons of documentation to produce). But since <em>common_schema<\/em> works on server side, it is limited to whatever server side programming allows. And this, as explained, is really very little to work with.<\/p>\n<h4>What would have been nice<\/h4>\n<p>There&#8217;s the <strong>error_count<\/strong> session variable. Doesn&#8217;t actually do anything useful. It would have been nice to have the following session STATUS VARIABLEs:<\/p>\n<ul>\n<li><strong>last_error_code<\/strong><\/li>\n<li><strong>last_error_message<\/strong><\/li>\n<\/ul>\n<p>And if a query made for multiple errors, pick one (just make both variables consistent).<\/p>\n<p>Or, <em>please<\/em>, make some way to parse <strong>SHOW<\/strong> commands on server side! (also refer to <a href=\"http:\/\/code.openark.org\/blog\/mysql\/reading-results-of-show-statements-on-server-side\">this<\/a>). If only I could parse the <strong>SHOW ERRORS<\/strong> command, that would solve everything!<\/p>\n<p>MySQL <strong>5.0<\/strong> introduced <strong>INFORMATION_SCHEMA<\/strong>, albeit an incomplete one. Shortly after, <strong>SHOW<\/strong> commands were excluded from server side cursors. But that left us with so many missing parts. I&#8217;ve opened a <a href=\"http:\/\/bugs.mysql.com\/bug.php?id=65897\">bug report\/feature request<\/a>. Would you please support it?<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[67,83,84,59],"class_list":["post-5082","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-errors","tag-information","tag-stored-routines"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1jY","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5082","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=5082"}],"version-history":[{"count":16,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5082\/revisions"}],"predecessor-version":[{"id":5100,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5082\/revisions\/5100"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=5082"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=5082"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=5082"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}