MySQL Bugs: #57838: mysqldump: sequence of CREATE TABLE ignores FOREIGN KEY

Bug #57838	mysqldump: sequence of CREATE TABLE ignores FOREIGN KEY
Submitted:	29 Oct 2010 9:22	Modified:	7 Nov 2010 13:47
Reporter:	Kai Sautter (Basic Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.1.51	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
If a table A references another table B (with a foreign key relationship) where the name of table A comes before the name of table B in lexical order, mysqldump will write the CREATE statements for the tables according to lexical order of table names. This will in turn result in an issue with the foreign key when using the sql script generated with mysqldump to (re-)create the database, e.g. to restore it.

If there are only a few FK relationships, running the script twice can solve the problem. If FK relationships are cascading (A --> B --> C), multiple runs can be necessary, which is inefficient and might be error prone.

How to repeat:
(1) Create a table 'B' having e.g. a SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY column
(2) Create a table 'A' having a foreign key column that references the primary key of table 'B'

It is important to make sure, that table B (created by (1)) has a name that is lexically "larger" than the name of table A (created by (2).

Run mysqldump -r<dumpfile> against the database

Suggested fix:
A) Analyse table relationships before deciding on the dump sequence for tables
B) Add foreign keys as separate ALTER TABLE statements below the last CREATE TABLE

You need to SET FOREIGN_KEY_CHECKS = 0. Does not 'mysqldump' always? I think so.

Peter
(not a MySQL person)

SET FOREIGN_KEY_CHECKS = 0 does the job. Thank you.

Unfortunately, this option is not automatically set (and unset) by the dump file. Would it be feasible to add this automatically (maybe governed by a specific option, if required not to interfere with other functionality).

I suggest to re-rate the bug to S3.

Please, check output of mysqldump without any options:

macbook-pro:5.5 openxs$ bin/mysqldump -uroot x
-- MySQL dump 10.13  Distrib 5.5.7-rc, for apple-darwin9.6.0 (i386)
--
-- Host: localhost    Database: x
-- ------------------------------------------------------
-- Server version	5.5.7-rc-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
...

Note the last line above. I clearly see that FOREIGN_KEY_CHECKS is set to 0.

I checked the files --> line is present as described

I checked this against a clean installation of mysql 5.1.52, but could not reproduce the behaviour.

I suggest to mark this bug as not reproducible.

Not repeatable with recent versions.

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