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

mysql_upgrade: case (in)sensitivity for stored procedures

Details

    • 5.5.58

    Description

      TL;DR:

      • Create first procedure in one database
      • Create second procedure in different database
      • ALTER TABLE proc CONVERT TO CHARACTER SET utf8;
        --> error

      Actual code:

      [root@test]# mysql
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 2
      Server version: 10.1.26-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> use test
      Database changed
      MariaDB [test]> DELIMITER |
      MariaDB [test]> CREATE PROCEDURE getStudents()
          -> BEGIN
          ->  SELECT * FROM students;
          -> END|
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> DELIMITER ;
      MariaDB [test]> create database TEST;
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [test]> use TEST
      Database changed
      MariaDB [TEST]> DELIMITER |
      MariaDB [TEST]> 
      MariaDB [TEST]> CREATE PROCEDURE getStudents()
          -> BEGIN
          ->  SELECT * FROM students;
          -> END|
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [TEST]> DELIMITER ;
      MariaDB [TEST]> use mysql
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Database changed
      MariaDB [mysql]> select db,name,type from proc;
      +-------+--------------------+-----------+
      | db    | name               | type      |
      +-------+--------------------+-----------+
      | TEST  | getStudents        | PROCEDURE |
      | mysql | AddGeometryColumn  | PROCEDURE |
      | mysql | DropGeometryColumn | PROCEDURE |
      | test  | getStudents        | PROCEDURE |
      +-------+--------------------+-----------+
      4 rows in set (0.00 sec)
       
      MariaDB [mysql]> ALTER TABLE proc CONVERT TO CHARACTER SET utf8;
      ERROR 1062 (23000): Duplicate entry 'TEST-getStudents-PROCEDURE' for key 'PRIMARY'
       
      MariaDB [mysql]>
      

      There is a bug report to RHEL, you can check:
      https://bugzilla.redhat.com/show_bug.cgi?id=1490398
      However the issue is reproducible pretty well anywhere in any MDB version.

      That is obviously a pretty unpleasant when using mysql_upgrade on bigger projects.

      Attachments

        Activity

          mschorm Michal Schorm added a comment -

          Can you please check this one?

          use test
          DELIMITER |
           
          CREATE PROCEDURE getStudents()
          BEGIN
           SELECT * FROM students;
          END|
           
          DELIMITER ;
          create database TEST;
          use TEST
          DELIMITER |
           
          CREATE PROCEDURE getStudents()
          BEGIN
           SELECT * FROM students;
          END|
           
          DELIMITER ;
          use mysql
          select db,name,type from proc;
           
          ALTER TABLE proc CONVERT TO CHARACTER SET utf8;
          

          .

          I still get this result with 5.5.58:

          [root@test_machine tmp]# mysql
          Welcome to the MariaDB monitor.  Commands end with ; or \g.
          Your MariaDB connection id is 2
          Server version: 5.5.58-MariaDB MariaDB Server
           
          Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
           
          Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
           
          MariaDB [(none)]> show databases;
          +--------------------+
          | Database           |
          +--------------------+
          | information_schema |
          | mysql              |
          | performance_schema |
          | test               |
          +--------------------+
          4 rows in set (0.00 sec)
           
          MariaDB [(none)]> use test
          Database changed
          MariaDB [test]> DELIMITER |
          MariaDB [test]> 
          MariaDB [test]> CREATE PROCEDURE getStudents()
              -> BEGIN
              ->  SELECT * FROM students;
              -> END|
          Query OK, 0 rows affected (0.00 sec)
           
          MariaDB [test]> 
          MariaDB [test]> DELIMITER ;
          MariaDB [test]> create database TEST;
          Query OK, 1 row affected (0.00 sec)
           
          MariaDB [test]> use TEST
          Database changed
          MariaDB [TEST]> DELIMITER |
          MariaDB [TEST]> 
          MariaDB [TEST]> CREATE PROCEDURE getStudents()
              -> BEGIN
              ->  SELECT * FROM students;
              -> END|
          Query OK, 0 rows affected (0.00 sec)
           
          MariaDB [TEST]> 
          MariaDB [TEST]> DELIMITER ;
          MariaDB [TEST]> use mysql
          Reading table information for completion of table and column names
          You can turn off this feature to get a quicker startup with -A
           
          Database changed
          MariaDB [mysql]> select db,name,type from proc;
          +------+-------------+-----------+
          | db   | name        | type      |
          +------+-------------+-----------+
          | TEST | getStudents | PROCEDURE |
          | test | getStudents | PROCEDURE |
          +------+-------------+-----------+
          2 rows in set (0.00 sec)
           
          MariaDB [mysql]> 
          MariaDB [mysql]> ALTER TABLE proc CONVERT TO CHARACTER SET utf8;
          ERROR 1062 (23000): Duplicate entry 'TEST-getStudents-PROCEDURE' for key 'PRIMARY'
          
          

          mschorm Michal Schorm added a comment - Can you please check this one? use test DELIMITER |   CREATE PROCEDURE getStudents() BEGIN SELECT * FROM students; END |   DELIMITER ; create database TEST; use TEST DELIMITER |   CREATE PROCEDURE getStudents() BEGIN SELECT * FROM students; END |   DELIMITER ; use mysql select db, name ,type from proc;   ALTER TABLE proc CONVERT TO CHARACTER SET utf8; . I still get this result with 5.5.58: [root@test_machine tmp]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.58-MariaDB MariaDB Server   Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   MariaDB [(none)]> show databases; + --------------------+ | Database | + --------------------+ | information_schema | | mysql | | performance_schema | | test | + --------------------+ 4 rows in set (0.00 sec)   MariaDB [(none)]> use test Database changed MariaDB [test]> DELIMITER | MariaDB [test]> MariaDB [test]> CREATE PROCEDURE getStudents() -> BEGIN -> SELECT * FROM students; -> END | Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> MariaDB [test]> DELIMITER ; MariaDB [test]> create database TEST; Query OK, 1 row affected (0.00 sec)   MariaDB [test]> use TEST Database changed MariaDB [TEST]> DELIMITER | MariaDB [TEST]> MariaDB [TEST]> CREATE PROCEDURE getStudents() -> BEGIN -> SELECT * FROM students; -> END | Query OK, 0 rows affected (0.00 sec)   MariaDB [TEST]> MariaDB [TEST]> DELIMITER ; MariaDB [TEST]> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Database changed MariaDB [mysql]> select db, name ,type from proc; + ------+-------------+-----------+ | db | name | type | + ------+-------------+-----------+ | TEST | getStudents | PROCEDURE | | test | getStudents | PROCEDURE | + ------+-------------+-----------+ 2 rows in set (0.00 sec)   MariaDB [mysql]> MariaDB [mysql]> ALTER TABLE proc CONVERT TO CHARACTER SET utf8; ERROR 1062 (23000): Duplicate entry 'TEST-getStudents-PROCEDURE' for key 'PRIMARY'

          I've backported MDEV-8960 as earlier comments suggested.

          But it didn't fix the issue in question, because MDEV-8960 (Can't refer the same column twice in one ALTER TABLE) doesn't seem to have anything to do with ALTER TABLE proc CONVERT TO CHARACTER SET.

          serg Sergei Golubchik added a comment - I've backported MDEV-8960 as earlier comments suggested. But it didn't fix the issue in question, because MDEV-8960 (Can't refer the same column twice in one ALTER TABLE) doesn't seem to have anything to do with ALTER TABLE proc CONVERT TO CHARACTER SET .
          mschorm Michal Schorm added a comment -

          Affect 10.2.11 too, reproducible the exact same way.

          mschorm Michal Schorm added a comment - Affect 10.2.11 too, reproducible the exact same way.
          mschorm Michal Schorm added a comment -

          Any update for MariaDB 5.5?

          mschorm Michal Schorm added a comment - Any update for MariaDB 5.5?
          mschorm Michal Schorm added a comment -

          Hello, what's the current state?
          I still wait on this issue to be solved, with a customer behind me.

          Is it possible to include it to the 5.5.62 release?
          Is tahere any mor information I can help with?

          mschorm Michal Schorm added a comment - Hello, what's the current state? I still wait on this issue to be solved, with a customer behind me. Is it possible to include it to the 5.5.62 release? Is tahere any mor information I can help with?

          People

            serg Sergei Golubchik
            mschorm Michal Schorm
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.