Bug #83397 INSERT INTO ... SELECT FROM ... fails if source has > 65535 rows on FTS

• Submitted: 16 Oct 2016 10:58
• Modified: 17 Oct 2016 6:57
• Reporter: Daniël van Eeden (OCA)
• Status: Verified
• Category: MySQL Server: FULLTEXT search
• Severity: S3 (Non-critical)
• Version: 5.7.15, 5.7.16
• OS: Any
• CPU Architecture: Any
• Tags: fts, fulltext, insert select

[16 Oct 2016 10:58] Daniël van Eeden

Description:
INSERT INTO ... SELECT FROM ... fails if source has > 65535 rows and target has an FTS Doc ID.

There are two issues here:
1. The insert select fails
2. The auto increment bump is not rolled back, which causes all subsequent inserts to fail

Related:
Bug #80261 	Invalid InnoDB FTS Doc ID during INSERT

How to repeat:
mysql-5.7.15 [test] > insert into phone(name,phonenr) values('test 0001', '12345');
Query OK, 1 row affected (0.00 sec)

mysql-5.7.15 [test] > show create table phone\G
*************************** 1. row ***************************
       Table: phone
Create Table: CREATE TABLE `phone` (
  `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `phonenr` varchar(255) NOT NULL,
  PRIMARY KEY (`FTS_DOC_ID`),
  FULLTEXT KEY `ngram_phone` (`phonenr`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB AUTO_INCREMENT=113161 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql-5.7.15 [test] > select count(*) from phone;
+----------+
| count(*) |
+----------+
|    94289 |
+----------+
1 row in set (0.01 sec)

mysql-5.7.15 [test] > insert into phone(name,phonenr) select name,phonenr from phone;
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID
mysql-5.7.15 [test] > insert into phone(name,phonenr) values('test 0001', '12345');
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID

Suggested fix:
For issue 2:
1. try to detect this failure before increasing the autoinc nr.
2. rollback the autoinc increase if possible
3. maybe auto reset the autoinc id on error 182 to max(FTS_DOC_ID)

For issue 1:
1. Maybe disable the check for insert select and other batch operations
2. Maybe do the insert select in batches < 65535

[16 Oct 2016 12:37] Peter Laursen

Do you know this?
https://bugs.mysql.com/bug.php?id=80261

"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."

-- Peter
-- not a MySQL/Oracle person

[17 Oct 2016 6:57] MySQL Verification Team

Hello Daniël,

Thank you for the report.
Observed this with 5.7.16 build.

Thanks,
Umesh

[17 Oct 2016 6:57] MySQL Verification Team

-- 5.7.16

mysql> DROP TABLE IF EXISTS phone;
CREATE TABLE `phone` (
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `phone` (
    ->   `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(255) NOT NULL,
    ->   `phonenr` varchar(255) NOT NULL,
    ->   PRIMARY KEY (`FTS_DOC_ID`),
    ->   FULLTEXT KEY `ngram_phone` (`phonenr`) /*!50100 WITH PARSER `ngram` */
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

set @id:=0;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> set @id:=0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>  insert into `phone` values
    ->   (@id:=@id+1,CONCAT('test',@id:=@id+1),CONCAT('test',@id:=@id+1))
    -> , (@id:=@id+1,CONCAT('test',@id:=@id+1),CONCAT('test',@id:=@id+1))
    -> , (@id:=@id+1,CONCAT('test',@id:=@id+1),CONCAT('test',@id:=@id+1))
    -> , (@id:=@id+1,CONCAT('test',@id:=@id+1),CONCAT('test',@id:=@id+1));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql>  insert into `phone`(`FTS_DOC_ID`,`name`,`phonenr`)
    ->    select  @id:=@id+1,CONCAT('test',@id:=@id+1),CONCAT('test',@id:=@id+1)  from
    ->     `phone` k1, `phone` k2, `phone` k3, `phone` k4,`phone` k5,`phone` k6, `phone` k7, `phone` k8, `phone` k9,
    -> `phone` k0,`phone` ka, `phone` kb, `phone` kc, `phone` kd limit  50000;
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID
mysql>
mysql> select count(*) from phone;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> insert into phone(name,phonenr) select name,phonenr from phone;
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID
mysql> insert into phone(name,phonenr) values('test 0001', '12345');
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID
mysql>
mysql>