How to Resolving MySQL Error 1032 “Can’t find record in table

MySQL is a powerful and widely used relational database management system. However, like any software, it can encounter issues that leave developers scratching their heads. One such issue is the MySQL 1032 error, which displays the message “Can’t Find Record in ‘global_priv’.” In this blog post, we’ll explore a specific instance of this error and discuss possible solutions.

The Scenario:

A developer posted a question on dba.stackexchange.com, seeking help with a MySQL 1032 error that they were encountering in their database. Despite the efforts, they had not received a response to their query, so they turned to the broader community for assistance. Let’s delve into the details of their problem.

The Problem Statement:

The developer was facing MySQL 1032 errors in certain queries in their database, specifically when executing a query against a table named ‘person’. The table had the following structure:

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 problematic query was as follows:

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;

Interestingly, removing any one of the ‘ORDER BY’ clauses allowed the query to run successfully. Additionally, changing ‘anne’ to ‘anna’ also resolved the issue. It’s worth noting that both ‘anne’ and ‘anna’ existed in the ‘person’ table.

The MySQL error log contained messages like:

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 ;

Troubleshooting Steps:

1. Bug Report Analysis: The initial step was to investigate whether this issue was a known bug. Indeed, it was found to be a known bug in MySQL 8.0, documented under Bug ID 93241 (https://bugs.mysql.com/bug.php?id=93241).
2. Temporary Workaround: While waiting for a permanent fix, a temporary workaround was suggested. This involved increasing the size of the sort_buffer_size. In MySQL 8.0, the default sort_buffer_size is 256KB, and you have the flexibility to configure it up to either 2^32-1 or 2^64-1. By increasing this value, the query execution could be successfully completed. It’s crucial to note that increasing sort_buffer_size should be done with caution, as each thread executing the query allocates its own sort buffer. Excessive memory allocation can lead to system instability.

Conclusion:

The MySQL 1032 error, “Can’t Find Record in ‘global_priv’,” can be a challenging issue to resolve. In this particular case, the problem was attributed to a bug in MySQL 8.0. While awaiting a permanent fix, increasing the sort_buffer_size provided a viable workaround. However, it’s essential to exercise caution when adjusting memory-related settings to avoid unintended consequences.

Remember that software issues can be complex, and solutions may vary depending on the specific circumstances. Always keep an eye on official bug reports and community discussions for the latest updates and insights into troubleshooting such errors in MySQL.

In conclusion, effective problem-solving and a thorough understanding of your database system are key to resolving issues like the MySQL 1032 error and ensuring the smooth operation of your applications.