MySQL 错误 1032 "Can't find record in table" [英] MySQL Error 1032 "Can't find record in table"

问题描述

我昨天在 dba.stackexchange.com 上问了这个问题，但没有得到任何回复，所以我正在这里尝试.

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

对于我的数据库中的某些查询，我收到 MySQL 1032无法在‘person’中找到记录"错误，但我无法解决这些错误.

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

这是表:

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;

失败的查询是

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;

如果我取出 order by 子句中的任何一个，查询运行得很好.如果我将 anne 更改为 anna，则查询在所有三个 order by 子句中都可以正常运行.桌子上有一些安妮，大约和安娜一样多.

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.

每次查询失败时，MySQL 错误日志都会有一堆这样的错误信息:

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     ;

我没有使用复制，对于 anne 记录，插入、更新和删除都是成功的.我删除并重新创建了全文索引，但没有任何改进.我删除并重新加载数据库并得到相同的错误.

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.

该查询在具有相同数据的生产(mysql Ver 15.1 Distrib 10.1.37-MariaDB)中没有失败.据我所知，它只是在我的开发机器上失败(mysql Ver 8.0.15 for osx10.14 on x86_64 (Homebrew)).

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?

推荐答案

如上述评论中所述，这是 MySQL 8.0 中的一个已知错误:https://bugs.mysql.com/bug.php?id=93241

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

临时解决方法是增加 sort_buffer_size 的大小.MySQL 8.0 中的默认 sort_buffer 大小 为 256KB，您可以配置的最大值为 2^32-1 或 2^64-1.

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.

如果您运行匹配大量行的查询，该错误可能会再次发生，对于增加的sort_buffer_size 来说，它太大了.

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.

我警告不要将 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.

假设您将 sort_buffer_size 增加到 1GB，然后 100 个并发客户端同时运行全文搜索！您可能会意外地使 MySQL 超出您的系统总内存，并且在发生这种情况时您不会收到任何警告.

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.

这篇关于MySQL 错误 1032 "Can't find record in table"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！