MySQL Forums
Forum List  »  Stored Procedures

Why is the first command committing?
Posted by: Cesar Murilo Cesar
Date: December 18, 2019 12:35PM

I would like that if delete failed, it would also reverse the creation of the backup table, but this is not happening, why?

/usr/bin/mysql --defaults-extra-file=/etc/my.cnf.d/.mylogin.cnf ${DB} <<EOFMYSQL
DELIMITER $$
USE ${DB} $$
DROP PROCEDURE IF EXISTS sp_monthly_table_backup $$
CREATE PROCEDURE sp_monthly_table_backup(IN tb varchar(30), IN tmclmn varchar(30), OUT Msg varchar(100))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
RESIGNAL;
SELECT 'FAILED: Routine failed' As Msg;
END;

START TRANSACTION;

SELECT CONCAT(tb,'_',YEAR(NOW()), MONTH(NOW())-1) INTO @tb_pmonth;
SET @str=CONCAT('CREATE TABLE IF NOT EXISTS ${DBBKP}.',@tb_pmonth,' SELECT * FROM ',tb,';');
PREPARE stmt FROM @str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @str2=CONCAT('DELETE FROM ',tb,' WHERE ',tmclmn,' < date_sub(NOW(), INTERVAL 1 MONTH)');
PREPARE stmt2 FROM @str2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
SELECT 'SUCCESS: routine successfully executed' As Msg;

COMMIT;
END$$
DELIMITER ;
EOFMYSQL

Options: ReplyQuote


Subject
Views
Written By
Posted
Why is the first command committing?
805
December 18, 2019 12:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.