MySQL Bugs: #87784: mysqldiff dropping foreign key constraints and adding them again wrongly

Bug #87784	mysqldiff dropping foreign key constraints and adding them again wrongly
Submitted:	17 Sep 2017 7:40	Modified:	18 Sep 2017 5:57
Reporter:	LOKESH SANAPALLI	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Utilities	Severity:	S2 (Serious)
Version:	1.6.5	OS:	Ubuntu (14.04)
Assigned to:		CPU Architecture:	Any
Tags:	mysqldiff

Description:
mysqldiff producing wrong transformation by dropping foreign keys and adding them to the reference of another db.

How to repeat:
I have two servers and below table with same definition on both databases.

On server1:

CREATE DATABASE bbdb;

USE bbdb;

CREATE TABLE `wh_productsales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_on` datetime NOT NULL,
  `updated_on` datetime NOT NULL,
  `created_by_id` int(11) DEFAULT NULL,
  `updated_by_id` int(11) DEFAULT NULL,
  `date` date NOT NULL,
  `sales_quantity` decimal(10,2) NOT NULL,
  `promo_quantity` decimal(10,2) NOT NULL,
  `kirana_quantity` decimal(10,2) NOT NULL DEFAULT '0.00',
  `outofstock_time` int(11) NOT NULL DEFAULT '0',
  `available_time` int(11) NOT NULL DEFAULT '86400',
  `unavailable_time` int(11) NOT NULL DEFAULT '0',
  `actual_sales` decimal(10,2) NOT NULL DEFAULT '0.00',
  `dc_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `date` (`date`,`product_id`,`dc_id`),
  KEY `wh_productsales_4a21cf42` (`created_by_id`),
  KEY `wh_productsales_6f403c1` (`updated_by_id`),
  KEY `wh_productsales_679343db` (`date`),
  KEY `wh_productsales_44bdf3ee` (`product_id`),
  KEY `wh_productsales_f3c25827` (`dc_id`),
  KEY `idx_wh_productsales_created_on` (`created_on`),
  CONSTRAINT `created_by_id_refs_id_567b1f38` FOREIGN KEY (`created_by_id`) REFERENCES `auth_user` (`id`),
  CONSTRAINT `updated_by_id_refs_id_567b1f38` FOREIGN KEY (`updated_by_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=261935007 DEFAULT CHARSET=latin1;
CREATE TABLE `auth_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(30) NOT NULL,
  `email` varchar(75) NOT NULL,
  `password` varchar(128) NOT NULL,
  `is_staff` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `is_superuser` tinyint(1) NOT NULL,
  `last_login` datetime(6) DEFAULT NULL,
  `date_joined` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=49782 DEFAULT CHARSET=latin1;

on Server2:
CREATE DATABASE qas4_bbdb;

USE qas4_bbdb;

Execute the same table definitions as above

when I run mysqldiff like this
`mysqldiff --force --server1=username:password@server1-db:3306 --server2=username:password@qserver2-db:3306 bbdb:qas4_bbdb --changes-for=server2 --difftype=sql`

The result we are obtaining is:

# Comparing `bbdb`.`wh_productsales` to `qas4_bbdb`.`wh_productsales`   [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `qas4_bbdb`.`wh_productsales`
  DROP FOREIGN KEY created_by_id_refs_id_567b1f38,
  DROP FOREIGN KEY product_id_refs_id_3530cfeb,
  DROP FOREIGN KEY updated_by_id_refs_id_567b1f38,
ADD CONSTRAINT created_by_id_refs_id_567b1f38 FOREIGN KEY(created_by_id) REFERENCES `bbdb`.`auth_user`(id),
ADD CONSTRAINT updated_by_id_refs_id_567b1f38 FOREIGN KEY(updated_by_id) REFERENCES `bbdb`.`auth_user`(id),
ADD CONSTRAINT product_id_refs_id_3530cfeb FOREIGN KEY(product_id) REFERENCES `bbdb`.`product_product`(id),
AUTO_INCREMENT=238982497;

There, it was dropping foreign keys and adding them again which is okay but while adding it's referencing back to the first db which is unfair.

Hello LOKESH,

Thank you for the report and feedback.

Thanks,
Umesh

Also ignore ON DELETE and ON UPDATE

# ON DB1

CREATE TABLE `user` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `document` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` int(10) UNSIGNED NOT NULL,
  `document` varchar(100) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `userId` (`userId`),
   CONSTRAINT `document_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE   
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

#ON DB2

CREATE TABLE `user` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `document` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` int(10) UNSIGNED NOT NULL,
  `document` varchar(100) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `userId` (`userId`),
   CONSTRAINT `document_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT   
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysqldiff --server1=root@localhost --server2=root@localhost --difftype=sql --changes-for=server1 --show-reverse db2.document:db1.document
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing db2.document to db1.document                          [FAIL]
# Transformation for --changes-for=server1:
#

ALTER TABLE `db2`.`document` 
  DROP FOREIGN KEY document_ibfk_1, 
ADD CONSTRAINT document_ibfk_1 FOREIGN KEY(userId) REFERENCES `db1`.`user`(id);

#
# Transformation for reverse changes (--changes-for=server2):
#
# ALTER TABLE `db1`.`document` 
#   DROP FOREIGN KEY document_ibfk_1, 
# ADD CONSTRAINT document_ibfk_1 FOREIGN KEY(userId) REFERENCES `db2`.`user`(id);
#