mysql - 在 MySQL 存储过程的退出处理程序中获取失败语句的 SQL

Question

我正在构建一个执行 10-12 个 SQL 语句的存储过程。我收到错误 1172，结果由多行组成。我正在尝试找出哪个语句负责。

最明显的方法是返回引发错误的语句的 SQL。

这是我的退出处理程序:

  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;

我可以添加什么，以便它告诉我哪个 SQL 语句生成了错误？

作为引用，这里是完整的存储过程:

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;

更新:根本问题在于第一个 SELECT 语句:

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

我假设左侧 parent_id 是来自 item_contains 的，右侧 parent_id 是存储过程参数。我误解了。两者都被解释为存储过程参数。解决方案是为表设置别名并通过别名引用字段，如下所示:

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

但是:原来的问题仍然存在:

我可以在 EXIT HANDLER 中添加一些内容来告诉我存储过程中的何处引发了错误吗？

Answer 1

所有 SELECT ... INTO 语句必须仅返回一行。

例如，以下代码可能返回多于 1 条记录:

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

您可以调试将结果存储在另一个变量中的语句并选择:

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*/

另一种确保只有一条记录的方法(不推荐)是添加限制

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;