0

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)

...
  • Recommend rethinking the use of ENGINE=Archive. This may not be the last of the hiccups. – Rick James Jan 26 '18 at 18:12
  • @RickJames I have a table in which I enter inumeros records (my largest table of all) data that are eventually queried, and because they are now with ENGINE = INNODB occupy a lot of RAM space, damaging other querys. I tried to change ENGINE of this table to ARCHIVE to try to minimize this my problem with RAM, do you have any other insight? – MathOliveira Jan 29 '18 at 11:45
  • When using multiple engines at the same time, you need to set the caches so that they share RAM. Do not allocate caches so high that "swapping" occurs. – Rick James Jan 29 '18 at 14:32
0

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.

  • Also without saying AUTO_INCREMENT. – Rick James Jan 26 '18 at 18:11
  • @Michael I looked in the MariaDB documentation and it says indexes are accepted link. I am restoring the backup on another clean server, I am not restoring upon a schema where the table is InnoDB. I tried to use only the dump inserts on a base with the correct structure (CREATE with NOT NULL AUTO_INCREMENT) and even then the error occurs. Now if I create without indexes works properly, however I wanted to restore my backup without having to change the dump file, is there any way to fix this problem? – MathOliveira Jan 29 '18 at 12:05
  • Ah, you are right about that. My bad, I have never actually used the ARCHIVE engine. I agree with @RickJames that it is not an engine to be used. Could it be that you created the dump from a InnoDB server where duplicate records existed? For example because the table you were exporting from does not have a primary key? In that case I would recommend to load the dump into an ARCHIVE table without primary key. – Michaël de Groot Jan 29 '18 at 21:26
  • If the dump file does contain a CREATE TABLE statement, the way to import the data without changing the dump file would be the following: - Create yourself a user without the privilege to create a table - Import the dump file slightly different, login to MariaDB and from the MariaDB prompt, execute: source /path/to/dump.sql; Please let me know if any of these ideas work and I will change the answer accordingly. – Michaël de Groot Jan 29 '18 at 21:33
  • As an alternative to ARCHIVE, you may want to use TokuDB (slightly discontinued but fantastic storage engine for this purpose, can be loaded as a plugin into MariaDB) or InnoDB with page compression (new feature in MariaDB). – Michaël de Groot Jan 29 '18 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.