?
Solved

MySQL function/procedure error handling

Posted on 2011-10-24
6
Medium Priority
?
903 Views
Last Modified: 2012-05-12
Hi there,

I need to implement some error handling in MySQL. I come from a .net background so was expecting this be straightforward but it doesn't seem that simple.  Another EE member kinly provided the following links.  

Maybe I am missing something but these seem to assume that I will know what type of error it is that I am going to catch, is there no way to create a function that will run a piece of code regardless of any error being thrown?  My function calls a view and inserts the results into a table, if the view cannot be called for any reason I need to know and trap the error.

Is there any chance one of you MySQL experts could provide an example of a simple function that will catch any errors that are thrown?

MySQL Signal Conditions
http://dev.mysql.com/doc/refman/5.5/en/signal.html

MySQL Declare Handlers
http://dev.mysql.com/doc/refman/5.5/en/declare-handler.html

Thanks,

Let me know if you need any more info
0
Comment
Question by:cp30
  • 4
  • 2
6 Comments
 
LVL 14

Expert Comment

by:Kalpan
ID: 37016897
Please refer the below examples
CREATE PROCEDURE sp_add_location
         (in_location    VARCHAR(30),
          in_address1    VARCHAR(30),
          in_address2    VARCHAR(30),
          zipcode        VARCHAR(10),
          OUT out_status VARCHAR(30))
    MODIFIES SQL DATA
BEGIN
  DECLARE CONTINUE HANDLER FOR 1062
     SET out_status='Duplicate Entry';

  SET out_status='OK';
  INSERT INTO locations
    (location,address1,address2,zipcode)
   VALUES 
 (in_location,in_address1,in_address2,zipcode); END; 

the DECLARE CONTINUE HANDLERstatement tells MySQL that “if you encounter MySQL error 1062 (duplicate entry for key), then continue execution but set the variablep_statusto
'Duplicate Entry'.” 

DELIMITER //

DROP PROCEDURE IF EXISTS decount_test;
CREATE DEFINER = 'root'@'localhost' PROCEDURE decount_test ( p_id bigint )
DETERMINISTIC MODIFIES SQL DATA
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '42000'
    SELECT 'Invoiced barcodes may not have accounting removed.';
  IF (SELECT invoice_id 
       FROM accounted_barcodes
       WHERE id = p_id
    ) THEN
    CALL raise_error;
 END IF;
 DELETE FROM accounted_barcodes WHERE id = p_id;
END //

DELIMITER ;

Output:

call decount_test(123456);
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. |
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. | 
+----------------------------------------------------+

Open in new window

0
 

Author Comment

by:cp30
ID: 37016971
Hi, thanks for examples but it seems that in your examples you specify the type of error your are expecting.  My question is, is there anyway to handle ANY type of error, and also, can you access the error info, i.e. to write to a log so you know what error was thrown?

Thanks
0
 
LVL 14

Expert Comment

by:Kalpan
ID: 37017256
The usual drill is to do as little as possible in your stored procedures. Your PHP program will get errors and exceptions and that's what you wanted to happen. Since Scripting language will get the exceptions, what are you worrying about? Just let them pop out of your stored procedure.

Your Scripting language will have to "alert the user that something has broken".

If you want to somehow "wrap" all your stored procedure in some kind of error logging, do this in Scripting language. Don't mess with over-writing your stored procedures. Don't worry about I/O errors. You have a database to handle that.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:cp30
ID: 37017674
Hi, thanks for your response.  I see where you are coming from, but in this instance the stored proc is being executed on a routine basis by the event scheduler, so there is no interface directly with the application/gui.  OK, I could write a small program to call the SP and then run this every x minutes and then log any errors from the new app but that introduces more complexity to the system having to rely on another scheduling tool to run the new app and developing new program to connect and execute the SP.

Are we saying there's no way to catch or handle an unknown excpetion type? This seems a little poor and I can't believe it's that uncommon a request.

Many thanks
0
 

Accepted Solution

by:
cp30 earned 0 total points
ID: 37018360
Hi, I think I've figured part of the question out myself...

It seems that I can declare a handler for the SQLEXPECTION class that covers all errors and that means I can detect when an error is thrown.  It still doesn't allow me to actually see what the error was, does anyone know if this is possible?

Here's what I'm doing now to call my code that must run even if an error is thrown

-- ADD HANDLER IN CASE OF ERROR
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    call odds.updateFail(
    'an error was detected in sp');

Open in new window


Cheers
0
 

Author Closing Comment

by:cp30
ID: 37254745
Managed to find out way to do it myself while waiting for solution
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 23 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question