mysql Transaction in the commit block

Question

CREATE DEFINER=`root`@`localhost` PROCEDURE `PrcCopyQuestion_Admin`(in Param1,in Param2 varchar(45))
BEGIN

 DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
      ROLLBACK;

Select 'Fail' as 'Status' ;      

  END; 

   DECLARE EXIT HANDLER FOR sqlwarning
  BEGIN
      ROLLBACK;

 Select 'Fail' as 'Status' ;         

  END; 

Start transaction;

   Insert statement 1; 

   Insert statement 2; 


   SELECT 'Success' AS 'Status'; 

call PrcGetQuestionAndOption_Admin(@variable); 

 Commit;

END

I am using Mysql 5.7. When in the commit block if the second (Insert statement 2) fails. It will go in the Rollback part and gives me output as 'Failed'. But when i am getting the output it still executes the Select 'Success' as Status in commit block. So my question is when the second insert statement fails. It should go directly in rollback and give me status as fail. It should not execute the status as 'success' in commit block.

Eg: On rollback I am getting two result set:

Select 'Fail'..1st result set

Select 'Success'....2nd result set

I need output as only

Select 'fail'

Any help appreciated!!

I spot no error here. Can you provide sample data and real statements so we can try to reproduce it? Or you try it yourself on http://sqlfiddle.com — fancyPants, Jun 06 '16 at 10:22
Sir!. I just need On roll back it should execute as 'fails' as status. But i am getting two result sets.The one in the Commit block .'success' is also executing in second result set. Or in transaction(Commit block) we don't have to use select statement??? — umang rathod, Jun 06 '16 at 10:32
Sir!.. I understood that. As I said, I can see no error here. Compare for example with this: http://stackoverflow.com/questions/9974325/mysql-transaction-within-a-stored-procedure or the manual: https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html So please, provide real data so we can see if we can reproduce this problem. — fancyPants, Jun 06 '16 at 10:53
Yes the exception arise when i am calling SP inside SP i.e. call PrcGetQuestionAndOption_Admin(@variable); and when i am getting error in this SP i am getting SQL exception. The two insert statement are running fine. Also i just wanna know is there any sql exception like RECORD NOT FIND in transaction-commit block like same in cursor how we handle. If we dont find any record in the transaction-commit block .It should throw me exception like same as sql exception and sql warning — umang rathod, Jun 07 '16 at 04:31

wchiquito · Answer 1 · 2016-06-06T12:13:51.647

I can't reproduce the problem.

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.12    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS  `t1`, `t2`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `t1` (
    ->   `c0` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `t2` (
    ->   `c0` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> DROP PROCEDURE IF EXISTS `PrcCopyQuestion_Admin`//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `PrcCopyQuestion_Admin`(Param1 INT, Param2 VARCHAR(45))
    -> BEGIN
    ->   DECLARE EXIT HANDLER FOR SQLEXCEPTION
    ->     BEGIN
    ->       ROLLBACK;
    ->       SELECT 'Fail' Status;
    ->     END; 
    ->   DECLARE EXIT HANDLER FOR SQLWARNING
    ->     BEGIN
    ->       ROLLBACK;
    ->       SELECT 'Fail' Status;
    ->   END;
    ->   START TRANSACTION;
    ->   INSERT INTO `t1` (`c0`) VALUES (0);
    ->   IF Param1 = 0 THEN
    ->     INSERT INTO `ERR_t2` (`c0`) VALUES (0);
    ->   ELSE
    ->     INSERT INTO `t2` (`c0`) VALUES (0);
    ->   END IF;
    ->   SELECT 'Success' Status;
    ->   COMMIT;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `PrcCopyQuestion_Admin`(0, NULL);
+--------+
| Status |
+--------+
| Fail   |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   `c0`
    -> FROM
    ->   `t1`;
Empty set (0.00 sec)

mysql> SELECT
    ->   `c0`
    -> FROM
    ->   `t2`;
Empty set (0.00 sec)

mysql> CALL `PrcCopyQuestion_Admin`(1, NULL);
+---------+
| Status  |
+---------+
| Success |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   `c0`
    -> FROM
    ->   `t1`;
+----+
| c0 |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> SELECT
    ->   `c0`
    -> FROM
    ->   `t2`;
+----+
| c0 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql Transaction in the commit block

1 Answers1