Get SQL of failed statement inside exit handler of MySQL stored procedure

Question

I am building a stored procedure that executes 10-12 SQL statements. I'm getting an error 1172, Result consisted of more than one row. I'm trying to figure out which statement is responsible.

The most obvious way is to return the SQL of the statement that raised the error.

Here is my exit handler:

  DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  BEGIN
    GET DIAGNOSTICS @n = NUMBER, @c = ROW_COUNT;
    GET DIAGNOSTICS CONDITION @n
      @s = RETURNED_SQLSTATE,
      @m = MESSAGE_TEXT,
      @e = MYSQL_ERRNO;
    ROLLBACK;
    SELECT @s as RETURNED_SQLSTATE, @e as MYSQL_ERRNO, @m as MESSAGE_TEXT, @n as NUMBER, @c as ROW_COUNT;
  END;

What can I add so that it will tell me which of my SQL statements generated the error?

For reference, here is the full stored procedure:

DROP PROCEDURE IF EXISTS `unlink_item`;
CREATE PROCEDURE `unlink_item` ( 
  IN item_id         varchar(36), 
  IN parent_id       varchar(36),
  IN author          varchar(64),
  IN conversation_id varchar(36),
  IN transaction_id  varchar(36)
)
BEGIN

  DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  BEGIN
    GET DIAGNOSTICS @n = NUMBER, @c = ROW_COUNT;
    GET DIAGNOSTICS CONDITION @n
      @s = RETURNED_SQLSTATE,
      @m = MESSAGE_TEXT,
      @e = MYSQL_ERRNO;
    ROLLBACK;
    SELECT @s as RETURNED_SQLSTATE, @e as MYSQL_ERRNO, @m as MESSAGE_TEXT, @n as NUMBER, @c as ROW_COUNT;
  END;

  START TRANSACTION;

  SET @when = now();

  -- identify the record we are supposed to delete --
  SET @deleted = '123';
  SELECT  `id` 
    FROM  `item_contains`
    WHERE `parent_id` = parent_id
    AND   `child_id`  = item_id
    INTO @deleted;

  -- delete the record --
  DELETE FROM `item_contains`
  WHERE `id` = @deleted;

  -- update the change history for the item_contains table --
  INSERT INTO `change_history` (
    `date_time`,
    `author`,
    `action`,
    `table_name`,
    `record_id`,
    `conversation_id`,
    `transaction_id`
  ) VALUES (
    @when,
    author,
    'delete',
    'item_contains',
    @deleted,
    conversation_id,
    transaction_id
  );

  -- update the change history for the parent item --
  INSERT INTO `change_history` (
    `date_time`,
    `author`,
    `action`,
    `table_name`,
    `record_id`,
    `conversation_id`,
    `transaction_id`
  ) VALUES (
    @when,
    author,
    concat('unlink from ',parent_id),
    'item',
    item_id,
    conversation_id,
    transaction_id
  );

  -- update the change history for the item being unlinked --
  INSERT INTO `change_history` (
    `date_time`,
    `author`,
    `action`,
    `table_name`,
    `record_id`,
    `conversation_id`,
    `transaction_id`
  ) VALUES (
    @when,
    author,
    concat('unlink ',item_id, ' from this'),
    'item',
    parent_id,
    conversation_id,
    transaction_id
  );

  -- if the unlinked item is now orphaned either delete it if it is empty, or re-attach it somewhere to make it accessible --
  SET @instances = 0;

  SELECT count(`id`) 
    FROM `item_contains`
    WHERE `child_id` = item_id
    INTO @instances;

  IF ( @instances = 0 ) THEN

    SET @children = 0;
    SELECT count(`id`) 
      FROM `item_contains`
      WHERE `parent_id` = item_id
      INTO @children;

    IF ( @children = 0 ) THEN

      -- delete the now inaccessible item from the database --
      DELETE FROM `item`
      WHERE `id` = item_id;

      -- update the change history for that item --
      INSERT INTO `change_history` (
        `date_time`,
        `author`,
        `action`,
        `table_name`,
        `record_id`,
        `conversation_id`,
        `transaction_id`
      ) VALUES (
        @when,
        author,
        'delete',
        'item',
        item_id,
        conversation_id,
        transaction_id
      );

    ELSE

      -- get the ID of the orphan folder --
      SET @orphan_id = '123';
      SELECT `id` 
        FROM `item`
        WHERE `name` = '### orphans ###'
        INTO @orphan_id;

      -- move the item into the orphan folder --
      SET @inserted = uuid();
      INSERT INTO `item_contains` (
        `id`,
        `parent_id`,
        `child_id`
      ) VALUES (
        @inserted,
        @orphan_id,
        item_id
      );

      -- update the change history to reflect the orphaning of the item --
      INSERT INTO `change_history` (
        `date_time`,
        `author`,
        `action`,
        `table_name`,
        `record_id`,
        `conversation_id`,
        `transaction_id`
      ) VALUES (
        @when,
        author,
        'orphaned',
        'item',
        item_id,
        conversation_id,
        transaction_id
      );

    END IF;

  END IF;

  SELECT 
    0 AS `RETURNED_SQLSTATE`,
    0 AS `MYSQL_ERRNO`, 
    'Item unlinked' AS `MESSAGE_TEXT`,
    1 as `ROW_COUNT`;

  COMMIT;

