Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.17, 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
-
SLES 12.5, SLES 15.2
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
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
This is unfortunately not easy to fix.
A simple solution would be to imply --skip-disable-keys internally when hash unique constraints are present in a table (that is, make these tables ignore ALTER TABLE ... DISABLE KEYS).
A better solution would be to disable only non-unique keys, as ALTER TABLE ... DISABLE KEYS is supposed to do, but this requires changes in storage engine API, because engines currently don't know that hash unique constraint is unique.
Hi all! I wanted to see if I could get a little clarification on the status. I was under the impression by the age of the ticket and the reply from Sergei that this issue was not going to get fixed. But now it's marked as fixed all of a sudden.
Is this really fixed or was in inadvertently marked as fixed instead of something else?
And thanks for all of your work BTW!
I just noticed that we're seeing this issue with all of the websites that we host that use this plugin:
https://wordpress.org/plugins/seo-redirection/
The table where they store the redirect data (_WP_SEO_Redirection) triggers this issue and using `--skip-disable-keys` fixes the issue during the import. Without that option, the import fails.
We're running 10.11.7 as well. Are there any other solutions other than --skip-disable-keys?