MySQL Bugs: #86460: Deleted DOCID are not maintained during OPTIMIZE of InnoDB FULLTEXT tables

Bug #86460	Deleted DOCID are not maintained during OPTIMIZE of InnoDB FULLTEXT tables
Submitted:	25 May 2017 14:19	Modified:	31 May 2017 14:44
Reporter:	Michael Riediger	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	fulltext, innodb, Optimize

Description:
The documentation indicates that by setting innodb_optimize_fulltext_only=ON and running an optimize on an innodb table with a fulltext field, deleted docids will me formally removed rather than maintaining the INNODB_FT_DELETED "blacklist".

"When OPTIMIZE TABLE is run, ..., and DOC_IDs are removed from the INNODB_FT_DELETED table. "

Example 14.5 detailed @ https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-fulltext_index-tables.ht... is not reproducible with respect to the INNODB_FT_DELETED table.

I have several production examples of tables with millions of deleted docids that can't be removed.  The interm solution is to to an actual ALTER TABLE ... ENGINE=InnoDB to rewrite the table and the fulltext indexes.

How to repeat:
> create database ft_test;
Query OK, 1 row affected (0.00 sec)

> use ft_test;
Database changed
>  CREATE TABLE articles (
    ->       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->       title VARCHAR(200),
    ->       body TEXT,
    ->       FULLTEXT (title,body)
    ->      ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

> 
> INSERT INTO articles (title,body) VALUES
    ->     ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ->     ('How To Use MySQL Well','After you went through a ...'),
    ->     ('Optimizing MySQL','In this tutorial we will show ...'),
    ->     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ->     ('MySQL vs. YourSQL','In the following database comparison ...'),
    ->     ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

> SET GLOBAL innodb_ft_aux_table = 'ft_test/articles';
Query OK, 0 rows affected (0.00 sec)

> SET GLOBAL innodb_optimize_fulltext_only=ON;
Query OK, 0 rows affected (0.00 sec)

> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
Empty set (0.00 sec)

> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED;
Empty set (0.00 sec)

> DELETE FROM ft_test.articles ORDER BY RAND() LIMIT 3;
Query OK, 3 rows affected (0.01 sec)

> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      4 |
+--------+
3 rows in set (0.00 sec)

> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED;
Empty set (0.00 sec)

> OPTIMIZE TABLE ft_test.articles;
+------------------+----------+----------+----------+
| Table            | Op       | Msg_type | Msg_text |
+------------------+----------+----------+----------+
| ft_test.articles | optimize | status   | OK       |
+------------------+----------+----------+----------+
1 row in set (0.03 sec)

> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      4 |
+--------+
3 rows in set (0.00 sec)

> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      4 |
+--------+
3 rows in set (0.00 sec)

> show variables like 'ver%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.7.17-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

Suggested fix:
Have MySQL perform as documented.

Verified as described. Thanks for your report.

mysql> use ft_test;
Database changed
mysql> CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.36 sec)

mysql> INSERT INTO articles (title,body) VALUES  ('MySQL Tutorial','DBMS stands for DataBase ...'),  ('How To Use MySQL Well','After you went through a ...'), ('Optimizing MySQL','In this tutorial we will show ...'), ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SET GLOBAL innodb_ft_aux_table = 'ft_test/articles';
Query OK, 0 rows affected (0.00 sec)

mysql>  SET GLOBAL innodb_optimize_fulltext_only=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
Empty set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED;
Empty set (0.00 sec)

mysql> DELETE FROM ft_test.articles ORDER BY RAND() LIMIT 3;
Query OK, 3 rows affected (0.04 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      6 |
+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      6 |
+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      6 |
+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      6 |
+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED;
Empty set (0.00 sec)

mysql> OPTIMIZE TABLE ft_test.articles;
+------------------+----------+----------+----------+
| Table            | Op       | Msg_type | Msg_text |
+------------------+----------+----------+----------+
| ft_test.articles | optimize | status   | OK       |
+------------------+----------+----------+----------+
1 row in set (0.23 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      6 |
+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      6 |
+--------+
3 rows in set (0.00 sec)

mysql> show variables like 'ver%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.7.18                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.5               |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql>