MySQL Bugs: #82080: mysqldbcopy fails to copy multiple foreign keys on the same table

Bug #82080	mysqldbcopy fails to copy multiple foreign keys on the same table
Submitted:	1 Jul 2016 9:15	Modified:	5 Aug 2016 8:56
Reporter:	Guillermo Barbero Maiz	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	5.5.44-0ubuntu0.14.04.1 (Ubuntu)	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any
Tags:	foreign key, mysqldbcopy

Description:
It seems this is a particular version of bug #63783

When a table has several foreign keys, each one to different tables, it gives the following error:

ERROR: Unable to execute constraint query
ALTER TABLE geisher_test.printing_properties add CONSTRAINT `printing_properties_printer_id_foreign`
FOREIGN KEY (`printer_id`,`user_id`)
REFERENCES `geisher_test`.`printers`
(`id`,`id`)

How to repeat:
In my case there are three tables; users, printers and printing_properties
and printing_properties has user_id (with onDeleteCascade) and printer_id as foreign keys

mysqldbcopy seems to fail to reproduce the constraint query command

Suggested fix:
instead of creating one alter table command to add all the foreign keys, create one per referenced table

Hi Guillermo Barbero Maiz,

Thank you for the report.
I cannot reproduce this issue using MySQL Utilities mysqldbcopy version 1.6.1 and with dummy schema. Could you please tell us which MySQL Utilities version you are using and also provide exact repeatable test case(table DDLs).

- On my test machine

DROP TABLE IF EXISTS t3,t2,t1;
CREATE TABLE t1 (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);
CREATE TABLE t2 (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE t3 (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    owner SMALLINT UNSIGNED NOT NULL,
	owner1 SMALLINT UNSIGNED NOT NULL ,
    PRIMARY KEY (id),
	CONSTRAINT `owner_fk` FOREIGN KEY (owner) REFERENCES `t1`(`id`) ON DELETE CASCADE,
	CONSTRAINT `owner1_fk` FOREIGN KEY (owner1) REFERENCES `t2`(`id`)
	 
);

root@ubuntu1604lts:~# mysqldbcopy --source=ushastry@x.x.x.x --destination=ushastry@x.x.x.x db1:db2 --drop-first
WARNING: Using a password on the command line interface can be insecure.
# Source on x.x.x.x: ... connected.
# Destination on x.x.x.x: ... connected.
# Copying database db1 renamed as db2
# Copying TABLE db1.t1
# Copying TABLE db1.t2
# Copying TABLE db1.t3
# Copying data for TABLE db1.t1
# Copying data for TABLE db1.t2
# Copying data for TABLE db1.t3
#...done.
root@ubuntu1604lts:~# mysqldbcopy --version
MySQL Utilities mysqldbcopy version 1.6.1 
License type: GPLv2

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Umesh

MySQL utilities version is:

MySQL Utilities mysqldbcopy version 1.3.5 (part of MySQL Workbench Distribution 5.2.47)

Here is my test case:

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `valid` tinyint(1) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `provider` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `provider_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `promo` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `tz` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `printers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `printers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `latitude` double(12,8) NOT NULL,
  `longitude` double(12,8) NOT NULL,
  `address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `postal_code` int(11) NOT NULL,
  `place` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `province` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `bm_id` int(10) unsigned NOT NULL,
  `web` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `phone_1` int(11) NOT NULL,
  `phone_2` int(11) NOT NULL,
  `email_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `order_limit` int(11) NOT NULL,
  `total_limit` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mgmt_cost` double(10,5) unsigned NOT NULL,
  `url_code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `has_logo` tinyint(1) NOT NULL,
  `has_distro` tinyint(1) NOT NULL,
  `hours` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `printers_user_id_foreign` (`user_id`),
  KEY `printers_bm_id_foreign` (`bm_id`),
  CONSTRAINT `printers_bm_id_foreign` FOREIGN KEY (`bm_id`) REFERENCES `billing_models` (`id`),
  CONSTRAINT `printers_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `printing_properties`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `printing_properties` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `printer_id` int(10) unsigned NOT NULL,
  `features` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  `product` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `printing_properties_user_id_foreign` (`user_id`),
  KEY `printing_properties_printer_id_foreign` (`printer_id`),
  CONSTRAINT `printing_properties_printer_id_foreign` FOREIGN KEY (`printer_id`) REFERENCES `printers` (`id`),
  CONSTRAINT `printing_properties_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

I'm not seeing any issues while using MySQL Utilities 1.6.1 with MySQL version 5.5.50. I would suggest you to upgrade MySQL Utilities and see if fixes your issue.

-- Created dummy table billing_models as it was missing in the provided tables DDL.

root@ubuntu1604lts:~# mysqldbcopy --source=ushastry@x.x.x.x:3306 --destination=ushastry@x.x.x.x:3306 db1:db2 --drop-first
WARNING: Using a password on the command line interface can be insecure.
# Source on x.x.x.x: ... connected.
# Destination on x.x.x.x: ... connected.
# Copying database db1 renamed as db2
# Copying TABLE db1.billing_models
# Copying TABLE db1.printers
# Copying TABLE db1.printing_properties
# Copying TABLE db1.users
# Copying data for TABLE db1.billing_models
# Copying data for TABLE db1.printers
# Copying data for TABLE db1.printing_properties
# Copying data for TABLE db1.users
#...done.
root@ubuntu1604lts:~# mysqldbcopy --version
MySQL Utilities mysqldbcopy version 1.6.1 
License type: GPLv2
root@ubuntu1604lts:~# 

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".