Bug #6455 backup tables with FK in good order
Submitted: 5 Nov 2004 9:35 Modified: 19 Sep 2008 14:41
Reporter: Mickael Besson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version:5.0, 5.1, 6.0 OS:Linux (Windows NT4)
Assigned to: CPU Architecture:Any

[5 Nov 2004 9:35] Mickael Besson 
Description:
It is sometimes usefull to worf on .sql file exported.

Then, could you backup tables in good order regarding FK ?

Thank you

How to repeat:
not a bug

Suggested fix:
take care of FK before doing back up
[17 Sep 2008 10:54] Susanne Ebrecht 
Many thanks for writing a feature request. This is a general problem of MySQL dump. The tables will be dumped in alphabetical order including foreign keys.

It would be better to dump schema first in alphabetic order and then add the keys by using ALTER TABLE.
[17 Sep 2008 11:17] Susanne Ebrecht 
Same with MySQL 5.1 bzr tree.
[17 Sep 2008 11:20] Susanne Ebrecht 
Test:

create table t(id serial, num integer)engine=innodb;
insert into t(num) values(1),(2),(3),(4),(5);
create table t1(id serial, i bigint unsigned, num integer, foreign key(i) references t(id))engine=innodb;
insert into t1(i,num) values(1,1),(1,2),(3,3),(3,4),(4,5);
create table t2(id serial, i bigint unsigned, num integer, foreign key(i) references t1(id))engine=innodb;
insert into t2(i,num) values(1,1),(1,2),(3,3),(3,4),(4,5);
create table t3(id serial, i bigint unsigned, num integer, foreign key(i) references t2(id))engine=innodb;
insert into t3(i,num) values(2,1),(2,2),(4,3),(4,4),(5,5);
create table t4(id serial, i bigint unsigned, num integer, foreign key(i) references t(id))engine=innodb;
insert into t4(i,num) values(3,1),(1,2),(2,3),(5,4),(5,5);
alter table t rename to z;
alter table t1 rename to m;
alter table t2 rename to a;
alter table t3 rename to j;
alter table t4 rename to c;

$ ./bin/mysqldump mydb

Dump dumps tables from a to z and not first z, then m, ....

Also the statement of create table includes the foreign keys.

Here it would be better to add FK after creating all tables.
[19 Sep 2008 12:34] Susanne Ebrecht 
Bug #12682 was set as duplicate of this bug here.
[19 Sep 2008 14:29] Susanne Ebrecht 
Verified for MySQL 6.0 bzr tree as well.
[19 Sep 2008 14:42] Susanne Ebrecht 
After checking this again. This is fixed in all bzr tree versions of MySQL.