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

[17 Dec 2011 23:31] Greg Thomas
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
[19 Dec 2011 6:59] Valeriy Kravchuk
Thank you for the bug report. Verified just as described on Windows XP while working with MySQL server 5.5.19.
[11 Jul 2013 2:48] Philip Olson
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.