54

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.

Community
  • 1
  • 1
Tai Squared
  • 12,273
  • 24
  • 72
  • 82

7 Answers7

68

The mysqldump command included with MySQL since version 4.1.1 by default produces a script that turns off the foreign key checks. The following line is included near 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 version 4.0.14 and later. The old foreign key checks setting is restored towards the end of the dump file:

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

Note that the conditional comments are interpreted by the the client (rather than the server). If you load the dump file with a client that doesn't support them, then foreign key checks will not be disabled and you might encounter errors. For best results, I'd suggest loading dump files using the official mysql command line client:

mysql -hserver -uuser -p database < dumpfile.sql

It's also worth noting that if mysqldump is run with the --compact option, then the commands to disable and re-enable the foreign key checks are omitted from the dump file.

Phil Ross
  • 25,590
  • 9
  • 67
  • 77
  • 3
    Yes, I do see that in the file. Another person attempting to import this file was getting an error on a foreign key line and thought it was due to the creation order. – Tai Squared Mar 12 '10 at 00:50
  • 13
    How has this answered the question? Tai has said he is getting an error WITH these comments in the file, so how has the problem been solved? – JoeTidee Feb 05 '16 at 14:07
  • 4
    How is this an accepted answer? It is absolutely not aligned to the question asked :/ – Pragyan May 10 '18 at 05:08
  • My issue was the "--compact" thanks a lot! – Allan Andrade Feb 13 '22 at 18:21
40

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

Ciao.

edtruant
  • 883
  • 8
  • 9
  • 6
    The reason is that `--compact` includes `--skip-comments` so instead `--compact` one should use `--skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset` – iRonin May 05 '15 at 17:22
  • 6
    You can actually --skip-comments too. The manual say that --compact "enables the --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options", but apparently --compact also removes some other output in addition to that -- for example the FOREIGN_KEY_CHECKS statements. – dpi Apr 01 '16 at 09:28
13

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.

alds
  • 525
  • 9
  • 19
  • Wow! A down vote for suggesting an alternative solution that works much easier than what's asked – alds Nov 14 '13 at 08:36
  • 3
    That's because "much easier" is extremely subjective (I don't it's useful at all, e.g.) and it doesn't actually answer the question. – sehe Mar 28 '14 at 09:25
  • 4
    +1 definitely useful for PhpMyAdmin users. Everyone else can subjectively ignore it, or extremely subjectively downvote it :) – chim Nov 06 '14 at 11:18
9

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

iRonin
  • 470
  • 4
  • 12
2

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.

freezed
  • 1,269
  • 1
  • 17
  • 34
0

using --single-transaction means do all in one transaction. That option can not ignore malformed Foreign key constraint. In any transaction any error will stop continuing query execution. Your problem is that you have a Foreign key constraint to a table which does not exist.

Abadis
  • 2,671
  • 5
  • 28
  • 42
0

One way that worked for me was to use MySQL WorkBench.

First I reversed engineered the DB into a model. Then with the model tab open, I used the EXPORT -> forward-engineer SQL Create Script option. It brings up a dialog box that allowed me to pick (among many other things) whether or not to export FK Constraints and FK indices.

The MySQL Workbench version I used is version 8.0.29. It should be supported in older versions but I am not sure until I could try, so your mileage may vary if you are using an older version.