Description:
After setting the value of innodb_ft_aux_table to the fully-qualified name of an InnoDB table which contains a full-text index, the documentation claims that information about the FT index should then become available by querying I_S.innodb_ft_config.
However, it appears that some of these values are never actually updated - in particular, these do not seem to change regardless of row insertions, row deletions, or many iterations of OPTIMIZE TABLE after running SET GLOBAL innodb_optimize_fulltext_only=1;
last_optimized_word, deleted_doc_count, total_word_count, optimize_start_time
optimize_end_time
I assume that I am using the feature correctly; if I'm not, then at a minimum I think this indicates the need for more explicit documentation as to how the I_S.innodb_ft_config table works.
How to repeat:
USE test;
CREATE TABLE innodb_ft_config_bug (
`FTS_DOC_ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
details TEXT,
FULLTEXT KEY (title, details)
);
mysql> select * from information_schema.innodb_ft_config;
Empty set (0.00 sec)
mysql> SET GLOBAL innodb_ft_aux_table='test/innodb_ft_config_bug';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from information_schema.innodb_ft_config;
+---------------------------+-------+
| KEY | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180 |
| synced_doc_id | 0 |
| last_optimized_word | |
| deleted_doc_count | 0 |
| total_word_count | |
| optimize_start_time | |
| optimize_end_time | |
| stopword_table_name | |
| use_stopword | 1 |
| table_state | 0 |
+---------------------------+-------+
mysql> SET GLOBAL innodb_optimize_fulltext_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into innodb_ft_config_bug (title, details) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...') , ('How To Use MySQL Well','After you went through a they...') ;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from information_schema.innodb_ft_config;
+---------------------------+-------+
| KEY | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180 |
| synced_doc_id | 0 |
| last_optimized_word | |
| deleted_doc_count | 0 |
| total_word_count | |
| optimize_start_time | |
| optimize_end_time | |
| stopword_table_name | |
| use_stopword | 1 |
| table_state | 0 |
+---------------------------+-------+
10 rows in set (0.00 sec)
mysql> select count(*) from information_schema.innodb_ft_index_table;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> optimize table innodb_ft_config_bug;
+---------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------------+----------+----------+----------+
| test.innodb_ft_config_bug | optimize | status | OK |
+---------------------------+----------+----------+----------+
1 row in set (0.01 sec)
mysql> select count(*) from information_schema.innodb_ft_index_table;
+----------+
| count(*) |
+----------+
| 11 |
+----------+
1 row in set (0.00 sec)
mysql> select * from information_schema.innodb_ft_config;
+---------------------------+-------+
| KEY | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180 |
| synced_doc_id | 3 |
| last_optimized_word | |
| deleted_doc_count | 0 |
| total_word_count | |
| optimize_start_time | |
| optimize_end_time | |
| stopword_table_name | |
| use_stopword | 1 |
| table_state | 0 |
+---------------------------+-------+
10 rows in set (0.00 sec)
It's clear that my FTS index was updated when I ran OPTIMIZE TABLE, but why weren't any of the diagnostic/status counters also updated? I've tried this with much larger data sets and with both utf8 and latin1 character sets and gotten the same result.
mysql> show global variables like 'innodb_ft%';
+---------------------------------+---------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------+
| innodb_ft_aux_table | test/innodb_ft_config_bug |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 4 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_user_stopword_table | |
+---------------------------------+---------------------------+
10 rows in set (0.00 sec)
mysql> show global variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 1.2.10 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.10 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
mysql> show create table innodb_ft_config_bug\G
*************************** 1. row ***************************
Table: innodb_ft_config_bug
Create Table: CREATE TABLE `innodb_ft_config_bug` (
`FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`details` text,
PRIMARY KEY (`FTS_DOC_ID`),
FULLTEXT KEY `title` (`title`,`details`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
Description: After setting the value of innodb_ft_aux_table to the fully-qualified name of an InnoDB table which contains a full-text index, the documentation claims that information about the FT index should then become available by querying I_S.innodb_ft_config. However, it appears that some of these values are never actually updated - in particular, these do not seem to change regardless of row insertions, row deletions, or many iterations of OPTIMIZE TABLE after running SET GLOBAL innodb_optimize_fulltext_only=1; last_optimized_word, deleted_doc_count, total_word_count, optimize_start_time optimize_end_time I assume that I am using the feature correctly; if I'm not, then at a minimum I think this indicates the need for more explicit documentation as to how the I_S.innodb_ft_config table works. How to repeat: USE test; CREATE TABLE innodb_ft_config_bug ( `FTS_DOC_ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, details TEXT, FULLTEXT KEY (title, details) ); mysql> select * from information_schema.innodb_ft_config; Empty set (0.00 sec) mysql> SET GLOBAL innodb_ft_aux_table='test/innodb_ft_config_bug'; Query OK, 0 rows affected (0.00 sec) mysql> select * from information_schema.innodb_ft_config; +---------------------------+-------+ | KEY | VALUE | +---------------------------+-------+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 0 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize_end_time | | | stopword_table_name | | | use_stopword | 1 | | table_state | 0 | +---------------------------+-------+ mysql> SET GLOBAL innodb_optimize_fulltext_only=1; Query OK, 0 rows affected (0.00 sec) mysql> insert into innodb_ft_config_bug (title, details) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...') , ('How To Use MySQL Well','After you went through a they...') ; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.innodb_ft_config; +---------------------------+-------+ | KEY | VALUE | +---------------------------+-------+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 0 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize_end_time | | | stopword_table_name | | | use_stopword | 1 | | table_state | 0 | +---------------------------+-------+ 10 rows in set (0.00 sec) mysql> select count(*) from information_schema.innodb_ft_index_table; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> optimize table innodb_ft_config_bug; +---------------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------+----------+----------+----------+ | test.innodb_ft_config_bug | optimize | status | OK | +---------------------------+----------+----------+----------+ 1 row in set (0.01 sec) mysql> select count(*) from information_schema.innodb_ft_index_table; +----------+ | count(*) | +----------+ | 11 | +----------+ 1 row in set (0.00 sec) mysql> select * from information_schema.innodb_ft_config; +---------------------------+-------+ | KEY | VALUE | +---------------------------+-------+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 3 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize_end_time | | | stopword_table_name | | | use_stopword | 1 | | table_state | 0 | +---------------------------+-------+ 10 rows in set (0.00 sec) It's clear that my FTS index was updated when I ran OPTIMIZE TABLE, but why weren't any of the diagnostic/status counters also updated? I've tried this with much larger data sets and with both utf8 and latin1 character sets and gotten the same result. mysql> show global variables like 'innodb_ft%'; +---------------------------------+---------------------------+ | Variable_name | Value | +---------------------------------+---------------------------+ | innodb_ft_aux_table | test/innodb_ft_config_bug | | innodb_ft_cache_size | 8000000 | | innodb_ft_enable_diag_print | OFF | | innodb_ft_enable_stopword | ON | | innodb_ft_max_token_size | 84 | | innodb_ft_min_token_size | 4 | | innodb_ft_num_word_optimize | 2000 | | innodb_ft_server_stopword_table | | | innodb_ft_sort_pll_degree | 2 | | innodb_ft_user_stopword_table | | +---------------------------------+---------------------------+ 10 rows in set (0.00 sec) mysql> show global variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 1.2.10 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.10 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.5 | +-------------------------+------------------------------+ 7 rows in set (0.00 sec) mysql> show create table innodb_ft_config_bug\G *************************** 1. row *************************** Table: innodb_ft_config_bug Create Table: CREATE TABLE `innodb_ft_config_bug` ( `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `details` text, PRIMARY KEY (`FTS_DOC_ID`), FULLTEXT KEY `title` (`title`,`details`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1