Mysqldump and the order of the tables

Question

I copy my database with mysqldump - uroot -p pagesjaunes > E:\db.sql
But when I try to execute mysql pagesjaunes < db.sql in my home pc I got error because mysqldump didn't put the tables with the right order in the file db.sql, tables without foreign key must be first for example.

DROP TABLE IF EXISTS `fonction`;

CREATE TABLE `fonction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(20) NOT NULL,
  `id_qualite` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fonction_qualite_fk` (`id_qualite`),
  CONSTRAINT `fonction_qualite_fk` FOREIGN KEY (`id_qualite`) REFERENCES `qualite` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `qualite`;
CREATE TABLE `qualite` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(40) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

I have to put qualite table first and it works but I have a lot of tables and there will be a lot of manual ordering.

So how can I handle this ?

score 15 · Accepted Answer · edited May 23 '17 at 11:46

15

i found the solution HERE i was making a mistake, in file E:\db.sql generated i deleted the lines like that

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

i was thinking that it's just a comment but in reality they are instructions too.

edited May 23 '17 at 11:46

Community

1
1

answered Feb 01 '14 at 13:31

Hayi

6,972
26
80
139

4

that's actually a "conditional comment", telling MySQL to skip it if it's version is under 4.00.14 – igorsantos07 Feb 21 '17 at 16:01

score 15 · Answer 2 · answered Feb 21 '18 at 20:36

15

You can't use --compact parameter of mysqldump, because it removes the information (comments) about foreign key dependency needed by mysql to recreate the tables later in the correct order.

answered Feb 21 '18 at 20:36

Ivan Apolonio

326
2
8

Mysqldump and the order of the tables

2 Answers2