MySQL Bugs: #14840: CONTINUE handler problem

Bug #14840	CONTINUE handler problem
Submitted:	10 Nov 2005 16:39	Modified:	21 Nov 2005 16:02
Reporter:	Goran Vranic	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.17-BK, 5.0.15	OS:	Linux (Linux, Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I have problem with CONTINUE type of handler and UPDATE statement in procedure. I'm not sure, but i think that something is not ok. 

How to repeat:
CREATE TABLE stopa_poreza (
    stopa_poreza_tarifa TINYINT(3) UNSIGNED NOT NULL,
    stopa_poreza_naziv VARCHAR(255) NOT NULL,
    stopa_poreza_iznos FLOAT(5,2) UNSIGNED NOT NULL,
    PRIMARY KEY (stopa_poreza_tarifa)
)
ENGINE = InnoDB;

CREATE TABLE operater (
    operater_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    operater_ime VARCHAR(255) NOT NULL,
    operater_prezime VARCHAR(255) NOT NULL,
    operater_lozinka VARCHAR(255) NOT NULL,
    PRIMARY KEY (operater_id)
)
ENGINE = InnoDB;

CREATE TABLE operater_akcija (
    operater_akcija_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    operater_akcija_operater_id SMALLINT(5) UNSIGNED NOT NULL,
    operater_akcija_vrijeme DATETIME NOT NULL,
    operater_akcija_tabela VARCHAR(255) NOT NULL,
    operater_akcija_tip ENUM('insert','delete','update') NOT NULL,
    operater_akcija_napomena VARCHAR(255),
    PRIMARY KEY (operater_akcija_id),
    KEY operater_akcija_operater_id(operater_akcija_operater_id),
    KEY operater_akcija_vrijeme(operater_akcija_vrijeme)
)
ENGINE = InnoDB;

ALTER TABLE operater_akcija
    ADD FOREIGN KEY (operater_akcija_operater_id) REFERENCES operater (operater_id) ON DELETE RESTRICT ON UPDATE CASCADE;

INSERT INTO `operater` VALUES (1,'Marko','Markovic','456');

INSERT INTO `poruka` VALUES (-2,'Message 1');
INSERT INTO `poruka` VALUES (-1,'Message 2');

INSERT INTO `stopa_poreza` VALUES (0,'Tarifa 0',0);
INSERT INTO `stopa_poreza` VALUES (1,'Tarifa 1',20);
INSERT INTO `stopa_poreza` VALUES (2,'Tarifa 2',10);

#stopa_poreza_update procedure (delimiter /)#
DROP PROCEDURE IF EXISTS stopa_poreza_update/

CREATE PROCEDURE stopa_poreza_update (IN stara_tarifa TINYINT(3) UNSIGNED, IN nova_tarifa TINYINT(3) UNSIGNED, IN naziv VARCHAR(255), IN iznos FLOAT(5,2) UNSIGNED, IN operater_id SMALLINT(5) UNSIGNED, OUT ok TINYINT, OUT tekst_poruke VARCHAR(255)) 
DETERMINISTIC 
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT 'procedura za izmjenu podataka u tabeli stopa_poreza'
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ok=-1;
    
  START TRANSACTION;
    SET ok=1;

    UPDATE stopa_poreza SET stopa_poreza_tarifa=nova_tarifa, stopa_poreza_naziv=naziv, stopa_poreza_iznos=iznos WHERE stopa_poreza_tarifa=stara_tarifa;     

    INSERT INTO operater_akcija VALUES (NULL, operater_id, NOW(), 'stopa_poreza_insert', 'update', CONCAT('stari broj tarife:',stara_tarifa,' novi broj tarife:',nova_tarifa));

  IF ok=1 THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF; 

  SELECT poruka_tekst INTO tekst_poruke FROM poruka WHERE poruka_id=ok;

END/

After execution above procedure

CALL stopa_poreza_update(2,1,'Tarifa 2',10.00,1,@ok,@poruka);

client stops responding. When i declare EXIT type of handler everything is ok.

Thank you for a problem report. Verified just as described on 5.0.17-BK (ChangeSet@1.1957, 2005-11-09 20:31:01+03:00, ...) on Linux. Client hangs.

I am not sure why not to rollback the transaction right in the handler, by the way. The hang is due to transaction not ending in your procedure (as one can see at the show innodb status results from the other session). By the way, the idea of using transaction control statements in the SP is not good in any case. You should return the status to caller to decide is it OF to COMMIT or ROLLBACK.

Removing the inserts to, and the select from, the table 'poruka' fixes the test case so it can be repeated.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32319

For the record: It's ok to do transactions in a procedure.
(The error here was actually due to a bug with the error status which caused the procedure to exit unexpectedly after the "insert". It's the client that's hanging, not
the server.)

But it's unusual to use a CONTINUE handler during a transaction. You usually do
not want to continue if one of the statements fails. To avoid having to check a flag
before each statement, use an EXIT handler instead:

BEGIN
  START TRANSACTION;
  BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

    UPDATE stopa_poreza ...;
    INSERT INTO operater_akcija ...;

    COMMIT;
  END;
  -- Will continue here...
END

Patch voided by a more general fix.

This is a duplicate of BUG#13729.