Invalid InnoDB FTS Doc ID during INSERT

[monty solomon]

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.

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 AUTO_INCREMENT=128012416 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

[monty solomon (monty+launchpad)]

mysql Ver 14.14 Distrib 5.6.25-73.1, for Linux (x86_64) using 6.0

Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

CentOS release 6.7 (Final)

[monty solomon (monty+launchpad)]

I reported upstream bug http://bugs.mysql.com/bug.php?id=80261

[monty solomon (monty+launchpad)]

Upstream was unable to reproduce the bug.

It may be specific to Percona Server or related to my my.cnf settings.

Are you able to reproduce the bug using the example I provided?

[monty solomon (monty+launchpad)]

The problem occurs when there is a large auto_increment specified on the table creation.

[monty solomon (monty+launchpad)]

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

[Laurynas Biveinis (laurynas-biveinis)]

Upstream documented fixed in 5.6.31/5.7.13/5.8.0.

[monty solomon (monty+launchpad)]

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

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

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

Before MySQL 5.7.13, the permitted gap between the largest used FTS_DOC_ID value and new FTS_DOC_ID value is 10000. In MySQL 5.7.13 and later, the permitted gap is 65535.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1694