MySQL Bugs: #36408: Dump of foreign key constraints with mysqldump not sorted

Bug #36408	Dump of foreign key constraints with mysqldump not sorted
Submitted:	29 Apr 2008 17:39	Modified:	8 Aug 2008 12:22
Reporter:	Peter Brückner	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	5.0.45	OS:	Linux (suse10.3)
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump foreign key constraints sort

Description:
When dumping a database with mysqldump everything is well sorted, so we can
make a diff in data structure with diff in the dump to test for correct update scripts.

The only problem is: Constraints (FOREIGN KEY) are only sometimes in the dump. I think it would be a good idea to sort constrains by name. This is done now only when an constraint is removed by drop constraint.

My tests show the following behaviour: after an create table the constraints
are sorted in order of definition. After any drop constraint: the constraints are sorted by name. 

How to repeat:
create a database with two different scripts:
-----------snip ---------------------
drop database if exists x;
create database x;
use x;

create table enum1(
  id integer unsigned not null auto_increment,
  constraint pk_enum1 primary key(id)
) engine=innodb;

create table t1(
  enum1Id integer unsigned not null,
  enum2Id integer unsigned not null,
  enum3Id integer unsigned not null,
  enum4Id integer unsigned not null,
  constraint fk_t1_d foreign key (enum1Id) references enum1(id),
  constraint fk_t1_c foreign key (enum2Id) references enum1(id),
  constraint fk_t1_a foreign key (enum3Id) references enum1(id),
  constraint fk_t1_b foreign key (enum4Id) references enum1(id)
) engine=innodb;

--alter table t1 drop foreign key fk_t1_c;
--alter table t1 drop column enum2Id;
----- snap -----------------

now do an mysqldump , after that: uncomment the last lines (alter table)
run and dump again: the constraints are now sorted by name!

Suggested fix:
Always sort by name in the dump.

Many thanks for writing a feature request.

Why do you want to sort the constraints by name?

we test structural identity to check for correct data model version.
everything else is stable sorted in the output but the fk-constraints.
so we can't make a diff to check the identity.

Your suggested test won't work perfect. The indexes are still not sorted.

I found some other bugs during analysing this bug here.

I think, what you really want is a dump out where you first have all "CREATE TABLE " statements and then at the end all dependencies.

Means first all create table statements in alphabetic order and then after all create table statements all create index statements and "alter table add foreign key" statements.

Is that right?

i think the constraints belong to the table when created. The sql syntax also designed in this way:
  CREATE TABLE x (
    x  CHAR(20),
    ...
    CONSTRAINT "xcon1" PRIMARY KEY(x)
    CONSTRAINT "xcon2" FOREIGN KEY(x) REFERENCES t(y) ON DELETE RESTRICT,
    ....
  );

so we would suggest this syntax and sorting by constraint name. But at least a
'stable' output - all definitions with the same meaning have the same dump.

Many thanks for your feedback.

I will give this feature request to development for discussion.

A workaround is to sort the constraints manually at the dump file.

This issue is still in mysql 5.0.51a. 
Sorting after dumping is quite hard since it should only sort some of the lines.

Any other suggestions on how to verify the structure-identity between two databases including constraints?

Hi,

Are there any plans to implement this feature? Anybody tracking this that could share their workarounds at the moment?

Thanks!
-Nestor

I would like to see this as well. All constraints, indexes, keys grouped together and sorted alphabetically. For schema comparisons of database changes this helps tremendously. Thanks.

Glad this request has been brought up again.
Any plans on implementing this in the near future?

Here is a gawk script that can be used to sort mysqldump CONSTRAINT and KEY  http://thinkinginsoftware.blogspot.com/2012/03/sort-key-and-constraint-in-mysqldump.html

Of course ideally this should be supported inside mysqldump code.