Bug #96289 | Stored Procedure Copy Drops Wrong Procedure | ||
---|---|---|---|
Submitted: | 23 Jul 2019 10:59 | Modified: | 26 May 2020 16:58 |
Reporter: | Edward Fultz | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Workbench: SQL Editor | Severity: | S2 (Serious) |
Version: | 8.0.17 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[23 Jul 2019 10:59]
Edward Fultz
[23 Jul 2019 11:02]
Edward Fultz
This is an issue that we have had multiple people recreate and is a serious bug because losing the original stored procedure, potentially, impacts production environments and is not noticeable until your code fails to find a stored procedure that should still be in the environment
[23 Jul 2019 11:24]
Edward Fultz
version 6.3.9 build 10690321 CE behaved without the bug - copying stored procedure did not drop the original stored procedure
[23 Jul 2019 11:57]
MySQL Verification Team
Thank you for the bug report. If you are using a version prior to 8.0.17 please try it. If the issue still exist provide a procedure create statement as test case. Thanks.
[23 Jul 2019 15:17]
Edward Fultz
I upgraded to v8.0.17 and the problem still exists. You can see below the drop procedure shows ABRH_Get_Labor but the create line shows procedure ABRH_Get_Labor_edf. This was a result of clicking the wrench on an existing stored procedure and when the procedure opens in the editor changing the name on the create line and clicking apply. The resulting SQL that is presented shows the issue. ============================================================================= USE `loyaltyconnect`; DROP procedure IF EXISTS `loyaltyconnect`.`ABRH_Get_Labor`; DELIMITER $$ USE `loyaltyconnect`$$ CREATE DEFINER=`lc`@`%` PROCEDURE `ABRH_Get_Labor_edf`( IN a_ApplicationKey VARCHAR(64),
[23 Jul 2019 15:18]
Edward Fultz
updated version line to be more specific
[23 Jul 2019 17:03]
MySQL Verification Team
Thank you for the feedback. Please provide the complete create statement of the original procedure. Thanks in advance.
[23 Jul 2019 19:14]
Edward Fultz
Original Stored Procedure CREATE DEFINER=`lc`@`%` PROCEDURE `cdm_get_tarpit`( IN a_clientId BIGINT(20), IN a_taskId VARCHAR(45) ) BEGIN DECLARE a_tarpit DECIMAL(10,2); set a_tarpit = 0.0; if (a_clientId = 31 and a_taskId in ('order-puller','labor-puller')) then select least(sum(x.numfails)*5,60) delaySeconds into a_tarpit from (select if(success=1,0,1) numFails from cdmperformance cp where cp.clientid = a_clientId and cp.taskid = a_taskId order by cp.jobdate desc limit 12) x; elseif (a_clientId = 31) then select least(sum(x.numfails)*5,60) delaySeconds into a_tarpit from (select if(success=1,0,1) numFails from cdmperformance cp where cp.clientid = a_clientId and cp.taskid = a_taskId order by cp.jobdate desc limit 12) x; end if; select a_tarpit; END ====================================================================================================================== invalid sql when changing the stored procedure name - see the drop is trying to remove cdm_get_tarpit instead of cdm_get_tarpit_new ====================================================================================================================== USE `loyaltyconnect`; DROP procedure IF EXISTS `loyaltyconnect`.`cdm_get_tarpit`; DELIMITER $$ USE `loyaltyconnect`$$ CREATE DEFINER=`lc`@`%` PROCEDURE `cdm_get_tarpit_new`( IN a_clientId BIGINT(20), IN a_taskId VARCHAR(45) ) BEGIN DECLARE a_tarpit DECIMAL(10,2); set a_tarpit = 0.0; if (a_clientId = 31 and a_taskId in ('order-puller','labor-puller')) then select least(sum(x.numfails)*5,60) delaySeconds into a_tarpit from (select if(success=1,0,1) numFails from cdmperformance cp where cp.clientid = a_clientId and cp.taskid = a_taskId order by cp.jobdate desc limit 12) x; elseif (a_clientId = 31) then select least(sum(x.numfails)*5,60) delaySeconds into a_tarpit from (select if(success=1,0,1) numFails from cdmperformance cp where cp.clientid = a_clientId and cp.taskid = a_taskId order by cp.jobdate desc limit 12) x; end if; select a_tarpit; END$$ DELIMITER ;
[27 Jul 2019 6:32]
MySQL Verification Team
Thank you for the feedback and providing requested details. - 8.0.17 rm -rf 96335/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/96335 --log-error-verbosity=3 bin/mysqld --defaults-file=./96335.cnf --basedir=$PWD --datadir=$PWD/96335 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/96335/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv=/tmp/ 2>&1 & DELIMITER // CREATE PROCEDURE test (IN con CHAR(20)) BEGIN SELECT con from dual; END // DELIMITER ; - from CLI mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> DELIMITER // mysql> CREATE PROCEDURE test -> (IN con CHAR(20)) -> BEGIN -> SELECT con from dual; -> END // Query OK, 0 rows affected (0.01 sec) mysql> DELIMITER ; - from WB, navigate to test->Stored Procedures -> Alter Stored procedure, rename procedure to test_new <-- apply USE `test`; DROP procedure IF EXISTS `test`.`test`; -- <- original proc is dropped DELIMITER $$ USE `test`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `test_new`(IN con CHAR(20)) BEGIN SELECT con from dual; END$$ DELIMITER ; ; - Follow same steps from 6.3.8 - notice that it don't drop old procedure but just creates new one - from WB, navigate to test->Stored Procedures -> Alter Stored procedure, rename procedure to test_new_new <-- apply USE `test`; DROP procedure IF EXISTS `test_new_new`; DELIMITER $$ USE `test`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `test_new_new`(IN con CHAR(20)) BEGIN SELECT con from dual; END$$ DELIMITER ;
[26 May 2020 16:58]
Christine Cole
Posted by developer: Fixed as of the upcoming MySQL Workbench 8.0.21 release, and here's the proposed changelog entry from the documentation team: A change made to the CREATE statement to rename an existing stored procedure did not also change the name used in the generated DROP PROCEDURE statement when the name change was applied using the editor. This fix modifies the generated statement to no longer drop the original stored procedure after it is renamed. Thank you for the bug report.