MySQL Forums
Forum List  »  Stored Procedures

Why doesn't mysql -uroot -p <db> -e "commands" not work when I define a delimiter?
Posted by: Cesar Murilo Cesar
Date: December 17, 2019 02:12PM

The following command works:

mysql -uroot -p radius -e "DROP PROCEDURE IF EXISTS sp_monthly_table_backup; ";

The same command with delimiter does not work:

mysql -uroot -p radius -e "DELIMITER $$ DROP PROCEDURE IF EXISTS sp_monthly_table_backup; END $$ DELIMITER;";

This is just part of a command block I want to make, complete would be like this:

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_monthly_table_backup $$
CREATE PROCEDURE sp_monthly_table_backup(OUT Msg varchar(100))
sp: BEGIN
DECLARE radacct_pmonth varchar(30);
DECLARE radpostauth_pmonth varchar(30);
DECLARE excessao SMALLINT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET excessao = 1;


START TRANSACTION;
SELECT CONCAT('radacct_',YEAR(NOW()), MONTH(NOW())-1) INTO @radacct_pmonth;
SET @str=CONCAT("CREATE TABLE IF NOT EXISTS radius_table_bkp.",@radacct_pmonth," SELECT * FROM radius.radacct;");
PREPARE stmt FROM @str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

IF excessao = 1 THEN
SELECT 'FAILED: Erro ao criar copia da tabela radacct' As Msg;
ROLLBACK;
LEAVE sp;
ELSE
DELETE FROM radius.radacct WHERE acctstoptime < date_sub(NOW(), INTERVAL 1 MONTH);
IF excessao = 1 THEN
SELECT 'FAILED: Erro ao remover registros com mais de um mes de radacct' As Msg;
ROLLBACK;
LEAVE sp;
ELSE
SELECT CONCAT('radpostauth_',YEAR(NOW()), MONTH(NOW())-1) INTO @radpostauth_pmonth;
SET @str2=CONCAT("CREATE TABLE IF NOT EXISTS radius_table_bkp.",@radpostauth_pmonth," SELECT * FROM radius.radpostauth;");
PREPARE stmt2 FROM @str2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

IF excessao = 1 THEN
SELECT 'FAILED: Erro ao criar copia da tabela radpostauth' As Msg;
ROLLBACK;
LEAVE sp;
ELSE
DELETE FROM radius.radpostauth WHERE authdate < date_sub(NOW(), INTERVAL 1 MONTH);
IF excessao = 1 THEN
SELECT 'FAILED: Erro ao remover registros com mais de um mes de radauthpost' As Msg;
ROLLBACK;
LEAVE sp;
ELSE
SELECT 'SUCCESS: Rotina executada com sucesso' As Msg;
COMMIT;
END IF;
END IF;
END IF;
END IF;
END $$
DELIMITER ;

Only I need to enter these commands in a shell script, so I need to use mysql -u <user> -p <db> -e "<commands>". I'm also using '/' to continue the commands on the bottom lines, like this:

mysql -uroot -p radius -e "DROP PROCEDURE IF EXISTS sp_monthly_table_backup; ";
CREATE PROCEDURE sp_monthly_table_backup(IN databasebkp varchar(30), IN table varchar(30), OUT Msg varchar(100)) \
sp: BEGIN \
DECLARE table VARCHAR(30); \
DECLARE dbbkp varchar(30); \
DECLARE excessao SMALLINT DEFAULT 0; \
(...)

Options: ReplyQuote


Subject
Views
Written By
Posted
Why doesn't mysql -uroot -p <db> -e "commands" not work when I define a delimiter?
961
December 17, 2019 02:12PM


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.