Bug #89774 procedure's schema names are stored as lowercase

• Submitted: 22 Feb 2018 16:20
• Modified: 23 Feb 2018 15:17
• Reporter: Aram Mirzadeh
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.7.21
• OS: MacOS (Docker version 17.12.0-ce, build c97c6d6)
• CPU Architecture: x86

[22 Feb 2018 16:20] Aram Mirzadeh

Description:
Running a oracle-linux based docker image on top of a Mac host still stores the SPROC schema names as lowercase even though they have been defined as uppercase.

How to repeat:
# docker run --name=mysql \
        --mount type=bind,source=/opt/docker/mysql/conf/my.cnf,target=/etc/my.cnf \
        --mount type=bind,source=/opt/docker/mysql/data,target=/var/lib/mysql \
        --restart=on-failure:3 \
        -p 3306:3306 \
        -e MYSQL_ROOT_PASSWORD=foobar \
        -e MYSQL_ROOT_HOST=% \
        -d mysql/mysql-server:5.7

CREATE SCHEMA `TDB` ;

USE `TDB`;
DROP procedure IF EXISTS `testProc`;

DELIMITER $$
USE `TDB`$$
CREATE PROCEDURE `TestProc` ()
BEGIN

END$$

mysql> select distinct db from mysql.proc where db in ('TDB',LCASE('TDB'));
+-----+
| db  |
+-----+
| tdb |
+-----+
1 row in set (0.00 sec)

ALSO:

mysql> SELECT distinct routine_schema from information_schema.ROUTINES WHERE routine_schema in ('TDB',LCASE('TDB'));
+----------------+
| routine_schema |
+----------------+
| tdb            |
+----------------+

Suggested fix:
Don't auto-lowercase the schema name.

[23 Feb 2018 15:17] MySQL Verification Team

Thank you for the bug report. Please read about MAC OS
 X:

https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

miguel:~ miguel$ sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
Password:
miguel:~ miguel$ sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
miguel:~ miguel$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password='miguel';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE SCHEMA `TDB` ;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> USE `TDB`;
Database changed
mysql> DROP procedure IF EXISTS `testProc`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> DELIMITER $$
mysql> USE `TDB`$$
Database changed
mysql> CREATE PROCEDURE `TestProc` ()
    -> BEGIN
    ->
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TDB                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT distinct routine_schema from information_schema.ROUTINES WHERE routine_schema in ('TDB',LCASE('TDB'));
+----------------+
| routine_schema |
+----------------+
| tdb            |
+----------------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE "%lower%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+
2 rows in set (0.04 sec)

mysql>

[23 Feb 2018 15:23] MySQL Verification Team

miguel:~ miguel$ sudo ls /usr/local/mysql/data/
Password:
TDB			ib_buffer_pool		ib_logfile1		ibtmp1			mysqld.local.err	performance_schema
auto.cnf		ib_logfile0		ibdata1			mysql			mysqld.local.pid	sys
miguel:~ miguel$

[23 Feb 2018 15:55] MySQL Verification Team

See https://bugs.mysql.com/bug.php?id=71407.