Can you automatically create a mysqldump file that

Question

When I run a mysqldump command on my database and then try to import it, it fails as it attempts to create the tables alphabetically, even though they may have a foreign key that references a table later in the file. There doesn't appear to be anything in the documentation and I've found answers like this that say to update the file after it's created to include:

set FOREIGN_KEY_CHECKS = 0;
...original mysqldump file contents...
set FOREIGN_KEY_CHECKS = 1;

Is there no way to automatically set those lines or export the tables in the necessary order (without having to manually specify all table names as that can be tedious and error prone)? I could wrap those lines in a script, but was wondering if there is an easy way to ensure I can dump a file and then import it without manually updating it.

Answer 1

If you're using phpMyAdmin when exporting SQL, choose Custom Export Method. Then among the checkbox options, click "Disable foreign key checks". The exported SQL statement will have the disable and enable foreign key checks at the beginning and end of the output file respectively.

It's not "automatic", but you won't have to write the statements yourself for every export.

Answer 2

The mysqldump command included with MySQL 5.0.51 (and according to the change log versions since 4.1.1) does switch off foreign key checks. By default, mysqldump includes the following line at the top of the dump file:

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

The /*!40014 ... */ syntax is a conditional comment that will be executed on MySQL 4.0.14 and later. The old foreign key checks setting is restored at the end of the dump file:

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

Answer 3

This may happen if you use --compact as one of your mysqldump command. --compact includes --skip-comments so instead --compact one should use --skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset

Answer 4

Beware of your MySQL client you use, with the mysql command, no problem. Dumping:

% mysqldump -u ocp6 -pocp6 ocp6 --single-transaction --result-file=dump.sql 

Restoring:

% mysql -u ocp6 -pocp6 ocp6 < dump.sql

Everything's fine.

With the use of another MySQL client (mycli in my situation) to restore the dump-file:

mysql ocp6@:(none)> \. dump.sql
[…]
(1005, 'Can\'t create table `ocp6`.`composition` (errno: 150 "Foreign key constraint is incorrectly formed")')

I assume that mycli do not understand conditional comments.

Answer 5

Beware. For some reason mysqldump doesn't write the FOREIGN_KEY_CHECKS=0 if the --compact option is used.

Ciao.