title: MySQL Error 1032 "Can't find record in table"
body: <p><em>I asked this question yesterday on <a href="https://dba.stackexchange.com/posts/233294">dba.stackexchange.com</a> and didn't get any responses, so I'm trying here.</em></p>
  
  <p>I'm getting MySQL 1032 "Can't find record in 'person'" errors for some queries in my database, and I cannot resolve them.</p>
  
  <p>Here's the table:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  
  <p>The query that's failing is </p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM person p0_
  WHERE MATCH (p0_.last_name , p0_.first_name , p0_.title) AGAINST ('anne' IN BOOLEAN MODE) &gt; 0.5
  ORDER BY p0_.last_name ASC, p0_.first_name ASC, p0_.dob ASC;
  </code></pre>
  
  <p>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.</p>
  
  <p>The MySQL error log has a bunch of these error messages each time the query fails:</p>
  
  <pre><code>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     ;
  </code></pre>
  
  <p>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. </p>
  
  <p>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)).</p>
  
  <p>What should I try next?</p>