END;

UPDATE: The root problem was in the first SELECT statement:

SELECT `id` FROM `item_contains` 
WHERE  `parent_id` = parent_id 
AND    `child_id` = item_id

I had assumed that the left parent_id would be the one from item_contains and the right parent_id would be the stored procedure parameter. I was mistaken. Both are interpreted as the stored procedure parameter. The solution was to alias the table and refer to the fields via the alias, like so:

SELECT ic.id FROM item_contains AS ic
WHERE  ic.parent_id = parent_id
AND    ic.child_id = item_id

HOWEVER: the original question still stands:

Is there something that I can add to my EXIT HANDLER that will tell me WHERE in my stored procedure that the error was raised?

Debugging in stored procedure isn't easy there isn't anny good debugging function(s) available. — Raymond Nijland
– Raymond Nijland, Commented Nov 14, 2017 at 14:35

Ivan Cachicatari · Accepted Answer · 2017-11-14 15:01:17Z

1

All SELECT ... INTO statements must return only one row.

For example, The below code may return more than 1 record:

SELECT  `id` 
  FROM  `item_contains`
  WHERE `parent_id` = parent_id
  AND   `child_id`  = item_id
  INTO @deleted;

You can debug statement storing the result in another variable and select:

SELECT  count(*) 
  FROM  `item_contains`
  WHERE `parent_id` = parent_id
  AND   `child_id`  = item_id
  INTO @supposed_to_delete;

SELECT @supposed_to_delete;  /*you will see the value after execute*/

Another way to make sure only one record (not recommended) is adding a limit

SELECT  `id` 
  FROM  `item_contains`
  WHERE `parent_id` = parent_id
  AND   `child_id`  = item_id
  LIMIT 1   /* <-- force to return only one record */
  INTO @deleted;

answered Nov 14, 2017 at 15:01

Ivan Cachicatari

4,3143 gold badges24 silver badges41 bronze badges

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

4 Comments

John Arrowwood Over a year ago

When I execute the SELECT statement on its own, it works. It only fails in the context of the stored procedure. There is only one record that matches, which is enforced by constraints on the item_contains table, so it can't possibly be returning multiple values.

John Arrowwood Over a year ago

Amazingly enough, though, adding LIMIT 1 seems to work. Apparently it throws that error even if there is only one matching row.

John Arrowwood Over a year ago

Okay, turns out that WHERE 'parent_id' = parent_id was not being interpreted the way I thought it was. The one on the left and the one on the right were both interpreted as the stored procedure parameter.

John Arrowwood Over a year ago

But the original question still stands: Is there a function that I can SELECT in my handler that will return the SQL of the failed statement?

Collectives™ on Stack Overflow

Get SQL of failed statement inside exit handler of MySQL stored procedure

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related