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

mysql_upgrade: case (in)sensitivity for stored procedures

    XMLWordPrintable

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

          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.