Why is the first command committing?
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