Bug #1850 mysqldump: foreign keys issue [order, import, constraints, circular]

• Submitted: 16 Nov 2003 8:40
• Modified: 17 Nov 2003 1:41
• Reporter: Jason Pyeron
• Status: Closed
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 4.0.16
• OS: Linux (RedHat 9 / Win2k (ANY))
• CPU Architecture: Any

[16 Nov 2003 8:40] Jason Pyeron

Description:
re bug 970:

 could the SET FOREIGN_KEY_CHECKS=0; / SET FOREIGN_KEY_CHECKS=1;
be added as an option to mysqldump?

it should go inside the dump code not outside, hence it requires a manual edit to do so.. RATIONAL: how does the restorer "know" the details about the database? the burden should be on backup not restore.

IMHO: it should surround only the table which have FKs but that is difficult to do.

How to repeat:
-- mysqldump does not add the next line:
-- SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE a_first (
  id int(11) NOT NULL auto_increment,
  ref_z int(11) default NULL,
  PRIMARY KEY  (id),
  KEY ref_z (ref_z),
  CONSTRAINT `0_16` FOREIGN KEY (`ref_z`) REFERENCES `z_last` (`id`)
) TYPE=InnoDB;
INSERT INTO a_first VALUES (4,1);
INSERT INTO a_first VALUES (3,2);
INSERT INTO a_first VALUES (2,3);
INSERT INTO a_first VALUES (1,4);
CREATE TABLE m_second (
  id int(11) NOT NULL auto_increment,
  PRIMARY KEY  (id)
) TYPE=InnoDB;
INSERT INTO m_second VALUES (1);
CREATE TABLE n_third (
  id int(11) NOT NULL auto_increment,
  ref_m int(11) default NULL,
  PRIMARY KEY  (id),
  KEY ref_m (ref_m),
  CONSTRAINT `0_18` FOREIGN KEY (`ref_m`) REFERENCES `m_second` (`id`)
) TYPE=InnoDB;
INSERT INTO n_third VALUES (1,1);
INSERT INTO n_third VALUES (2,1);
INSERT INTO n_third VALUES (3,1);
CREATE TABLE z_last (
  id int(11) NOT NULL auto_increment,
  PRIMARY KEY  (id)
) TYPE=InnoDB;
INSERT INTO z_last VALUES (1);
INSERT INTO z_last VALUES (2);
INSERT INTO z_last VALUES (3);
INSERT INTO z_last VALUES (4);
-- mysqldump does not add the next line:
-- SET FOREIGN_KEY_CHECKS=1;

Suggested fix:
mysqldump --disable-foreignkeys

[17 Nov 2003 1:41] Sergei Golubchik

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

it's done in 4.1 (will be in 4.1.1)

more precisely mysqldump now prepends the dump with

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;

and restores old values at the end of the dump - just in case somebody will load the dump with "source" command of mysql command-line client.