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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.5
    • N/A
    • Stored routines
    • 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

          blocks

          Technical task - A technical task. MDEV-10591 Oracle-style packages

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            alvinr Alvin Richards (Inactive) created issue -
            alvinr Alvin Richards (Inactive) made changes -
            Field Original Value New Value
            Description h1. 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.

            h1. Solution
            If a drop database causes all the functions/procedures to be removed, leaving an empty pseudo database, then drop the database as well.

            h1. Workaround
            Drop the package, e.g.
            {code}
            drop package test_pack1;
            {code}

            h1. Reproduce
            {code}
            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 |
            +-------+--------------------+
            {code}
            Body is not created, so not listed in mysql.proc

            {code}
            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 |
            +-------+--------------------+
            {code}
            Package body is created, so the function now appears in mysql.proc.

            {code}
            drop database test;

            select db, name from mysql.proc;
            +-------+--------------------+
            | db | name |
            +-------+--------------------+
            | mysql | AddGeometryColumn |
            | mysql | DropGeometryColumn |
            +-------+--------------------+
            {code}
            Database dropped, functions no longer listed in mysql.proc

            {code}
            create database test;

            use test;

            select db, name from mysql.proc;
            +-------+--------------------+
            | db | name |
            +-------+--------------------+
            | mysql | AddGeometryColumn |
            | mysql | DropGeometryColumn |
            +-------+--------------------+
            {code}
            Database re-created, by 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
            {code}




            		h1. 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.

            h1. Solution
            If a drop database causes all the functions/procedures to be removed, leaving an empty pseudo database, then drop the database as well.

            h1. Workaround
            Drop the package, e.g.
            {code}
            drop package test_pack1;
            {code}

            h1. Reproduce
            {code}
            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 |
            +-------+--------------------+
            {code}
            Body is not created, so not listed in mysql.proc

            {code}
            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 |
            +-------+--------------------+
            {code}
            Package body is created, so the function now appears in mysql.proc.

            {code}
            drop database test;

            select db, name from mysql.proc;
            +-------+--------------------+
            | db | name |
            +-------+--------------------+
            | mysql | AddGeometryColumn |
            | mysql | DropGeometryColumn |
            +-------+--------------------+
            {code}
            Database dropped, functions no longer listed in mysql.proc

            {code}
            create database test;

            use test;

            select db, name from mysql.proc;
            +-------+--------------------+
            | db | name |
            +-------+--------------------+
            | mysql | AddGeometryColumn |
            | mysql | DropGeometryColumn |
            +-------+--------------------+
            {code}
            Database re-created, by functions not listed.

            {code}
            delimiter $$
            create PACKAGE pack1 IS

            function f() RETURN BOOLEAN;
             
            END $$
            delimiter ;
            ERROR 1007 (HY000): Can't create database 'test_pack1'; database exists
            {code}




            alvinr Alvin Richards (Inactive) made changes -
            Description h1. 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.

            h1. Solution
            If a drop database causes all the functions/procedures to be removed, leaving an empty pseudo database, then drop the database as well.

            h1. Workaround
            Drop the package, e.g.
            {code}
            drop package test_pack1;
            {code}

            h1. Reproduce
            {code}
            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 |
            +-------+--------------------+
            {code}
            Body is not created, so not listed in mysql.proc

            {code}
            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 |
            +-------+--------------------+
            {code}
            Package body is created, so the function now appears in mysql.proc.

            {code}
            drop database test;

            select db, name from mysql.proc;
            +-------+--------------------+
            | db | name |
            +-------+--------------------+
            | mysql | AddGeometryColumn |
            | mysql | DropGeometryColumn |
            +-------+--------------------+
            {code}
            Database dropped, functions no longer listed in mysql.proc

            {code}
            create database test;

            use test;

            select db, name from mysql.proc;
            +-------+--------------------+
            | db | name |
            +-------+--------------------+
            | mysql | AddGeometryColumn |
            | mysql | DropGeometryColumn |
            +-------+--------------------+
            {code}
            Database re-created, by functions not listed.

            {code}
            delimiter $$
            create PACKAGE pack1 IS

            function f() RETURN BOOLEAN;
             
            END $$
            delimiter ;
            ERROR 1007 (HY000): Can't create database 'test_pack1'; database exists
            {code}




            		h1. 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.

            h1. 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.

            h1. Workaround
            Drop the package, e.g.
            {code}
            drop package test_pack1;
            {code}

            h1. Reproduce
            {code}
            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 |
            +-------+--------------------+
            {code}
            Body is not created, so not listed in mysql.proc

            {code}
            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 |
            +-------+--------------------+
            {code}
            Package body is created, so the function now appears in mysql.proc.

            {code}
            drop database test;

            select db, name from mysql.proc;
            +-------+--------------------+
            | db | name |
            +-------+--------------------+
            | mysql | AddGeometryColumn |
            | mysql | DropGeometryColumn |
            +-------+--------------------+
            {code}
            Database dropped, functions no longer listed in mysql.proc

            {code}
            create database test;

            use test;

            select db, name from mysql.proc;
            +-------+--------------------+
            | db | name |
            +-------+--------------------+
            | mysql | AddGeometryColumn |
            | mysql | DropGeometryColumn |
            +-------+--------------------+
            {code}
            Database re-created, but functions not listed.

            {code}
            delimiter $$
            create PACKAGE pack1 IS

            function f() RETURN BOOLEAN;
             
            END $$
            delimiter ;
            ERROR 1007 (HY000): Can't create database 'test_pack1'; database exists
            {code}
            Create package now fails because the pseudo database is still present.



            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Fix Version/s N/A [ 14700 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 80250 ] MariaDB v4 [ 151921 ]

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.