INFORMATION_SCHEMA tables are case sensitive

Posted in: Technical Track

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)
email

Interested in working with Sheeri? Schedule a tech call.

5 Comments. Leave new

Roland Bouman
July 20, 2010 12:22 pm

This is a bug. http://bugs.mysql.com/bug.php?id=34921

Unfortunately it hasn’t been fixed yet.

Reply
Roland Bouman
July 20, 2010 12:26 pm

mm, apparently, there is a documented workaround: http://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html

I kind of forgot about this, but I must say I am a bit disappointed there has been no real progress in bug #34921

Reply
Kostja Osipov
July 21, 2010 4:22 pm

Eh? Use a SELECT FROM INFORMATION_SCHEMA.TABLES, not a SHOW TABLES statement.
SHOW TABLES is case sensitive, backward-compatible.
information-schema is case insensitive:

mysql> select table_name from information_schema.tables where table_name like ‘%innodb%’;
+————————-+
| table_name |
+————————-+
| INNODB_SYS_FIELDS |
| INNODB_TRX |
| INNODB_SYS_INDEXES |
| INNODB_LOCK_WAITS |
| INNODB_SYS_TABLESTATS |
| INNODB_CMP |
| INNODB_SYS_COLUMNS |
| INNODB_CMP_RESET |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_LOCKS |
| INNODB_CMPMEM_RESET |
| INNODB_CMPMEM |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLES |
+————————-+
14 rows in set (0.02 sec)

mysql> show tables like ‘%innodb%’;
Empty set (0.00 sec)

Reply
Kostja Osipov
July 21, 2010 4:25 pm

And yes, just in case, I’m using MySQL 5.5.

Reply
Roland Bouman
July 23, 2010 4:08 pm

Kostja, no offence, but it doesn’t work for me – not on 5.1 and not on 5.5.5m3:

mysql> select schema_name from information_schema.schemata where schema_name = ‘test’;
+————-+
| schema_name |
+————-+
| test |
+————-+
1 row in set (0.00 sec)

mysql> select schema_name from information_schema.schemata where schema_name = ‘tesT’;
Empty set (0.00 sec)

mysql> select version();
+———–+
| version() |
+———–+
| 5.5.5-m3 |
+———–+
1 row in set (0.00 sec)

mysql> select collation_name from information_schema.columns where table_schema = ‘information_schema’ and table_name = ‘SCHEMATA’ and column_name = ‘SCHEMA_NAME’;
+—————–+
| collation_name |
+—————–+
| utf8_general_ci |
+—————–+
1 row in set (0.00 sec)

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *