[MDEV-25102] Restore Mariadb dump cannot be restored: ERROR 1032 (HY000) at line xx: Can't find record in .. Created: 2021-03-10  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.4.17, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Thies Meincke Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 8
Labels: regression
Environment:

SLES 12.5, SLES 15.2

Issue Links:
Duplicate
duplicates MDEV-26253 ERROR 1032 (HY000): Can't find record... Confirmed
Relates
relates to MDEV-20001 Potential dangerous regression: INSER... Closed

 Description   

A dump of a table with a UNQUE KEY cannot be restored. Error: ERROR 1032 (HY000) at line xx: Can't find record in ...
However, mysqldump with option --skip-disable-keys creates dumps, that can be restored.
So the workaround is to create dumps with this option set instead of the default option --disable-keys
The problem is located in the following line of the dump:
/*!40000 ALTER TABLE `seiten` DISABLE KEYS */;

we can reproduce the error with this dum file of a table "seiten":

-- MariaDB dump 10.18  Distrib 10.4.17-MariaDB, for Linux (x86_64)
 
--
 
-- Host: localhost    Database: pro
 
-- ------------------------------------------------------
 
-- Server version	10.4.17-MariaDB-log
 
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 
/*!40101 SET NAMES utf8mb4 */;
 
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
 
/*!40103 SET TIME_ZONE='+00:00' */;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
 
 
--
 
-- Table structure for table `seiten`
 
--
 
 
 
DROP TABLE IF EXISTS `seiten`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `seiten` (
 
  `ID` int(11) NOT NULL AUTO_INCREMENT,
 
  `CheckTime` int(11) DEFAULT NULL,
 
  `Error` text DEFAULT NULL,
 
  `URL` varchar(400) DEFAULT NULL,
 
  `Alternate` int(11) DEFAULT NULL,
 
  `CMS` varchar(1) DEFAULT NULL,
 
  `Title` varchar(500) DEFAULT NULL,
 
  `Status` int(11) DEFAULT NULL,
 
  PRIMARY KEY (`ID`),
 
  UNIQUE KEY `URL` (`URL`) USING HASH,
 
  KEY `CheckTime` (`CheckTime`),
 
  KEY `Status` (`Status`)
 
) ENGINE=MyISAM AUTO_INCREMENT=197673 DEFAULT CHARSET=utf8;
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
 
--
 
-- Dumping data for table `seiten`
 
--
 
 
 
LOCK TABLES `seiten` WRITE;
 
/*!40000 ALTER TABLE `seiten` DISABLE KEYS */;
 
INSERT INTO `seiten` VALUES (1,1615130639,'{}','https://www.uni-hamburg.de/',196731,'f','Universität Hamburg',200);
 
/*!40000 ALTER TABLE `seiten` ENABLE KEYS */;
 
UNLOCK TABLES;
 
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
 
 
-- Dump completed on 2021-03-10 10:24:52

when the line 

 /*!40000 ALTER TABLE `seiten` DISABLE KEYS */;

is removed the dump can be restored.

 Comments   
Comment by Alice Sherepa [ 2021-03-12 ]

Thanks! It is reproducible on 10.4,10.5, the problem with UNIQUE KEY..USING HASH

create table t1 (i varchar(400), unique key (i) using hash) engine=myisam ;
 
insert into t1 values (1),(2),(3),(4);
 
 
 
--let $dmp1 = $MYSQLTEST_VARDIR/tmp/1.sql
 
--exec $MYSQL_DUMP test t1 > $dmp1
 
 
 
drop table t1;
 
--exec $MYSQL test < $dmp1
 
 
 
#Cleanup
 
--remove_file $dmp1
 
drop table t1;


ERROR 1032 (HY000) at line 37: Can't find record in 't1'

Comment by Andrei Elkin [ 2022-02-22 ]

sanja, I am re-distributing Sachin's bugs. If anything you'll find unrelated to you, please bounce back to me or straight to serg

Comment by Elena Stepanova [ 2022-05-17 ]

Or, in simpler terms,

create table t1 (i int, unique key (i) using hash) engine=myisam;
 
alter table t1 disable keys;
 
insert into t1 values (1),(2);
 
 
 
# Cleanup
 
drop table t1;

Comment by Kim Gert Nielsen [ 2023-04-11 ]

I can reproduce it in 10.6-12 too when restoring

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