MySQL Bugs: #72912: Inconsistent naming of tables in different I

Bug #72912	Inconsistent naming of tables in different I_S.innodb% tables
Submitted:	8 Jun 2014 13:31	Modified:	8 Jun 2014 19:38
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.6.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	information_schema, INNODB_BUFFER_PAGE_LRU, innodb_sys_tables

Description:
Different InnoDB-related tables in INFORMATION_SCHEMA use different ways to present fully qualified table names:

mysql> show create table test.ft\G
*************************** 1. row ***************************
       Table: ft
Create Table: CREATE TABLE `ft` (
  `id` int(11) NOT NULL,
  `details` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `details` (`details`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into test.ft values(1, 'some text');
Query OK, 1 row affected (0.10 sec)

mysql> select table_name from information_schema.innodb_buffer_page_lru where table_name like 'test%';
Empty set (0.01 sec)

mysql> select table_name from information_schema.innodb_buffer_page_lru where table_name like '%ft%';
+--------------------------------------------------------+
| table_name                                             |
+--------------------------------------------------------+
| `test`.`ft`                                            |
| `test`.`FTS_0000000000000206_DELETED`                  |
| `test`.`FTS_0000000000000206_DELETED_CACHE`            |
| `test`.`FTS_0000000000000206_BEING_DELETED`            |
| `test`.`FTS_0000000000000206_BEING_DELETED_CACHE`      |
| `test`.`FTS_0000000000000206_CONFIG`                   |
| `test`.`ft`                                            |
| `test`.`FTS_0000000000000206_00000000000005f5_INDEX_1` |
| `test`.`FTS_0000000000000206_00000000000005f5_INDEX_2` |
| `test`.`FTS_0000000000000206_00000000000005f5_INDEX_3` |
| `test`.`FTS_0000000000000206_00000000000005f5_INDEX_4` |
| `test`.`FTS_0000000000000206_00000000000005f5_INDEX_5` |
| `test`.`FTS_0000000000000206_00000000000005f5_INDEX_6` |
+--------------------------------------------------------+
13 rows in set (0.01 sec)

So, in the innodb_buffer_page_lru table database and table names are quoted with backtics and separated by dot. While in the innodb_sys_tables:

mysql> select name from information_schema.innodb_sys_tables where name like '%f
t%';
+----------------------------------------------------+
| name                                               |
+----------------------------------------------------+
| test/FTS_0000000000000206_00000000000005f5_INDEX_1 |
| test/FTS_0000000000000206_00000000000005f5_INDEX_2 |
| test/FTS_0000000000000206_00000000000005f5_INDEX_3 |
| test/FTS_0000000000000206_00000000000005f5_INDEX_4 |
| test/FTS_0000000000000206_00000000000005f5_INDEX_5 |
| test/FTS_0000000000000206_00000000000005f5_INDEX_6 |
| test/FTS_0000000000000206_BEING_DELETED            |
| test/FTS_0000000000000206_BEING_DELETED_CACHE      |
| test/FTS_0000000000000206_CONFIG                   |
| test/FTS_0000000000000206_DELETED                  |
| test/FTS_0000000000000206_DELETED_CACHE            |
| test/ft                                            |
+----------------------------------------------------+
12 rows in set (0.00 sec)

they are NOT quoted at all and separated with slash. This is on recent 5.6.19:

mysql> select version(), @@innodb_file_per_table;
+------------+-------------------------+
| version()  | @@innodb_file_per_table |
+------------+-------------------------+
| 5.6.19-log |                       1 |
+------------+-------------------------+
1 row in set (0.00 sec)

I wonder why different naming approaches are used?

How to repeat:
Run this:

CREATE TABLE test.`ft` (

Thank you for the bug report. I got for information_schema.innodb_sys_tables
an empty set on Windows and on MacBook Pro. I missed something?. Thanks.

C:\dbs\5.6>bin\mysql -uroot -p test
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `ft` (
    ->   `id` int(11) NOT NULL,
    ->   `details` text,
    ->   PRIMARY KEY (`id`),
    ->   FULLTEXT KEY `details` (`details`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.97 sec)

mysql> insert into test.ft values(1, 'some text');
Query OK, 1 row affected (0.02 sec)

mysql> select table_name from information_schema.innodb_buffer_page_lru where table_name like 'test%';
Empty set (0.02 sec)

mysql> select table_name from information_schema.innodb_buffer_page_lru where table_name like '%ft%';
+--------------------------------------------------------+
| table_name                                             |
+--------------------------------------------------------+
| `x`.`ft`                                               |
| `x`.`FTS_0000000000000015_DELETED`                     |
| `x`.`FTS_0000000000000015_DELETED_CACHE`               |
| `x`.`FTS_0000000000000015_BEING_DELETED`               |
| `x`.`FTS_0000000000000015_BEING_DELETED_CACHE`         |
| `x`.`FTS_0000000000000015_CONFIG`                      |
| `x`.`ft`                                               |
| `x`.`FTS_0000000000000015_000000000000001e_INDEX_1`    |
| `x`.`FTS_0000000000000015_000000000000001e_INDEX_2`    |
| `x`.`FTS_0000000000000015_000000000000001e_INDEX_3`    |
| `x`.`FTS_0000000000000015_000000000000001e_INDEX_4`    |
| `x`.`FTS_0000000000000015_000000000000001e_INDEX_5`    |
| `x`.`FTS_0000000000000015_000000000000001e_INDEX_6`    |
| `test`.`ft`                                            |
| `test`.`FTS_0000000000000021_DELETED`                  |
| `test`.`FTS_0000000000000021_DELETED_CACHE`            |
| `test`.`FTS_0000000000000021_BEING_DELETED`            |
| `test`.`FTS_0000000000000021_BEING_DELETED_CACHE`      |
| `test`.`FTS_0000000000000021_CONFIG`                   |
| `test`.`ft`                                            |
| `test`.`FTS_0000000000000021_000000000000002c_INDEX_1` |
| `test`.`FTS_0000000000000021_000000000000002c_INDEX_2` |
| `test`.`FTS_0000000000000021_000000000000002c_INDEX_3` |
| `test`.`FTS_0000000000000021_000000000000002c_INDEX_4` |
| `test`.`FTS_0000000000000021_000000000000002c_INDEX_5` |
| `test`.`FTS_0000000000000021_000000000000002c_INDEX_6` |
+--------------------------------------------------------+
26 rows in set (0.00 sec)

mysql> select name from information_schema.innodb_sys_tables where name like '%f
    '> t%';
Empty set (0.00 sec)

mysql> select version(), @@innodb_file_per_table;
+-----------+-------------------------+
| version() | @@innodb_file_per_table |
+-----------+-------------------------+
| 5.6.19    |                       1 |
+-----------+-------------------------+
1 row in set (0.00 sec)

mysql>

Run this:

select name from information_schema.innodb_sys_tables where name like '%ft%';

without new line inside '%ft%', please.

It seems "How to repeat" section was truncated somehow upon submission. So, here it is again:

CREATE TABLE test.`ft` (
  `id` int(11) NOT NULL,
  `details` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `details` (`details`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test.ft values(1, 'some text');

select table_name from information_schema.innodb_buffer_page_lru where table_name like 'test%';
select table_name from information_schema.innodb_buffer_page_lru where table_name like '%ft%';

select name from information_schema.innodb_sys_tables where name like '%ft%';

Thank you for the feedback now I was able to repeat.