如何在MySQL存储过程中检测回滚？

Question

我想找到一种方法来检测MySQL存储过程中回滚的情况，以便我可以从PHP脚本中相应地处理该情况，但到目前为止我找不到任何解决方案。

我的存储过程如下所示：

     delimiter |
      create procedure multi_inserts(
      IN var1 int(11),
           .
           .
           .
      IN string1 text
      )
      BEGIN

      declare exit handler for sqlexception rollback;
      declare exit handler for sqlwarning rollback;

      START TRANSACTION;
      insert into table1(a,b,c,d) values(var1,var2,var3,var4);
      insert into table2(e,f,g) values(var5,var6,string1);
      COMMIT;

      END
      delimiter ;

我对这个存储过程进行了回滚测试，虽然它确实回滚了，但我没有得到错误提示。 我希望我的存储过程能够在事务失败时抛出某种错误信息，这样我就可以像这样处理：

    $result = mysql_query($procedure); 
    if(!$result) 
    {
      //rollback occured do something   
    }

有没有办法在MySQL中检测回滚？我有什么遗漏吗？感谢您的阅读，期待您的回复。

---

感谢您的建议，我已解决了这个问题。以下是我的解决方法：

存储过程

     delimiter |
      create procedure multi_inserts(
      IN var1 int(11),
           .
           .
           .
      IN string1 text
      )
      BEGIN

      declare exit handler for sqlexception sqlwarning
      BEGIN
      rollback;
      select -1;
      END;

      START TRANSACTION;
      insert into table1(a,b,c,d) values(var1,var2,var3,var4);
      insert into table2(e,f,g) values(var5,var6,string1);
      COMMIT;

      END
      delimiter ;

如果我使用输出变量而不是选择-1，会出现以下错误：

OUT或INOUT参数在BEFORE触发器中不是变量或NEW伪变量

我不知道我做错了什么，但我无法解决这个问题。

PHP脚本

$result=mysqli_query($con,$procedure);
if(is_object($result))
{
//rollback happened do something!
}

如果SP成功，它会返回true。

- Kou

Answer 1

您可以添加一个输出参数，并在退出处理程序中将其设置为所需的值。

以下是使用您的过程的示例：

delimiter $$
  create procedure multi_inserts(
  IN var1 int(11),
       .
       .
       .
  IN string1 text,
  OUT p_return_code tinyint unsigned
  )
  BEGIN

  DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
    set p_return_code = 1;
    rollback;
  END;

  DECLARE exit handler for sqlwarning
  BEGIN
    -- WARNING
    set p_return_code = 2;
    rollback;
  END;

  START TRANSACTION;
  insert into table1(a,b,c,d) values(var1,var2,var3,var4);
  insert into table2(e,f,g) values(var5,var6,string1);
  COMMIT;

  -- SUCCESS
  set p_return_code = 0;

  END $$
  delimiter ;

- Ike Walker

---

感谢您详细的回答。我重新编写了我的SP，在MySQL控制台中运行良好，但是php的mysql_query无法处理结果集，并给出了有趣的错误，因此我更新了PHP到5.3.3以使用mysqli_query。我以为这会解决问题。我太天真了。更新后，我在Pear Auth.php中遇到了另一个错误，我正在使用它作为身份验证，除非我首先解决新的问题，否则我无法确定mysqli_query是否真的可以解决问题。最新的问题是我无法出于某种原因更新pear auth。我想pear auth与5.3.3不兼容。现在该怎么办？抱歉我在抱怨。 - Kou

Answer 2

如果您想捕获错误，通常会在 PHP 端执行此操作。请阅读 http://php.net/manual/en/pdo.transactions.php 以获取更多信息。

- Martin Bean

---

谢谢回复。也许我应该使用PDO。 - Kou

Answer 3

嘿，做一件事，使用OUTPUT变量并从SP返回1或0作为结果，然后在此标志上执行任何操作。

- Singleton

Answer 4

<?php
try {
    $user='root';
    $pass='';
  $dbh = new PDO('mysql:host=localhost;dbname=dbname', $user, $pass, 
      array(PDO::ATTR_PERSISTENT => true));
  echo "Connected\n";
} catch (Exception $e) {
  die("Unable to connect: " . $e->getMessage());
}

try {  
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $dbh->beginTransaction();
  $dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
  $dbh->exec("insert into salarychange (id, amount, changedate) 
      values (23, 50000, NOW())");
  $dbh->commit();

} 
catch (Exception $e) {
  $dbh->rollBack();
  echo "Failed: " . $e->getMessage();
}
?>

- vineesh vijayan

---

please add description - zohar