Problem
When a Oracle storage procedure is created, entries in mysql.proc are namespaces by the database that the procedures were created in.
When the Database is dropped, the entries "appear" to be removed from mysql.proc.
If the same name database is then created (or perhaps and database is then selected), re-creating the stored procedures will fail because the "database" exists - this is the pseudo database created for the package.
Solution
If a drop database causes all the functions/procedures to be removed, leaving an empty pseudo database, then drop the database as well.
or
Don't fail the package create if the pseudo database is already present.
Workaround
Drop the package, e.g.
Reproduce
SET sql_mode = 'ORACLE';
|
|
create database if not exists test;
|
|
use test
|
|
delimiter $$
|
create PACKAGE pack1 IS
|
|
function f() RETURN BOOLEAN;
|
|
END $$
|
delimiter ;
|
|
select db, name from mysql.proc;
|
|
+-------+--------------------+
|
| db | name |
|
+-------+--------------------+
|
| mysql | AddGeometryColumn |
|
| mysql | DropGeometryColumn |
|
+-------+--------------------+
|
Body is not created, so not listed in mysql.proc
delimiter $$
|
create PACKAGE BODY pack1 IS
|
|
FUNCTION f() RETURN BOOLEAN
|
IS
|
BEGIN
|
RETURN FALSE;
|
END;
|
|
END $$
|
delimiter ;
|
|
select db, name from mysql.proc;
|
+-------+--------------------+
|
| db | name |
|
+-------+--------------------+
|
| mysql | AddGeometryColumn |
|
| mysql | DropGeometryColumn |
|
| test | pack1.f |
|
+-------+--------------------+
|
Package body is created, so the function now appears in mysql.proc.
drop database test;
|
|
select db, name from mysql.proc;
|
+-------+--------------------+
|
| db | name |
|
+-------+--------------------+
|
| mysql | AddGeometryColumn |
|
| mysql | DropGeometryColumn |
|
+-------+--------------------+
|
Database dropped, functions no longer listed in mysql.proc
create database test;
|
|
use test;
|
|
select db, name from mysql.proc;
|
+-------+--------------------+
|
| db | name |
|
+-------+--------------------+
|
| mysql | AddGeometryColumn |
|
| mysql | DropGeometryColumn |
|
+-------+--------------------+
|
Database re-created, but functions not listed.
delimiter $$
|
create PACKAGE pack1 IS
|
|
function f() RETURN BOOLEAN;
|
|
END $$
|
delimiter ;
|
ERROR 1007 (HY000): Can't create database 'test_pack1'; database exists
|
Create package now fails because the pseudo database is still present.
|