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: | Windows (10) |
Assigned to: | CPU Architecture: | Any | |
Tags: | Database name, information_schema, lower case, upper case |
[20 Apr 2017 16:31]
Lukas Eder
[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.