CODEWITHSUNDEEP
mysqlstored-procedures
user3168378
user3168378

Reputation: 31

MySQL 5.6 GET DIAGNOSTICS into log table and ROLLBACK previous DML

Apparently I cannot combine the GET DIAGNOSTICS and ROLLBACK statements inside a stored procedure: besides catching the error, I want to be able to reverse all the previous processed data, not just to stop the execution. Please find bellow my script:

CREATE TABLE tbl_logs (

`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

`txt` VARCHAR(100) NOT NULL,

PRIMARY KEY (`id`));

DELIMITER $$

CREATE PROCEDURE `test`() 

BEGIN 

DECLARE state CHAR(5) DEFAULT ’00000′;

DECLARE msg TEXT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION 

begin 

   rollback;

   GET DIAGNOSTICS CONDITION 1 state = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;

   insert into tbl_logs (txt) select concat(‘Error ‘,state,’: ‘,msg);

 end;

start transaction; 

   insert into tbl_logs (txt) select state;

   – drop table no_such_table;

   insert into tbl_logs (txt) select ‘commit’; 

commit; 

END

call test(); 

select * from tbl_logs;

=> check the table: 2 rows

00000

commit

call test();

select * from tbl_logs;

=> check the table: 2 new rows instead of just one, the last

00000

Error 42S02: Unknown table ‘no_such_table’

=> the handler catches the error and stops the execution, is just that doesn’t consider the rollback (no matter what/where other DML are previously made, they are committed)…

What am I doing wrong?

Upvotes: 3

Views: 1970

Answers (1)

eggyal
eggyal

Reputation: 125835

As documented under DROP TABLE Syntax:

Note 

DROP TABLE automatically commits the current active transaction, unless you use the TEMPORARY keyword.

Upvotes: 0

Related Questions