| Bug #86022 | TABLE_SCHEMA column transformed to lower case for upper case database names | ||
|---|---|---|---|
| Submitted: | 20 Apr 2017 16:31 | Modified: | 21 Apr 2017 7:35 |
| Reporter: | Lukas Eder | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Data Dictionary | Severity: | S3 (Non-critical) |
| Version: | 5.7.11 | OS: | Microsoft Windows (10) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Database name, information_schema, lower case, upper case | ||
[20 Apr 2017 23:13]
MySQL Verification Team
Thank you for the bug report. I couldn't repeat with most recent source server on Manjaro Linux:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 Source distribution 2017-apr-19
Copyright (c) 2000, 2017, 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 5.7 > CREATE DATABASE `UPPER`;
Query OK, 1 row affected (0,00 sec)
mysql 5.7 > CREATE TABLE `UPPER`.`T` (`I` int);
Query OK, 0 rows affected (0,33 sec)
mysql 5.7 >
mysql 5.7 > -- These two queries incorrectly report the database name as `upper`
mysql 5.7 > SELECT DISTINCT table_schema FROM information_schema.columns
-> WHERE information_schema.columns.table_schema = 'UPPER';
+--------------+
| table_schema |
+--------------+
| UPPER |
+--------------+
1 row in set (0,00 sec)
mysql 5.7 >
mysql 5.7 > SELECT DISTINCT table_schema FROM information_schema.columns
-> WHERE information_schema.columns.table_schema IN ('UPPER');
+--------------+
| table_schema |
+--------------+
| UPPER |
+--------------+
1 row in set (0,00 sec)
mysql 5.7 >
mysql 5.7 > -- These two queries correctly report the database name as `UPPER`:
mysql 5.7 > SELECT DISTINCT table_schema FROM information_schema.columns
-> WHERE concat(information_schema.columns.table_schema) IN ('UPPER');
+--------------+
| table_schema |
+--------------+
| UPPER |
+--------------+
1 row in set (0,00 sec)
mysql 5.7 >
mysql 5.7 > SELECT DISTINCT table_schema FROM information_schema.columns
-> WHERE information_schema.columns.table_schema IN ('UPPER', 'dummy');
+--------------+
| table_schema |
+--------------+
| UPPER |
+--------------+
1 row in set (0,00 sec)
mysql 5.7 >
mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------+
| innodb_version | 5.7.19 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.19 |
| version_comment | Source distribution 2017-apr-19 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------+
8 rows in set (0,00 sec)
mysql 5.7 >
[21 Apr 2017 7:35]
Lukas Eder
Thanks for looking into this. I omitted the OS because this can be reproduced on Windows and MacOSX as reported here in a jOOQ issue: https://github.com/jOOQ/jOOQ/issues/5213 Some additional info: mysql> SHOW VARIABLES LIKE "%VERSION%"; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.7.11 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.11-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Win64 | +-------------------------+------------------------------+ Probably also relevant here: mysql> SHOW VARIABLES LIKE '%case%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | ON | | lower_case_table_names | 2 | +------------------------+-------+ Let me know if you need any additional info.
Description: When a database name is written in upper case, it is reported in lower case from the INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA column in some situations How to repeat: CREATE DATABASE `UPPER`; CREATE TABLE `UPPER`.`T` (`I` int); -- These two queries incorrectly report the database name as `upper` SELECT DISTINCT table_schema FROM information_schema.columns WHERE information_schema.columns.table_schema = 'UPPER'; SELECT DISTINCT table_schema FROM information_schema.columns WHERE information_schema.columns.table_schema IN ('UPPER'); -- These two queries correctly report the database name as `UPPER`: SELECT DISTINCT table_schema FROM information_schema.columns WHERE concat(information_schema.columns.table_schema) IN ('UPPER'); SELECT DISTINCT table_schema FROM information_schema.columns WHERE information_schema.columns.table_schema IN ('UPPER', 'dummy');