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)
Post new comment