Bug #98098 Attempting to add a JSON array index can fail with "Incorrect key file"
Submitted: 31 Dec 2019 0:47 Modified: 5 Feb 2020 15:03
Reporter: Benjamin Morel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:8.0.18, 8.0.17 OS:Fedora (31)
Assigned to: CPU Architecture:x86 (i7-7700HQ)

[31 Dec 2019 0:47] Benjamin Morel 
Description:
When adding an index to an existing MySQL table filled with enough records, MySQL fails with the following error:

> Incorrect key file for table 'test'; try to repair it

The threshold is 40570 records in my case: with 40570 records, MySQL happily adds the index; with 40571 records, the error occurs.

Full reproduce script: https://gofile.io/?c=cNEPjV

How to repeat:
CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  catIds JSON NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO test (id, catIds) VALUES (1037011, '[12, 34, 56]');

... more inserts (see full reproduce script)

ALTER TABLE test ADD INDEX test_categories ((CAST(catIds AS UNSIGNED ARRAY)));
[31 Dec 2019 7:50] MySQL Verification Team 
Hello Benjamin Morel,

Thank you for the report and test case.

regards,
Umesh
[5 Feb 2020 15:03] Daniel Price 
Posted by developer:
 
Fixed as of the upcoming 8.0.20 release, and here's the changelog entry:

Adding an index on a column cast as a JSON array on a table with a
specific number of records failed with an "Incorrect key file for table"
error.