| Bug #74146 | querying the new data dictionary is case sensitive | ||
|---|---|---|---|
| Submitted: | 30 Sep 2014 0:19 | Modified: | 18 May 2016 18:07 |
| Reporter: | Giuseppe Maxia (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S2 (Serious) |
| Version: | 5.7.5 DD labs | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | data dictionary, usability | ||
[30 Sep 2014 8:13]
MySQL Verification Team
Hello Giuseppe Maxia, Thank you for the report and test case. Thanks, Umesh
[30 Sep 2014 8:14]
MySQL Verification Team
// 5.7.5-labs-dd mysql> show variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.7.5 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.7.5-labs-dd-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | linux-el6 | +-------------------------+------------------------------+ 7 rows in set (0.00 sec) mysql> select count(*) from information_schema . tables where engine='innodb'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from information_schema . tables where engine='InnoDB'; +----------+ | count(*) | +----------+ | 42 | +----------+ 1 row in set (0.01 sec) // 5.7.6-m16-enterprise-commercial-advanced mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.7.6 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.7.6-m16-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> select count(*) from information_schema . tables where engine='innodb'; +----------+ | count(*) | +----------+ | 19 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from information_schema . tables where engine='InnoDB'; +----------+ | count(*) | +----------+ | 19 | +----------+ 1 row in set (0.01 sec)
[18 May 2016 18:07]
Erlend Dahl
Recently fixed in 5.8.0.
Description: Up to MySQL 5.7.4, a query for engines to the information schema would return results regardless of the case of the string used. For example, "select count(*) from information_schema . tables where engine='innodb';" would give the same result as " select count(*) from information_schema . tables where engine='InnoDB';" With MySQL 5.7.5 using the new data dictionary, instead, the result depends on the case of the search string. mysql [localhost] {msandbox} (mysql) > select version(); +---------------+ | version() | +---------------+ | 5.7.5-labs-dd | +---------------+ 1 row in set (0.01 sec) mysql [localhost] {msandbox} (mysql) > select count(*) from information_schema . tables where engine='innodb'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (mysql) > select count(*) from information_schema . tables where engine='InnoDB'; +----------+ | count(*) | +----------+ | 42 | +----------+ 1 row in set (0.00 sec) How to repeat: run these two queries: select count(*) from information_schema . tables where engine='innodb'; select count(*) from information_schema . tables where engine='InnoDB'; They should return the same result. Suggested fix: The reason for the regression is that the information_schema tables are implemented as views to the data dictionary tables, which in turn are created with collation "UTF8_bin". The search fields in the data dictionary should be relaxed, so that they can be queried with case-insensitive requests.