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: | |
| 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 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.
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` (