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:
None 
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 0:19] Giuseppe Maxia
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.
[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.