MySQL Bugs: #16181: Foreign keys: mysqldump should respect foreign keys

Bug #16181	Foreign keys: mysqldump should respect foreign keys
Submitted:	4 Jan 2006 11:50	Modified:	22 Dec 2013 10:27
Reporter:	Sebastian Nohn	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.0,5.1,5.5	OS:	Any (any)
Assigned to:		CPU Architecture:	Any
Tags:	bfsm_2007_03_01

Description:
mysqldump exports tables in alphabetical order. it should be possible to export a database in a way that referential integrity is respected.

How to repeat:
$ mysqldump database > test.sql
$ mysql somedatabase < test.sql

Thank you the bug report - feature request.

You can import the backup with the following lines.

mysql
>use databasename
>SET FOREIGN_KEY_CHECKS = 0;
>\. sqlscript.sql
>SET FOREIGN_KEY_CHECKS = 1;

Then your foreign keys will be properly restored.
N.b. the fastes way to take a backup is with mysqldump --skip-opt --quick --extended-insert databasename > sqlscript.sql.

Hope this helps.

Best regards
Rasmus

This is not possible in case of circular references... :-)

Bug #67486 was marked as duplicate of this one.

As mentioned by Domas, it is not always possible to define order "that referential integrity is respected" , e.g in case with circular references or e.g when every table references all other tables.

Thus the only solution is to use FOREGN_KEY_CHECKS variable during import, which always present in mysqldump-created scripts and should solve mentioned FK problems.

set foreign_key_checks=0;
create table a(i int primary key, foreign key (i) references b(i)) engine=innodb;
create table b(i int primary key, foreign key (i) references a(i)) engine=innodb;

If you get error 1215 during import of mysqldump-created script - check if connection ever was aborted at some point during the import (which would reset FOREGN_KEY_CHECKS variable). Also check if all tables did have all prerequisites for FK constraints satisfied.

I put status of this ticket to 'Feedback needed' in case if I missed something and eventually will close it if no complain

see also bug #70979	load of mysqldump script may fail with error 1215 if dest already has tables

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".