INFORMATION_SCHEMA tables are case sensitive

Jul 20, 2010 / By Sheeri Cabral

Tags: , ,

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)

5 Responses to “INFORMATION_SCHEMA tables are case sensitive”

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

    Unfortunately it hasn’t been fixed yet.

  • 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

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

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

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

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>