MySQL错误1032“无法在表中找到记录" [英] 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)具有相同的数据.据我所知，它只是在我的开发机器上失败(对于x86_64(Homebrew)上的osx10.14的MySQL Ver 8.0.15).

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中的一个已知错误:

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“无法在表中找到记录"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！