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

[8 Jun 2014 13:31] Valeriy Kravchuk
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` (
[8 Jun 2014 18:24] MySQL Verification Team
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>
[8 Jun 2014 19:28] Valeriy Kravchuk
Run this:

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

without new line inside '%ft%', please.
[8 Jun 2014 19:31] Valeriy Kravchuk
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%';
[8 Jun 2014 19:38] MySQL Verification Team
Thank you for the feedback now I was able to repeat.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.