[MDEV-26253] ERROR 1032 (HY000): Can't find record with MyISAM, (too?) large key and DISABLE KEYS Created: 2021-07-27  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Server, Storage Engine - MyISAM
Affects Version/s: 10.4.19, 10.4, 10.5, 10.6, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Martijn Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-25102 Restore Mariadb dump cannot be restor... Confirmed
Problem/Incident
is caused by MDEV-371 Unique indexes for blobs Closed

 Description   

This runs just fine:

CREATE TABLE `test` (
 
  `id` int unsigned NOT NULL,
 
  `foo` varchar(255) NOT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `foo_key` (`foo`)
 
) ENGINE=MyISAM;
 
INSERT INTO `test` VALUES (1,'dummy');

However, when first disabling keys (commonly seen when using mysqldump) the INSERT query fails with an ERROR 1032 (HY000): Can't find record in 'test':

DROP TABLE IF EXISTS `test`;
 
CREATE TABLE `test` (
 
  `id` int unsigned NOT NULL,
 
  `foo` varchar(255) NOT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `foo_key` (`foo`)
 
) ENGINE=MyISAM;
 
ALTER TABLE `test` DISABLE KEYS;
 
INSERT INTO `test` VALUES (1,'dummy');

Character set being used is utf8mb4

If you use the ARIA storage engine, the CREATE statement will fail with ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes (varchar(255) in utf8mb4 is 1020 bytes).
If you use the InnoDB storage engine, there is no issue.
If you change the column foo to a varchar(250), it works fine as well.

 Comments   
Comment by Martijn [ 2021-07-27 ]

Possible the same issue as MDEV-25102

Comment by Elena Stepanova [ 2022-01-07 ]

Thanks for the report. Reproducible as described, yet another unique blob problem.

DROP TABLE IF EXISTS `test`;
 
CREATE TABLE `test` (
 
  `foo` varchar(255) NOT NULL,
 
  UNIQUE KEY `foo_key` (`foo`)
 
) ENGINE=MyISAM CHARACTER SET utf8mb4;
 
ALTER TABLE `test` DISABLE KEYS;
 
INSERT INTO `test` VALUES ('dummy');

10.4 a48d2ec8

 
mysqltest: At line 8: query 'INSERT INTO `test` VALUES ('dummy')' failed: 1032: Can't find record in 'test'

The resulting table structure:

SHOW CREATE TABLE `test`;
 
Table	Create Table
 
test	CREATE TABLE `test` (
 
  `foo` varchar(255) NOT NULL,
 
  UNIQUE KEY `foo_key` (`foo`) USING HASH
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

or, upon DDL,

CREATE TABLE t (f TEXT, UNIQUE(f)) ENGINE=MyISAM;
 
INSERT INTO t VALUES ('x');
 
ALTER TABLE t DISABLE KEYS;
 
ALTER TABLE t FORCE;
 
 
 
# Cleanup
 
DROP TABLE t;

Generated at Thu Feb 08 09:43:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.