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 ?

Answer 1

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.

Answer 2

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.