[MDEV-20642] Orphan procedure remains in mysql.proc after dropping database Created: 2019-09-21  Updated: 2019-09-21

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Procedure, Server
Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: upstream


 Description   

The test below runs DROP DATABASE and kills the connection which runs it. Then it runs DROP DATABASE again, to ensure it is really dropped if it hadn't been yet. However, a procedure which was previously created within the dropped database survives and stays in mysql.proc.

The test is non-deterministic, run with --repeat=N. It currently fails for me every time, but it can vary on different machines and builds.

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a INT) ENGINE=MEMORY;
 
CREATE SCHEMA db;
 
CREATE PROCEDURE db.pr() SELECT * FROM test.t1; 
 
 
--connect (con1,localhost,root,,test)
 
--let $con1_id= `SELECT CONNECTION_ID()`
 
--send
 
  DROP DATABASE db;
 
 
 
--connection default
 
SELECT * FROM t1;
 
--eval KILL $con1_id
 
 
 
--error 0,ER_BAD_DB_ERROR,ER_SP_DOES_NOT_EXIST
 
 
 
--connection con1
 
--error 0,2013
 
--reap
 
--disconnect con1
 
--connection default
 
DROP DATABASE IF EXISTS db;
 
 
 
if (`SELECT 1 FROM mysql.proc WHERE db = 'db' AND name = 'pr'`)
 
{
 
  --die # Found orphan procedure db.pr in mysql.proc
 
}
 
 
 
# Cleanup
 
DROP TABLE test.t1;

10.4 71c57bcf

 
DROP DATABASE IF EXISTS db;
 
Warnings:
 
Note    1008    Can't drop database 'db'; database doesn't exist
 
bug.find4 'innodb'                       [ 2 fail ]
 
        Test ended at 2019-09-21 16:18:44
 
 
 
CURRENT_TEST: bug.find4
 
mysqltest: At line 27: # Found orphan procedure db.pr in mysql.proc

Reproducible on all of MariaDB and at least on MySQL 5.7 (8.0 doesn't seem to have mysql.proc).
Generated at Thu Feb 08 09:01:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.