INFORMATION_SCHEMA tables are case sensitive

I wanted to get examples of some of the extra information that the Percona server has in its INFORMATION_SCHEMA metadata, and in doing so, I stumbled across an interesting MySQL bug/feature/point — INFORMATION_SCHEMA tables (which are actually system views) are case sensitive when used in comparisons:

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  select @@version;
+--------------------+
| @@version          |
+--------------------+
| 5.1.36-xtradb6-log |
+--------------------+
1 row in set (0.00 sec)

mysql> use information_schema;
Database changed
mysql> show tables like 'innodb%';
Empty set (0.00 sec)

mysql>  show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_BUFFER_POOL_PAGES_INDEX         |
| INNODB_RSEG                            |
| INNODB_LOCKS                           |
| INNODB_BUFFER_POOL_PAGES               |
| INNODB_TRX                             |
| INNODB_INDEX_STATS                     |
| INNODB_LOCK_WAITS                      |
| INNODB_CMP_RESET                       |
| INNODB_CMP                             |
| INNODB_CMPMEM_RESET                    |
| INNODB_BUFFER_POOL_PAGES_BLOB          |
| INNODB_CMPMEM                          |
| INNODB_TABLE_STATS                     |
+----------------------------------------+
13 rows in set (0.00 sec)

It is not just for the new tables Percona has added:

mysql> show tables like 'table%';

Empty set (0.00 sec)

mysql> show tables like 'TABLE%';

+---------------------------------------+
| Tables_in_information_schema (TABLE%) |
+---------------------------------------+
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
+---------------------------------------+
3 rows in set (0.00 sec)

And it is not due to the collation:

mysql> show global variables like '%collat%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> show session variables like '%collat%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Comments are closed.