MySQL Bugs: #96289: Stored Procedure Copy Drops Wrong Procedure

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:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
making a copy of a stored procedure results in the original stored procedure being dropped

How to repeat:
open a stored procedure for update
change the create statement to rename the procedure (such as adding _new to the end of the name)
when you click on apply the sql code generated shows dropping the original stored procedure and not the new stored procedure name
the result of this issue is that the original stored procedure that you wanted to keep is dropped but the new, copy, remains.

Suggested fix:
what needs to happen is if you rename the stored procedure on the create statement the drop statement needs to use the new name and not the original stored procedure name

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

version 6.3.9 build 10690321 CE behaved without the bug - copying stored procedure did not drop the original stored procedure

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.

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),

updated version line to be more specific

Thank you for the feedback. Please provide the complete create statement of the original procedure. Thanks in advance.

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 ;

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 ;

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.