MySQL Bugs: #36424: Rollback on all data changes!

Bug #36424	Rollback on all data changes!
Submitted:	30 Apr 2008 11:26	Modified:	2 May 2008 15:55
Reporter:	R Roberts	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.2.1	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Hi 

I am in need of some assistance please. 

I have created a stored procedure, and I have put DECLARE EXIT HANDLER FOR SQLWARNING,SQLEXCEPTION and ROLLBACK. I have also put START TRANSACTION and COMMIT. 

I have a WHILE DO loop with the INSERT statements. 

I found it inserts the first record and bombs out on the second record. But it never does a complete ROLLBACK to removing the first record as well. 

I am using Innodb engine and SQLYog Community Enterprise v6.16.

I have tried various way, but I am not sure if this is a bug or some programmer error.

Any ideas or suggestion where I am going wrong on this? 

Please help... 

Thanks

How to repeat:
DECLARE EXIT HANDLER FOR SQLWARNING,SQLEXCEPTION and ROLLBACK
ROLLBACK
START TRANSACTION
WHILE DO loop with the INSERT statements
COMMIT

Thank you for the report.

> I am in need of some assistance please. 

Please note bugs database is for reporting bugs in MySQL code, note for bugs in user code. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

But anyway please provide full definition of the stored procedure. Also, please, indicate accurate version of MySQL server you use.

Hi

I thought it may be bug cos I have tried endless approaches to this.

This is my stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_AddRecord`$$

CREATE DEFINER=`root`@`IPADDRESS` PROCEDURE `sp_AddRecord`()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
BEGIN 
ROLLBACK;
SET @ErrorMsg = CONCAT('SQL ERROR IN sp_AddRecord'); 
SELECT @ErrorMsg INTO p_sErrorMsg;
END;
START TRANSACTION;
/*Create a record*/
WHILE (UOMCount < 7) DO
IF (UOMCount = 1) THEN
  INSERT INTO tableX(firstname,lastname) VALUES ("blah","job", "new");
ELSE
  INSERT INTO tableX(firstname,lastname) VALUES ("blah","job", "old");
END IF;
END WHILE;

COMMIT;
END$$

DELIMITER ;

I am using MySql 5.2.1 and SQLYogEnterprise Community V6.16.

I have tried using support forum, but there seems to be a lack of response or sporadic feedback.

So any help will be most appreciated.

Thanks

hello .. i am a little interested here as I am from Webyog and you use our program ..  Still I think this is not a client issue .. 

I see some problems with your code (missing DECLARES, row counts that do not match, uninitialized variable).  Anyway this simplified example shows different behaviour with and without transactions (not what *really* happens, but what the server *tells*).  So I paste this example if it should help!

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_AddRecord`$$

CREATE PROCEDURE `sp_AddRecord`()
BEGIN
Declare myCount integer; 

/*Create a record*/
set myCount = 1;
WHILE (myCount < 7) DO
IF (myCount = 1) THEN
  INSERT INTO tab1(id,a) VALUES (7,'new');
ELSE
  INSERT INTO tab1(id,a) VALUES (8, 'old');
END IF;
SET myCount = myCount + 1;
END WHILE;

END$$

DELIMITER ;

call `sp_AddRecord`() -- it tells "(1 row(s)affected)" but 6 rows were inserted

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_AddRecord`$$

CREATE PROCEDURE `sp_AddRecord`()
BEGIN
Declare myCount integer; 

START TRANSACTION;
/*Create a record*/
set myCount = 1;
WHILE (myCount < 7) DO
IF (myCount = 1) THEN
  INSERT INTO tab1(id,a) VALUES (7,'new');
ELSE
  INSERT INTO tab1(id,a) VALUES (8, 'old');
END IF;
SET myCount = myCount + 1;
END WHILE;

COMMIT;
END$$

DELIMITER ;

call `sp_AddRecord`() -- it tells "(0 row(s)affected)" but 6 rows were inserted

btw:

I think you should post a SP *without* that HANDLER stuff that works as expected and another SP *with* HANDLERs (and except for that identical with the first one) that does not, if you think that HANDLER is the problem (this is how I understand you).

Hi

I have read your statements.

I am trying to say that in my stored procedure, I have allowed the first record to go through and and also allowed the second record not to go through.
I would have thought the rollback would actually "undo" the first record as well. 
I mean surely the rollback "undo" all the data changes in the stoerd procedure, even if the ones have already been inserted.

That is why I am not if there is a bug or some syntax is missing.

Any help would be appreciated.

Thanks

From your example I am not able to understand this: "I have allowed the first record to go through and and also allowed the second record not to go through." I do not know if Sveta is.

For me your example won't create due to syntax errors (missing DECLARES, columns counts not matching in INSERTS)!

If you write the SP simply like this:

BEGIN
START TRANSACTION;
SET MYCOUNT = 1;
WHILE MYCOUNT < 7 DO
-- inserts
SET MYCOUNT = MYCOUNT + 1;
END WHILE;
ROLLBACK;
END $$

... then everything is rolled back.  

It is not clear to me WHAT and WHERE you think this bug is!  But now Sveta is in charge here, so maybe she has some proposal.

Thank you for the feedback.

R Roberts, if I understood your needs correctly you just should issue ROLLBACK explicitly. Either after stored procedure call or like in following example:

set autocommit=0;

create table t1(id int not null primary key, f1 varchar(10)) engine=innodb;

delimiter |

create procedure p1()
begin
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING rollback;
start transaction;
  INSERT INTO t1 VALUES (7,'new');
  INSERT INTO t1 VALUES (7, 'old');
commit;
end
|

delimiter ;

call p1();

select * from t1;

So I closed report as "not a Bug"