Bug #18536 mysqldump does not maintain table orders as per --tables option
Submitted: 27 Mar 2006 10:41 Modified: 2 Jun 2006 1:41
Reporter: Rushabh Dadbhawala Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S1 (Critical)
Version:5.0.18 OS:Microsoft Windows (Win XP)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[27 Mar 2006 10:41] Rushabh Dadbhawala
Description:
mysqldump does not generate the backup as per the order of tables specified in the "--tables" option. mysqldump is a backup utility; but its output is not in a "restoreable" state, as the child tables get dumped before the parent tables.

Note: There's no guarantee that the below steps will reproduce the problem and hence I suggest that you try some more combinations. I have appended the commands with the actual output.

This is very very critical, as in the event of a system crash, or a migration, I can't ensure that the database will be restored correctly.

How to repeat:
start the mysql console interface:

mysql> use test;
Database changed

mysql> create table parent (col int primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> create table child (col int primary key references parent (col));
Query OK, 0 rows affected (0.06 sec)

mysql> exit;

now use the mysqldump program to get a backup:
$ mysqldump -u root -v -B test --tables parent child > test.sql
-- Connecting to localhost...
-- Retrieving table structure for table child...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table parent...
-- Sending SELECT query...
-- Retrieving rows...
-- Disconnecting from localhost...

Suggested fix:
mysqldump should not manipulate the order of tables given by the "--tables" option. This must be kept as it is. As far as I can recall, this was working fine in 4.1.x versions.
[28 Mar 2006 18:40] Tonci Grgin
Thanks for your bug report. I was able to verify it as you reported.
5.1.7-beta-nt-max-log, Win XP SP2

create table parent (col int primary key);
create table child (col int primary key references parent (col));

mysqldump -u root -v -B test --tables parent child > test.sql
-- Connecting to localhost...
-- Retrieving table structure for table child...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table parent...
-- Sending SELECT query...
-- Retrieving rows...
-- Disconnecting from localhost...
[7 Apr 2006 15:50] Alex Pyltsov
The same problem in 4.1.18
[8 Apr 2006 10:46] Rushabh Dadbhawala
We were using version 4.1.7 for one of our projects, and there it seems to work fine.

When can we expect the fix ?
[10 May 2006 1:40] Morgan Tocker
BUG #19659 is a duplicate.  It seems the scope is limited to InnoDB.
[10 May 2006 2:10] Morgan Tocker
InnoDB testcase;

morgo@morguntu:~$ mysql test;

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 4.1.18-standard-log |
+---------------------+

mysql> show tables;
Empty set (0.00 sec)

mysql> create table t2 (id int);
mysql> create table t1 (id int);
mysql> create table t3 (id int);
mysql> exit;

morgo@morguntu:~$ mysqldump test t3 t2 t1
(shows dump in order t3, t2, t1 - correct)

morgo@morguntu:~$ mysqldump test t1 t2 t3
(shows dump in order t1, t2, t3 - correct)

mysql> alter table t1 engine=innodb;
mysql> alter table t2 engine=innodb;
mysql> alter table t3 engine=innodb;

morgo@morguntu:~$ mysqldump test t3 t2 t1
(shows dump in order t3, t2, t1 - correct)

morgo@morguntu:~$ mysqldump test t1 t2 t3
(shows dump in order t3, t2, t1 - incorrect)
[19 May 2006 8:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6615
[19 May 2006 11:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6626
[23 May 2006 6:51] Ramil Kalimullin
fixed in 4.1.20
[24 May 2006 17:42] Paul Dubois
Noted in 4.1.21 changelog.

<command>mysqldump</command> did not respect the order of
tables named with the <option>--tables</option> option.
[2 Jun 2006 1:41] Rushabh Dadbhawala
when can we expect a fix in the 5.0.x series ?
[10 Jun 2009 9:16] Lee Mallabone
This bug is still present in 5.0.41. Can the patch be applied to the 5.x series?
[15 Feb 2012 5:26] elemneteoniut elemneteoniut
http://goo.gl/qDQ0n  Take a look at really low prices 
http://rxmedicaretax.com/ Take a look at really low prices 
http://hifrino.ru/ Take a look at really low prices