Restoring dump(backup): ERROR 1022 (23000) at line 44: Can't write; duplicate key in table

Question

I'm doing tests to change a current table from my InnoDB database to Archive, but I had a problem trying to restore a dump after the change.

After several tests, the problem is related to the value declared in the AUTO_INCREMENT attribute in CREATE TABLE. But this script is created automatically.

How can I fix this error so that I can restore the dump quickly without having to change it?

...

--
-- Table structure for table `tabela`
--

DROP TABLE IF EXISTS `tabela`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tabela` (
  `cd_tabela` int(11) NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`tabela`)
) ENGINE=ARCHIVE AUTO_INCREMENT=3081487 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tabela`
--

LOCK TABLES `tabela` WRITE;
/*!40000 ALTER TABLE `tabela` DISABLE KEYS */;
INSERT INTO `tabela` VALUES (1,...), ... (in this line the error appears)

...

Answer 1

In the block you have added to your question it is clear that you create a table with ENGINE=ARCHIVE. The Archive storage engine in MariaDB (and the other flavors of the ecosystem) does not support indexes.

The error you get is because of a UNIQUE/PRIMARY index reporting a duplicate. This means you are not restoring your backup to an ARCHIVE table.

I would assume that your backup includes the schema, and that it creates the table as InnoDB (or another storage engine that supports indexes). Can you make sure that the table is still the ARCHIVE storage engine after trying to import the dump?

Remove this part from your dump, or create it as ARCHIVE without specifying any indexes and importing the data should work.

Alternatively, you can remove the creation of indexes from your dump file and later on changing it to the storage engine you want it to be.