0

I generate backups of my databases by using mysqldump.exe -u root --all-databases > mariaDbBackup.sql.

When I try to restore my dump by doing mysql -u root -p < mariaDbBackup.sql, I get an

Error 1064 (42000) near 'STATS_PERSISTENT=0'

Here is the faulty line:

CREATE TABLE `engine_cost` (
  `engine_name` varchar(64) NOT NULL,
  `device_type` int(11) NOT NULL,
  `cost_name` varchar(64) NOT NULL,
  `cost_value` float DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `comment` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`cost_name`,`engine_name`,`device_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
/*!40101 SET character_set_client = @saved_cs_client */;

It is the first line out of seven on the backup that includes STATS_PERSISTENT=0 so I assume it might have to be a compatibility issue. I am using specifically MariaDB 10.1.37 and MySQL 5.5.39.

I assume there have to be some flag in either generating or reading the dump that I can use to be able to do it.

So, how can I restore the dump or how do I generate a compatible dump?

  • how do I generate a compatible dump? Compatible structure dump cannot be generated. Edit it manually. Table's data compatible dump can be generated using SELECT .. INTO OUTFILE - separately for each table. – Akina Jan 30 at 19:31
0

I've not tested this myself, but in the MariaDB mysqldump main page there is a paragraph about the --create-options switch which seems to indicate you can solve the problem by using the switch --skip-create-options:

Include all MariaDB and/or MySQL specific create options in CREATE TABLE statements. Use --skip-create-options to disable.

  • It (kind of) works!, it gives error 1005 errno:-1 when it tries to restore the mysql database, I think that those tables do need their creation options. However, skipping that database from the backup it was able to restore all other dbs. Lets just hope that the default creation options don't mess up the data, I see no reason why would they. – LeedMx Jan 30 at 21:03

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.