MySQL Bugs: #63783: mysqldbcopy does not copy foreign keys

Bug #63783	mysqldbcopy does not copy foreign keys
Submitted:	17 Dec 2011 23:31	Modified:	11 Jul 2013 2:48
Reporter:	Greg Thomas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	5.2.36 CE	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using mysqldbcopy to copy a database, the foreign keys on tables are not copied.

How to repeat:
1. Create tables with a foreign key

CREATE  TABLE `test`.`person` (
  `id_person` INT NOT NULL AUTO_INCREMENT ,
  `surname` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id_person`) );

CREATE  TABLE `test`.`qualification` (
  `id_qualification` INT NOT NULL AUTO_INCREMENT ,
  `id_person` INT NOT NULL ,
  `qualification_name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id_qualification`) );

ALTER TABLE `test`.`qualification` ADD CONSTRAINT `FK_qualification_person` FOREIGN KEY `FK_qualification_person` (`id_person`)
    REFERENCES `person` (`id_person`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

INSERT INTO `test`.`person` (surname) VALUES ('Smith');
INSERT INTO `test`.`qualification` (id_person, qualification_name) VALUES (1, 'Twig Bender');

2. Note that the FK prevents rows from being deleted:
DELETE FROM `test`.`person` WHERE surname = 'Smith';

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`test`.`qualification`, CONSTRAINT `FK_qualification_person` FOREIGN KEY (`id_person`) REFERENCES `person` (`id_person`))

3. Copy the database test to test2; 
mysqldbcopy --source=root:password@localhost:3306 --destination=root:password@localhost:3306 test:test2

4. Note that on test2, the FK is lost.
DELETE FROM `test2`.`person` WHERE surname = 'Smith';
1 row(s) affected

Thank you for the bug report. Verified just as described on Windows XP while working with MySQL server 5.5.19.

Fixed as of MySQL Utilities 1.2.4/1.3.3, and here's the changelog entry:

The "mysqldbcopy" utility failed to copy foreign key constraints when it
cloned databases, and the "--new-storage-engine" and
"--default-storage-engine" options did not function correctly when cloning
databases.

Thank you for the bug report.