Bug #80261 Invalid InnoDB FTS Doc ID during INSERT
Submitted: 4 Feb 2016 6:48 Modified: 5 Jun 2016 0:11
Reporter: monty solomon Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S1 (Critical)
Version:5.6.25, 5.6.29, 5.7.10, 5.7.11 OS:CentOS
Assigned to: CPU Architecture:Any

[4 Feb 2016 6:48] monty solomon
Description:
Attempts to insert into an InnoDB table that contains a FULLTEXT KEY and a FTS_DOC_ID column fail when the FTS_DOC_ID is not specified.

How to repeat:
CREATE TABLE `fts` (
  `id` varchar(36) NOT NULL,
  `reason` varchar(500) NOT NULL,
  `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),
  FULLTEXT KEY `reason` (`reason`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

mysql> INSERT IGNORE INTO fts (id, reason) VALUES (UUID(), 'unknown');
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID

mysql> INSERT INTO fts (id, reason) VALUES (UUID(), 'unknown');
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID
[4 Feb 2016 7:19] Valeriy Kravchuk
Either something depends on my.cnf settings or this is a regression. Works for me in 5.6.23:

...

mysql> INSERT IGNORE INTO fts (id, reason) VALUES (UUID(), 'unknown');
Query OK, 1 row affected, 1 warning (0.72 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1592
Message: Unsafe statement written to the binary log using statement format since
 BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system functio
n that may return a different value on the slave.
1 row in set (0.00 sec)

mysql> INSERT INTO fts (id, reason) VALUES (UUID(), 'unknown');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1592
Message: Unsafe statement written to the binary log using statement format since
 BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system functio
n that may return a different value on the slave.
1 row in set (0.00 sec)

mysql> select * from fts;
+--------------------------------------+---------+------------+
| id                                   | reason  | FTS_DOC_ID |
+--------------------------------------+---------+------------+
| 395869ca-cb0f-11e5-9d5b-00123f76eef8 | unknown |          1 |
| 4655a040-cb0f-11e5-9d5b-00123f76eef8 | unknown |          2 |
+--------------------------------------+---------+------------+
2 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.23-log |
+------------+
1 row in set (0.02 sec)
[4 Feb 2016 9:31] Umesh Shastry
Thank you for the report.
I'm not seeing any issues with 5.6.28 as well.

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28: bin/mysql -uroot -S run/master.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.28-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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> drop table if exists fts;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `fts` (
    ->   `id` varchar(36) NOT NULL,
    ->   `reason` varchar(500) NOT NULL,
    ->   `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),
    ->   FULLTEXT KEY `reason` (`reason`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT IGNORE INTO fts (id, reason) VALUES (UUID(), 'unknown');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT IGNORE INTO fts (id, reason) VALUES (UUID(), 'unknown');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
1 row in set (0.00 sec)

mysql> select * from fts;
+--------------------------------------+---------+------------+
| id                                   | reason  | FTS_DOC_ID |
+--------------------------------------+---------+------------+
| daf1407a-cb21-11e5-b2e2-0010e05f3e06 | unknown |          1 |
| dcb64214-cb21-11e5-b2e2-0010e05f3e06 | unknown |          2 |
+--------------------------------------+---------+------------+
2 rows in set (0.00 sec)

mysql>

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Umesh
[5 Feb 2016 19:25] monty solomon
The problem occurs when there is a large auto_increment specified on the table creation.

mysql> CREATE TABLE `fts` (   `id` varchar(36) NOT NULL,   `reason` varchar(500) NOT NULL,   `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`),   UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),   FULLTEXT KEY `reason` (`reason`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 AUTO_INCREMENT=50000;
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT IGNORE INTO fts (id, reason) VALUES (UUID(), 'unknown');
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID
mysql> 

In the error log

InnoDB: Doc ID 50000 is too big. Its difference with largest used Doc ID 0 cannot exceed or equal to 10000
[5 Feb 2016 19:26] monty solomon
Umesh
[5 Feb 2016 19:26] monty solomon
Umesh
[5 Feb 2016 19:27] monty solomon
Umesh,

I am unable to change the status of the bug to open. My only choices are can't repeat and closed.
[5 Feb 2016 20:48] monty solomon
On a slave I converted a large MyISAM FTS table to InnoDB add added the FTS_DOC_ID column and index. The slave SQL failed trying to insert a row.

InnoDB: Doc ID 128012412 is too big. Its difference with largest used Doc ID 127998061 cannot exceed or equal to 10000
2016-02-04 05:21:42 18349 [ERROR] Slave SQL: Error 'Invalid InnoDB FTS Doc ID' on query. Error_code: 182
2016-02-04 05:21:42 18349 [Warning] Slave: Invalid InnoDB FTS Doc ID Error_code: 182
2016-02-04 05:21:42 18349 [Warning] Slave: Got error 182 from storage engine Error_code: 1030
2016-02-04 05:21:42 18349 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'bin.012487' position 6505648
[8 Feb 2016 3:15] Umesh Shastry
Thank you for the feedback.

Thanks,
Umesh
[8 Feb 2016 3:15] Umesh Shastry
-- 5.6.29

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.29: bin/mysql -uroot -S /tmp/mysql_ushastry.sock  test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> drop table if exists fts;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `fts` (
    ->   `id` varchar(36) NOT NULL,
    ->   `reason` varchar(500) NOT NULL,
    ->   `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),
    ->   FULLTEXT KEY `reason` (`reason`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT IGNORE INTO fts (id, reason) VALUES (UUID(), 'unknown');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT IGNORE INTO fts (id, reason) VALUES (UUID(), 'unknown');
Query OK, 1 row affected (0.00 sec)

mysql> select * from fts;
+--------------------------------------+---------+------------+
| id                                   | reason  | FTS_DOC_ID |
+--------------------------------------+---------+------------+
| dcaa84b0-ce11-11e5-8d44-0010e05f3e06 | unknown |          1 |
| ddec9e6c-ce11-11e5-8d44-0010e05f3e06 | unknown |          2 |
+--------------------------------------+---------+------------+
2 rows in set (0.00 sec)

-- with AUTO_INCREMENT=50000

mysql> drop table if exists fts;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `fts` (   `id` varchar(36) NOT NULL,   `reason` varchar(500) NOT NULL,   `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`),   UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),   FULLTEXT KEY `reason` (`reason`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 AUTO_INCREMENT=50000;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT IGNORE INTO fts (id, reason) VALUES (UUID(), 'unknown');
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID
mysql>
[8 Feb 2016 3:15] Umesh Shastry
-- 5.7.10

[root@cluster-repo ~]# mysql -uroot -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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 `fts` (   `id` varchar(36) NOT NULL,   `reason` varchar(500) NOT NULL,   `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`),   UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),   FULLTEXT KEY `reason` (`reason`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 AUTO_INCREMENT=50000;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT IGNORE INTO fts (id, reason) VALUES (UUID(), 'unknown');
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID
mysql> show errors;
+-------+------+-----------------------------------+
| Level | Code | Message                           |
+-------+------+-----------------------------------+
| Error |  182 | Invalid InnoDB FTS Doc ID         |
| Error | 1030 | Got error 182 from storage engine |
+-------+------+-----------------------------------+
2 rows in set (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------+
| Level | Code | Message                           |
+-------+------+-----------------------------------+
| Error |  182 | Invalid InnoDB FTS Doc ID         |
| Error | 1030 | Got error 182 from storage engine |
+-------+------+-----------------------------------+
2 rows in set (0.00 sec)

mysql>
[4 Mar 2016 6:38] Umesh Shastry
-- innodb affected

DROP TABLE IF EXISTS `fts_innodb`;
CREATE TABLE `fts_innodb` (   `id` varchar(36) NOT NULL,   `reason` varchar(500) NOT NULL,   `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`),   UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),   FULLTEXT KEY `reason` (`reason`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 AUTO_INCREMENT=50000;

INSERT IGNORE INTO fts_innodb (id, reason) VALUES (UUID(), 'unknown');
SELECT * from fts_innodb;

- may be one can use ALTER... ENGINE=MyISAM instead of below 
DROP TABLE IF EXISTS `fts_myisam`;
CREATE TABLE `fts_myisam` (   `id` varchar(36) NOT NULL,   `reason` varchar(500) NOT NULL,   `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`),   UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),   FULLTEXT KEY `reason` (`reason`) ) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=50000;

INSERT IGNORE INTO fts_myisam (id, reason) VALUES (UUID(), 'unknown');
SELECT * from fts_myisam;

-- 5.7.11

[root@cluster-repo ~]# mysql -uroot -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 157
Server version: 5.7.11-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> -- broken in innodb

mysql> DROP TABLE IF EXISTS `fts_innodb`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE `fts_innodb` (   `id` varchar(36) NOT NULL,   `reason` varchar(500) NOT NULL,   `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`),   UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),   FULLTEXT KEY `reason` (`reason`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 AUTO_INCREMENT=50000;

INSERT IGNORE INTO fts_innodb (id, reason) VALUES (UUID(), 'unknown');
SELECT * from fts_innodb;
Query OK, 0 rows affected (0.12 sec)

mysql>
mysql> INSERT IGNORE INTO fts_innodb (id, reason) VALUES (UUID(), 'unknown');
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID
mysql> SELECT * from fts_innodb;
Empty set (0.00 sec)

mysql> -- works for myisam

mysql> DROP TABLE IF EXISTS `fts_myisam`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `fts_myisam` (   `id` varchar(36) NOT NULL,   `reason` varchar(500) NOT NULL,   `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`),   UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),   FULLTEXT KEY `reason` (`reason`) ) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=50000;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT IGNORE INTO fts_myisam (id, reason) VALUES (UUID(), 'unknown');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * from fts_myisam;
+--------------------------------------+---------+------------+
| id                                   | reason  | FTS_DOC_ID |
+--------------------------------------+---------+------------+
| 182436dc-e3a1-11e5-a4e0-00055d4a731d | unknown |      50000 |
+--------------------------------------+---------+------------+
1 row in set (0.00 sec)

mysql>
[9 Mar 2016 22:25] monty solomon
Since the InnoDB FTS is not working we are going to shut down the server. Do you need anything else before I shut down the server?
[15 Mar 2016 19:57] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.6.31, 5.7.13, 5.8.0 release, and here's the changelog entry:

An INSERT operation on a table with a FULLTEXT index and FTS_DOC_ID
column failed because the inserted FTS_DOC_ID value exceeded the permitted
gap between consecutive FTS_DOC_ID values. To avoid this problem, the
permitted gap between the largest used FTS_DOC_ID value and new FTS_DOC_ID
value was raised from 10000 to 65535. 

Thank you for the bug report.
[18 Mar 2016 2:28] monty solomon
The revised documentation states that the permitted gap is currently 10000 but the error message from InnoDB states that the value cannot exceed or equal 10000.

Documentation

Before MySQL 5.6.31, the permitted gap between the largest used FTS_DOC_ID value and new FTS_DOC_ID value is 10000.

Error message

InnoDB: Doc ID 50000 is too big. Its difference with largest used Doc ID 0 cannot exceed or equal to 10000
[15 Apr 2016 8:42] Umesh Shastry
Please wait for 5.6.31 and report us back if you see any issues.

Thanks,
Umesh
[5 Jun 2016 0:10] monty solomon
The release notes for MySQL 5.7.13 state this bug was fixed but do not reference this bug number.
[5 Jun 2016 0:11] monty solomon
The release notes for MySQL 5.6.31 state this bug was fixed but do not reference this bug number.