Bug #89096 Inconsistent interaction between mysqldbexport and mysqldbcompare
Submitted: 3 Jan 2018 8:14 Modified: 8 Jan 2018 12:56
Reporter: Ingo Korndoerfer Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Utilities Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[3 Jan 2018 8:14] Ingo Korndoerfer
Description:
mysqldbcompare run on a "schema only" backup of my database flags differences in the foreign keys in a quantity that makes it impossible to spot real schema differences between the backup and my current version.

How to repeat:
I can confirm this or at least a similar inconsistency. Procedure to reproduce:

1. dump db:
mysqldbexport --server=root:<paasword>@<server> --skip=data --output="<filename>" <database>

2. run script to reproduce schema in a new db ('old') on the server (toggle 'continue after errors' switch in workbench)

3. compare thus generated version.

>mysqldbcompare --server1=root:<password>@<server> --changes-for=server1 --difftype=sql old:<database> --run-all-tests --skip-data-check --skip-row-count

Produces endless complaints like:

#
# Transformation for --changes-for=server1:
#

ALTER TABLE `old`.`tbl_seedstocks`
  DROP FOREIGN KEY fk_tbl_seedstocks_tbl_dilutions1,
  DROP FOREIGN KEY fk_tbl_seedstocks_tbl_drops1,
ADD CONSTRAINT fk_tbl_seedstocks_tbl_dilutions1 FOREIGN KEY(SeedStockDilutionID) REFERENCES `crystaltrain`.`tbl_dilutions`(DilutionID),
ADD CONSTRAINT fk_tbl_seedstocks_tbl_drops1 FOREIGN KEY(DropID) REFERENCES `crystaltrain`.`tbl_drops`(DropID),
AUTO_INCREMENT=523;

i.e. is dropping foreign keys and re-creating them. 

Suggested fix:
Maybe something to do with mysqldbcompare not having clear that all keys reference 'internal' tables, i.e. NOT keys in the specific databases, and thus tries to create keys in the backup that point to tables in the working copy?
[8 Jan 2018 12:56] MySQL Verification Team
Hello Ingo Korndoerfer,

Thank you for the report.
This is most likely duplicate of Bug #89105, please see Bug #89105.

Thanks,
Umesh