Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12450

PL/SQL stored procedure appears to be removed after a drop database, but then fails to re-create - database still exists

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.5
    • Fix Version/s: N/A
    • Component/s: Stored routines
    • Labels:
      None

      Description

      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.

      drop package test_pack1;
      

      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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              alvinr Alvin Richards (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: