MySQL Bugs: #18536: mysqldump does not maintain table orders as per --tables option

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:	Windows (Win XP)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

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.

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...

The same problem in 4.1.18

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 ?

BUG #19659 is a duplicate.  It seems the scope is limited to InnoDB.

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)

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

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

fixed in 4.1.20

Noted in 4.1.21 changelog.

<command>mysqldump</command> did not respect the order of
tables named with the <option>--tables</option> option.

when can we expect a fix in the 5.0.x series ?

This bug is still present in 5.0.41. Can the patch be applied to the 5.x series?

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