How can I see errors from a MySQL stored procedure?

Question

With regards to using MySQL stored procedures with transactions, and I am having a problem getting error output.

The problem is that I need to set an exit_handler to roll back the transaction if anything fails. But when I do this, I don't get any error output if something goes wrong. For example if I accidentally pass a NULL value and try to insert it into a non-null field.

I am using a return value to programmatically indicate success or failure, however this does nothing to tell me what actually went wrong.

I am using Perl DBI to talk to MySQL. I am using MySQL 5.0.92 on the production server and MySQL 5.0.51a on the development server. Upgrading to a newer version of MySQL is politically untenable.

This is a simplified example:

DELIMITER //
CREATE PROCEDURE pmt_new(

  app_id            varchar(40),

  out ret           tinyint unsigned,
  out pmt_req_id    int(10) unsigned)
BEGIN

DECLARE v_pmt_req_type int(10) unsigned;

DECLARE exit handler for not found, sqlwarning, sqlexception rollback;

set ret=1;
START TRANSACTION;

  SELECT pmt_type INTO v_pmt_req_type FROM pmt_req_types WHERE pmt_req_name = 'Name 1';

  INSERT INTO pmt_reqs (pmt_req_id,    pmt_req_type,   app_id)
  values               (null,          v_pmt_req_type, app_id);

  set pmt_req_id = last_insert_id();

  INSERT INTO other (pmt_req_id) values (pmt_req_id);

COMMIT;
set ret=0;
END//  
DELIMITER ;

Nodlehs · Accepted Answer · 2011-10-20 03:13:50Z

2

Instead of just doing a rollback in your exit handler you need to return something as well...

You currently have

DECLARE exit handler for not found, sqlwarning, sqlexception rollback;

Change it to something like...

DECLARE exit handler for not found, sqlwarning, sqlexception
  begin
    rollback;
    select "We had to rollback, error!";
  end;

In 5.5 they added the SIGNAL/RESIGNAL statements so you could 'return' an error but prior versions you have to kind of roll your own solution. If you need you can declare multiple exit handlers to tailor the output better, or setup your own error table you can pull from.

You can also do input testing inside your stored procedure. Want to know if app_id is null?

DELIMITER //
CREATE PROCEDURE pmt_new(

  app_id            varchar(40),
  out result        varchar(256),
  out ret           tinyint unsigned,
  out pmt_req_id    int(10) unsigned)
BEGIN

  DECLARE v_pmt_req_type int(10) unsigned;

  DECLARE exit handler for not found, sqlwarning, sqlexception rollback;

  SET ret=1;
  SET result = "";

  IF app_id IS NULL THEN
    set result = "Supplied ID is Null";
  ELSE

    START TRANSACTION;

      SELECT pmt_type INTO v_pmt_req_type FROM pmt_req_types WHERE pmt_req_name = 'Name 1';

      INSERT INTO pmt_reqs (pmt_req_id,    pmt_req_type,   app_id)
      values               (null,          v_pmt_req_type, app_id);

      set pmt_req_id = last_insert_id();

      INSERT INTO other (pmt_req_id) values (pmt_req_id);

    COMMIT;
    set ret=0;
  END IF;
END//  
DELIMITER ;

Doing it this way adds another out parameter, but gives you much better information. You could do the same with multiple exit handlers.

edited Oct 20, 2011 at 3:13

answered Oct 18, 2011 at 0:31

Nodlehs

1014 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nodlehs Over a year ago

@NXT You are simply using 'ret' as a true/false, you aren't using it to provide yourself with the information you require. (As in, more detailed failure information). My answer shows you a way to do what you want. I will edit my answer with more information that you may want to try out instead.

Collectives™ on Stack Overflow

How can I see errors from a MySQL stored procedure?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related