Bug #89105 mysqldbcompare CONSTRAINT references wrong DB
Submitted: 4 Jan 2018 12:56 Modified: 4 Jan 2018 13:57
Reporter: Josh Nijenhuis Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Utilities Severity:S3 (Non-critical)
Version:1.6.5 OS:Any
Assigned to: CPU Architecture:Any

[4 Jan 2018 12:56] Josh Nijenhuis
Description:
when running mysqldbcompare and there is a difference between two tables on constraints it fails to reference the right db on output

How to repeat:
CREATE DATABASE comp1;
CREATE DATABASE comp2;

CREATE TABLE `comp1`.`log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `datefield` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE  `comp1`.`log_details` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `log_id` int(10) unsigned NOT NULL DEFAULT 0,
  `comments` text NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `log_details_ix_log_id` (`log_id`),
  CONSTRAINT `fk_log_id` FOREIGN KEY (`log_id`) REFERENCES `log` (`id`) ON UPDATE NO ACTION
) ENGINE=InnoDB;

CREATE TABLE `comp2`.`log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `datefield` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE  `comp2`.`log_details` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `log_id` int(10) unsigned NOT NULL DEFAULT 0,
  `comments` text NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `log_details_ix_log_id` (`log_id`)
) ENGINE=InnoDB;

mysqldbcompare --server1=$username:$password@localhost --server2=$username:$password@localhost comp1:comp2 --skip-row-count --skip-data-check --changes-for=server2 --disable-binary-logging --difftype=sql --run-all-tests --skip-table-options 

# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases comp1 on server1 and comp2 on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     log                                     pass    SKIP    SKIP    
# TABLE     log_details                             FAIL    SKIP    SKIP    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE `comp2`.`log_details` 
ADD CONSTRAINT fk_log_id FOREIGN KEY(log_id) REFERENCES `comp1`.`log`(id);

# Database consistency check failed.
#
# ...done

Suggested fix:
comp1 in the REFERENCES should be comp2
[4 Jan 2018 13:57] Umesh Shastry
Hello Josh Nijenhuis,

Thank you for the report and test case.

Thanks,
Umesh
[4 Jan 2018 13:57] Umesh Shastry
--
Launching console ...

Welcome to the MySQL Utilities Client (mysqluc) version 1.6.5
Copyright (c) 2010, 2017 Oracle and/or its affiliates. All rights reserved.
This is a release of dual licensed MySQL Utilities. For the avoidance of
doubt, this particular copy of the software is released
under the version 2 of the GNU General Public License.
MySQL Utilities is brought to you by Oracle.

Type 'help' for a list of commands or press TAB twice for list of utilities.

mysqluc> mysqldbcompare --server1=ushastry:@10.172.161.253:7777 --server2=ushastry:@10.172.161.253:7777 comp1:comp2 --skip-row-count --skip-data-check --changes
-for=server2 --disable-binary-logging --difftype=sql --run-all-tests --skip-table-options
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 10.172.161.253: ... connected.
# server2 on 10.172.161.253: ... connected.
# Checking databases comp1 on server1 and comp2 on server2
#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     log                                     pass    SKIP    SKIP
# TABLE     log_details                             FAIL    SKIP    SKIP
#
# Transformation for --changes-for=server2:
#

ALTER TABLE `comp2`.`log_details`
ADD CONSTRAINT fk_log_id FOREIGN KEY(log_id) REFERENCES `comp1`.`log`(id);

# Database consistency check failed.
#
# ...done
[13 Jan 2018 6:34] Umesh Shastry
Bug #89210 marked as duplicate of this one