MySQL Error 1032 "Can't find record in table"

Question

I asked this question yesterday on dba.stackexchange.com and didn't get any responses, so I'm trying here.

I'm getting MySQL 1032 "Can't find record in 'person'" errors for some queries in my database, and I cannot resolve them.

Here's the table:

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dob` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `person_full_idx` (`last_name`,`first_name`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=4448 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The query that's failing is

SELECT * FROM person p0_
WHERE MATCH (p0_.last_name , p0_.first_name , p0_.title) AGAINST ('anne' IN BOOLEAN MODE) > 0.5
ORDER BY p0_.last_name ASC, p0_.first_name ASC, p0_.dob ASC;

If I take out any one of the order by clauses the query runs just fine. And If I change anne to anna the query runs just fine with all three order by clauses. There are some Annes in the table, about as many as there are Annas.

The MySQL error log has a bunch of these error messages each time the query fails:

2019-03-27T17:31:27.891405Z 9 [Warning] [MY-012853] [InnoDB] Using a
partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table 
`database`.`person`. Last data field length 8 bytes, key ptr now 
exceeds key end by 4 bytes. Key value in the MySQL format:
len 4; hex 05110000; asc     ;

I'm not using replication, and inserts, updates, and deletes are all successful for anne records. I dropped and recreated the fulltext index with no improvement. I dropped and reloaded the database and get the same error.

The query isn't failing in production (mysql Ver 15.1 Distrib 10.1.37-MariaDB) with the same data. As far as I can tell, it's only failing on my dev machine (mysql Ver 8.0.15 for osx10.14 on x86_64 (Homebrew)).

What should I try next?

Answer 1

As noted in the comment above, this is a known bug in MySQL 8.0: https://bugs.mysql.com/bug.php?id=93241

A temporary workaround is to increase the size of sort_buffer_size. The default sort_buffer size in MySQL 8.0 is 256KB, and the maximum value you can configure is either 2^32-1 or 2^64-1.

The error might reoccur if you run a query that matches a larger number of rows, enough that it too large for your increased sort_buffer_size.

I'd caution against increasing sort_buffer_size very large, because each thread that runs this query allocates its own sort buffer.

Suppose you increase sort_buffer_size to 1GB, and then 100 concurrent clients run the fulltext search at the same time! You could accidentally make MySQL exceed your total system memory, and you'll have no warning when it happens.