Bug #77026 mysqldbcompare and mysqldiff error updating
Submitted: 13 May 2015 12:20 Modified: 10 Oct 2016 10:06
Reporter: fabio fabio Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Utilities Severity:S2 (Serious)
Version:5.6.24 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[13 May 2015 12:20] fabio fabio
Description:
hi, using mysqldbcompare with in this way

mysqldbcompare --server1=root@localhost --server2=root@localhost --changes-for=server2 --skip-data-check --run-all-tests --skip-table-options --show-reverse --difftype=sql db1:db2 > f:\tst.sql

executing the script produced to sync i receave many errors.

specifically, when updating FK tables. example:

ALTER TABLE  `casino_gameround` 
  DROP FOREIGN KEY casino_gameround_fk1, 
  DROP FOREIGN KEY casino_gameround_fk2, 
  DROP INDEX casino_gameround_producttimestamp, 
  DROP INDEX casino_gameround_timestamp, 
  DROP INDEX casino_gameround_idx3, 
  DROP INDEX GAMEID, 
  DROP INDEX ID_PRODUCT, 
  DROP INDEX casino_gameround_idx2, 
  DROP INDEX casino_gameround_idx4, 
  DROP PRIMARY KEY, 
ADD CONSTRAINT casino_gameround_fk1 FOREIGN KEY(ID_PRODUCT) REFERENCES  `tooladmin_products`(ID_PRODUCT), 
ADD CONSTRAINT casino_gameround_fk2 FOREIGN KEY(GAMEID) REFERENCES  `casino_game`(ID_GAME), 
  ADD PRIMARY KEY(`ID_GAMEROUND`), 
  ADD INDEX casino_gameround_timestamp (TIMESTAMP), 
  ADD INDEX casino_gameround_producttimestamp (TIMESTAMP,ID_PRODUCT), 
  ADD INDEX casino_gameround_idx3 (DEVICEID), 
  ADD INDEX GAMEID (GAMEID), 
  ADD INDEX casino_gameround_idx2 (USERID), 
  ADD INDEX ID_PRODUCT (ID_PRODUCT), 
  ADD INDEX casino_gameround_idx4 (PRODUCTID);

There is a parameter to produce the change of FK subsequent modification of the tables?
like:

ALTER TABLE  `casino_gameround` 
  DROP FOREIGN KEY casino_gameround_fk1, 
  DROP FOREIGN KEY casino_gameround_fk2, 
  DROP INDEX casino_gameround_producttimestamp, 
  DROP INDEX casino_gameround_timestamp, 
  DROP INDEX casino_gameround_idx3, 
  DROP INDEX GAMEID, 
  DROP INDEX ID_PRODUCT, 
  DROP INDEX casino_gameround_idx2, 
  DROP INDEX casino_gameround_idx4, 
  DROP PRIMARY KEY,  
  ADD PRIMARY KEY(`ID_GAMEROUND`), 
  ADD INDEX casino_gameround_timestamp (TIMESTAMP), 
  ADD INDEX casino_gameround_producttimestamp (TIMESTAMP,ID_PRODUCT), 
  ADD INDEX casino_gameround_idx3 (DEVICEID), 
  ADD INDEX GAMEID (GAMEID), 
  ADD INDEX casino_gameround_idx2 (USERID), 
  ADD INDEX ID_PRODUCT (ID_PRODUCT), 
  ADD INDEX casino_gameround_idx4 (PRODUCTID);

and 

ALTER TABLE  `casino_gameround`  
ADD CONSTRAINT casino_gameround_fk1 FOREIGN KEY(ID_PRODUCT) REFERENCES  `tooladmin_products`(ID_PRODUCT), 
ADD CONSTRAINT casino_gameround_fk2 FOREIGN KEY(GAMEID) REFERENCES  `casino_game`(ID_GAME) ;

How to repeat:
just have 2 db equal with linked tables with FK. then on one of the 2 to perform the changes so that the script identifies the differences.

Suggested fix:
if it is not already present, a parameter (eg --fk-after-tables) so you do not run into this problem (change struttra with data already present)
[10 Sep 2016 10:06] MySQL Verification Team
Hello fabio,

Thank you for the report.
Could you please provide exact test case(create tables etc), and mysqldbcompare versions details to reproduce this issue at our end?

Thanks,
Umesh
[11 Oct 2016 1:00] Bugs System
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".