Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25102

UNIQUE USING HASH error after ALTER ... DISABLE KEYS

    XMLWordPrintable

Details

    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.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              thiesmeincke Thies Meincke
              Votes:
              8 Vote for this issue
              Watchers:
              13 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.