Bug #72132 Auxiliary tables for InnoDB FTS indexes are always created in shared tablespace
Submitted: 26 Mar 2014 8:00 Modified: 3 Jul 2014 17:50
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.16, 5.6.19 OS:Any
Assigned to: Daniel Price CPU Architecture:Any
Tags: fts, fulltext, IBData1, innodb_file_per_table

[26 Mar 2014 8:00] Valeriy Kravchuk
Description:
It seems that even with innodb_file_per_table set to 1 some of the tables that InnoDB creates for every FULLTEXT index are stored in shared tablespace:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3314 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.6.16-log 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> show variables like 'innodb_file_per%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.77 sec)

mysql> create table test.ft(id int primary key, details text, fulltext key(detai
ls)) engine=InnoDB;
Query OK, 0 rows affected (7.08 sec)

mysql> select name, space from information_schema.innodb_sys_tables where name l
ike 'test/FTS%';
+----------------------------------------------------+-------+
| name                                               | space |
+----------------------------------------------------+-------+
| test/FTS_000000000000002e_0000000000000059_INDEX_1 |     0 |
| test/FTS_000000000000002e_0000000000000059_INDEX_2 |     0 |
| test/FTS_000000000000002e_0000000000000059_INDEX_3 |     0 |
| test/FTS_000000000000002e_0000000000000059_INDEX_4 |     0 |
| test/FTS_000000000000002e_0000000000000059_INDEX_5 |     0 |
| test/FTS_000000000000002e_0000000000000059_INDEX_6 |     0 |
| test/FTS_000000000000002e_BEING_DELETED            |    35 |
| test/FTS_000000000000002e_BEING_DELETED_CACHE      |    36 |
| test/FTS_000000000000002e_CONFIG                   |    37 |
| test/FTS_000000000000002e_DELETED                  |    33 |
| test/FTS_000000000000002e_DELETED_CACHE            |    34 |
| test/FTS_0000000000000054_000000000000008b_INDEX_1 |     0 |
| test/FTS_0000000000000054_000000000000008b_INDEX_2 |     0 |
| test/FTS_0000000000000054_000000000000008b_INDEX_3 |     0 |
| test/FTS_0000000000000054_000000000000008b_INDEX_4 |     0 |
| test/FTS_0000000000000054_000000000000008b_INDEX_5 |     0 |
| test/FTS_0000000000000054_000000000000008b_INDEX_6 |     0 |
| test/FTS_0000000000000054_BEING_DELETED            |    67 |
| test/FTS_0000000000000054_BEING_DELETED_CACHE      |    68 |
| test/FTS_0000000000000054_CONFIG                   |    69 |
| test/FTS_0000000000000054_DELETED                  |    65 |
| test/FTS_0000000000000054_DELETED_CACHE            |    66 |
+----------------------------------------------------+-------+
22 rows in set (0.64 sec)

That tables in space 0, "FTS_<table_id_in_hex>_<index_id_in_hex>_INDEX_?" are "inverted index tables". I wonder why they are always created in shared tablespace? All these details are totally undocumented in the manual (topic for another bug report).

Having tables implicitly created in shared tablespace may become a problem to those DBA who expect it to NOT grow as they have innodb_file_per_table=1 and care about not having long active transactions and/or separate space for UNDO (via innodb_undo_directory setting etc).

How to repeat:
On fresh installation of MySQL 5.6:

set global innodb_file_per_table=1;
show variables like 'innodb_file_per%';
create table test.ft(id int primary key, details text, fulltext key(details)) engine=InnoDB;
select name, space from information_schema.innodb_sys_tables where name like 'test/FTS%';

Suggested fix:
Do something to allow placing these auxiliary tables into individual .ibf files. 

Alternatively, document current design in details. It's not enough to have only this article:

http://www.drdobbs.com/database/full-text-search-with-innodb/231902587

for years.
[26 Mar 2014 10:39] MySQL Verification Team
Hello Valeriy,

Thank you for the report.

Thanks,
Umesh
[8 Jun 2014 13:13] Valeriy Kravchuk
Same story with 5.6.19 If anybody cares):

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3314 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19-log 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> show variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Antelope  |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

mysql> drop table ft;
Query OK, 0 rows affected (0.29 sec)

mysql>  create table test.ft(id int primary key, details text, fulltext key(deta
ils)) engine=InnoDB;
Query OK, 0 rows affected (1.56 sec)

mysql> select name, space from information_schema.innodb_sys_tables where name
    -> like 'test/FTS%';
+----------------------------------------------------+-------+
| name                                               | space |
+----------------------------------------------------+-------+
| test/FTS_0000000000000206_00000000000005f5_INDEX_1 |     0 |
| test/FTS_0000000000000206_00000000000005f5_INDEX_2 |     0 |
| test/FTS_0000000000000206_00000000000005f5_INDEX_3 |     0 |
| test/FTS_0000000000000206_00000000000005f5_INDEX_4 |     0 |
| test/FTS_0000000000000206_00000000000005f5_INDEX_5 |     0 |
| test/FTS_0000000000000206_00000000000005f5_INDEX_6 |     0 |
| test/FTS_0000000000000206_BEING_DELETED            |   424 |
| test/FTS_0000000000000206_BEING_DELETED_CACHE      |   425 |
| test/FTS_0000000000000206_CONFIG                   |   426 |
| test/FTS_0000000000000206_DELETED                  |   422 |
| test/FTS_0000000000000206_DELETED_CACHE            |   423 |
+----------------------------------------------------+-------+
11 rows in set (0.00 sec)
[11 Jun 2014 18:59] Daniƫl van Eeden
I filed a related bug: Bug #72961
[3 Jul 2014 17:50] Daniel Price
FTS index tables should be created in their own individual tablespaces when innodb_file_per_table=ON, which was the case in MySQL 5.6.4. Due to a regression introduced in MySQL 5.6.5, FTS index tables were created in the system tablespace. This bug is fixed in 5.6.20 and MySQL 5.7.5.

The incorrect behaviour is noted in the FULLTEXT Indexes section of the reference manual and in the "system tablespace" glossary entry.

http://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_system_tablespace
http://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_system_tablespace

The revised content should appear soon, with the next published documentation build.

Thank you for the bug report.
[6 Aug 2014 17:34] Laurynas Biveinis
$ bzr log -n0 -r 5943
------------------------------------------------------------
revno: 5943
committer: Jimmy Yang <jimmy.yang@oracle.com>
branch nick: mysql-5.6
timestamp: Fri 2014-05-16 10:21:05 +0800
message:
  Fix Bug #18635485 - INNODB_FTS: FTS INDEX TABLE SHOULD HAVE THEIR OWN SPACE ID 
        
  rb://5242 approved by Kevin Lewis
[6 Aug 2014 17:34] Laurynas Biveinis
$ bzr log -n0 -r 5945
------------------------------------------------------------
revno: 5945
committer: Jimmy Yang <jimmy.yang@oracle.com>
branch nick: mysql-5.6
timestamp: Fri 2014-05-16 14:19:52 +0800
message:
  Fix a regex expression for innodb-wl5980-alter.test. Needed for bug #18635485
  testing change