MySQL Bugs: #65169: Workbench Utility mysqldiff is unreliable

Bug #65169	Workbench Utility mysqldiff is unreliable
Submitted:	1 May 2012 19:27	Modified:	3 May 2013 5:02
Reporter:	Van Stokes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Utilities	Severity:	S2 (Serious)
Version:	5.2.39, 1.2.0	OS:	Linux (Ubuntu 11.10 x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	mysqldiff

Description:
MySQL Server: 5.5.22 x64 on Linux Ubuntu 11.10 x64.

The mysqldiff utility is not reliable. It finds differences even though no difference exists. 

How to repeat:
SHOW CREATE TABLE lapcost;

CREATE TABLE `lapcost` (
  `type` varchar(1) NOT NULL DEFAULT '',
  `askfld` varchar(38) NOT NULL DEFAULT '',
  `cntrl` varchar(3) NOT NULL DEFAULT '',
  `cstcde` varchar(3) DEFAULT NULL,
  `amount` decimal(9,2) DEFAULT NULL,
  `paiddt` int(8) DEFAULT NULL,
  `eacode` varchar(1) DEFAULT NULL,
  `gltrml` varchar(2) DEFAULT NULL,
  `vendnm` varchar(25) DEFAULT NULL,
  `vend` varchar(5) DEFAULT NULL,
  `invnum` varchar(25) DEFAULT NULL,
  `invdat` int(8) DEFAULT NULL,
  `entdat` int(8) DEFAULT NULL,
  `entrby` varchar(8) DEFAULT NULL,
  `enttim` varchar(6) DEFAULT NULL,
  `postby` varchar(8) DEFAULT NULL,
  `posted` varchar(1) DEFAULT NULL,
  `posttm` varchar(6) DEFAULT NULL,
  `chknum` int(10) DEFAULT NULL,
  `venref` varchar(25) DEFAULT NULL,
  `expvoy` varchar(10) DEFAULT NULL,
  `inlvoy` varchar(9) DEFAULT NULL,
  `unit` varchar(28) DEFAULT NULL,
  `cstdr` varchar(8) DEFAULT NULL,
  `updtby` varchar(8) DEFAULT NULL,
  `upddat` int(8) DEFAULT NULL,
  `updtim` varchar(6) DEFAULT NULL,
  `postdt` int(8) DEFAULT NULL,
  `mstvn` varchar(5) DEFAULT NULL,
  `uadrs1` varchar(25) DEFAULT NULL,
  `uadrs2` varchar(25) DEFAULT NULL,
  `uacity` varchar(18) DEFAULT NULL,
  `uastat` varchar(2) DEFAULT NULL,
  `uazip` varchar(5) DEFAULT NULL,
  `uaphn` varchar(15) DEFAULT NULL,
  `uafax` varchar(15) DEFAULT NULL,
  `editky` varchar(12) DEFAULT NULL,
  `appddt` int(8) DEFAULT NULL,
  `appdby` varchar(8) DEFAULT NULL,
  `appdtm` varchar(6) DEFAULT NULL,
  `autocd` varchar(1) DEFAULT NULL,
  `cmmnts` varchar(25) DEFAULT NULL,
  `mstck` int(10) DEFAULT NULL,
  `agtvn` varchar(5) DEFAULT NULL,
  `key022` varchar(22) DEFAULT NULL,
  `faecde` varchar(1) DEFAULT NULL,
  `actcod` varchar(3) DEFAULT NULL,
  `cutamt` decimal(9,2) DEFAULT NULL,
  `cutdte` int(8) DEFAULT NULL,
  `cuttim` varchar(6) DEFAULT NULL,
  `voidby` varchar(8) DEFAULT NULL,
  `voiddt` int(6) DEFAULT NULL,
  `voidtm` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`type`,`askfld`,`cntrl`),
  KEY `lapcost_idx1` (`type`,`cstcde`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Create this table in two difference databases. Then run:

mysqldiff --difftype=sql --force --quiet --server1=user:password@locahost --changes-for=server2 sourcedb.lapcost:destdb.lapcost

It will produce this output:

# Transformation for --changes-for=server2:
#

ALTER TABLE destdb.lapcost 
  DROP PRIMARY KEY, 
  DROP INDEX lapcost_idx1, 
  ADD PRIMARY KEY(type,cntrl,askfld), 
  ADD INDEX lapcost_idx1 (type,cstcde);

Suggested fix:
Not sure.

I can not repeat this on Windows while running against local 5.5.23.

Thank you for the report.

Please connect to both databases and run query SHOW CREATE TABLE problem_table on each of them, then send us results. Would be better if you attach them as text files.

Btw is it same MySQL server? If they are different is version same?

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".

This bug is real and it's not fixed. Im using version 5.2.42 and it complains about diffs on primary keys (it deletes and recreates keys in the sql diffs) for cases where there're no diffs.

Thank you for the feedback.

But we can not repeat this issue on our side. Please answer questions I asked in previous comment:

Please connect to both databases and run query SHOW CREATE TABLE problem_table on each of them, then send us results. Would be better if you attach them as text files.

Btw is it same MySQL server? If they are different is version same?

Im not giving create table since it's proprietary information, but the tables are myisam and have the exact same schema. Im running this on mountain lion (osx). The output bellow does not make any sense, it's dropping all the indexes and creating them again. The versions of sql server are the same. below is the mysqldiff version.

mysqldiff --version
MySQL Utilities mysqldiff version 1.0.6
Copyright (c) 2010, 2012 Oracle and/or its affiliates. All rights reserved.
This program is free software; see the source for copying
conditions. There is NO warranty; not even for MERCHANTABILITY or
FITNESS FOR A PARTICULAR PURPOSE, to the extent permitted by law.

ALTER TABLE te_main.tb_bgp_historic 
  DROP PRIMARY KEY, 
  DROP PRIMARY KEY, 
  DROP INDEX ROUND_PREFIX, 
  DROP PRIMARY KEY, 
  DROP PRIMARY KEY, 
  DROP INDEX PATH_ID, 
  DROP INDEX COMMITTED_TIME, 
  ADD PRIMARY KEY(monitor_id,path_id,prefix_id,round_id), 
  ADD INDEX COMMITTED_TIME (committed_time), 
  ADD INDEX ROUND_PREFIX (prefix_id,round_id), 
  ADD INDEX PATH_ID (path_id);

Ricardo,

thank you for the feedback. But without information we can not repeat, then fix the bug.

You can obscure your tables: simply replace field names with f1, f2, f3 and replace all comments with something like 'foobar', finally rename tables. After you did so, try to use mysqldiff again to ensure noting missed and issue is still repeatable.

But Im telling you the tables are the same, and your output does not make any sense. So there's clearly something pretty wrong in the code. Btw, this is not happening in the gui version.

Ricardo,

thank you for the table definition. I hided your comment in case if it is still contains sensitive data. Problem is order of keys in both tables.

Shorter test case.

1. On server 1:

CREATE TABLE `t2` (
  `f1` int(11) DEFAULT NULL,
  `f2` int(11) DEFAULT NULL,
  KEY `f1` (`f1`),
  KEY `f2` (`f2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

2. On server 2:

CREATE TABLE `t2` (
  `f1` int(11) DEFAULT NULL,
  `f2` int(11) DEFAULT NULL,
  KEY `f2` (`f2`),
  KEY `f1` (`f1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

3. Run mysqldiff:

$ mysqldiff --difftype=sql --force --quiet --server1=root:@127.0.0.1:13000 --changes-for=server2 --server2=root:@127.0.0.1:13010 test.t2:test.t2
# Transformation for --changes-for=server2:
#

ALTER TABLE test.t2 
  DROP INDEX f1, 
  DROP INDEX f2, 
  ADD INDEX f1 (f1), 
  ADD INDEX f2 (f2);

So this is clearly a bug, the order of key statements is irrelevant, and as i mentioned the GUI version of workbench does not signal anything - when will you fix this?

also, why would primary keys have to be dropped?

Fixed in release-1.2.2.

Fixed as of the upcoming MySQL Utilities 1.2.2, and here's the changelog entry:

The "mysqldiff" utility would consider two tables as different if the
columns or indexes were ordered differently.

Thank you for the bug report.

This bug is not fixed. I can reproduce it on:
MySQL Utilities mysqldiff version 1.3.1 (part of MySQL Workbench Distribution 5.2.47) on ubuntu 12.04 (compiled from source)

--changes-for argument will bring back all the wrong diffs based on order of statements. Also it seems you guys still didnt get this right. If server1 has table t1 and server2 does not, the output show show the sql statement creating table t1, just like the GUI version does. As it is right now, the command line tools are pretty useless.

is anyone still maintaining this code? it's completely useless as it is, mysqldiff produces imaginary diffs dropping all the keys just because mysql decides to output keys in create table in different order

The example test case was tested with Utilities release-1.3.5 as follows:

$ python ./scripts/mysqldiff.py --server1=root@localhost:13001 --server2=root@localhost:13002 --diff=sql --force --changes-for=server2 db1:db1
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing `db1` to `db1`                                         [PASS]
# Comparing `db1`.`t2` to `db1`.`t2`                               [PASS]
Success. All objects are the same.

If you are still encountering the problem, please reopen this bug and state which version of MySQL Utilities you are using and include the output of the command so that we can investigate further.

Will check later today - btw do you guys have any plans of having an ubuntu package in your downloads section? i only see debian and redhat ...

just checked it; it's better for sure;) it still complains in cases where the schema is the same, but the auto-inc value is differentm e.g.:

ALTER TABLE `te_meta`.`tb_rc_urls` 
AUTO_INCREMENT=1759;

either you add an option to ignore auto-inc values or ignore it by default

MYSQLDIFF VERSION: MySQL Utilities mysqldiff version 1.3.6 (part of MySQL Workbench Distribution 5.2.47)

PROBLEMS:

[1] Wrong schema. If you ALTER TABLE <schema2>.<table>, you want the FK references
<schema2>.<other table>, not <schema1>.<other table>. Please see the example below.

[2] The ability to ignore AUTO_INCREMENT, as there are times one is interested only in the structure,
not the data.

[3] Mysqldiff reports differences that does not exist. The only difference between the two tables
shown below is the order the two FK's appear in the output of mysqldump.

EXAMPLE/TEST CASE:

# mysqldiff --server1=root:xxxxxxxx@localhost:1234 --server2=tungsten:xxxxxxxx@t1db-a01:5678 --difftype=sql --force  --change
s-for=server2 auth.brws_lbl_spc:TEST_DC_AUTH.brws_lbl_spc 
# server1 on localhost: ... connected.
# server2 on t1db-a01: ... connected.
# Comparing auth.brws_lbl_spc to TEST_DC_AUTH.brws_lbl_spc         [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `test_dc_auth`.`brws_lbl_spc` 
  DROP FOREIGN KEY FK_Browse_LBL_SPC_TO_PRD, 
  DROP FOREIGN KEY FK_Browse_LBL_SPC_TO_LBL, 
  DROP INDEX UK_BRWS_LBL_SPC, 
  DROP PRIMARY KEY, 
  DROP INDEX BROWSE_LBL_SPC_LBL_IDX, 
ADD CONSTRAINT FK_Browse_LBL_SPC_TO_LBL FOREIGN KEY(LBL_ID) REFERENCES `auth`.`lbl`(ID), 
ADD CONSTRAINT FK_Browse_LBL_SPC_TO_PRD FOREIGN KEY(PRD_ID) REFERENCES `auth`.`prd`(ID), 
  ADD PRIMARY KEY(`ID`), 
  ADD INDEX BROWSE_LBL_SPC_LBL_IDX (LBL_ID), 
  ADD UNIQUE INDEX UK_BRWS_LBL_SPC (PRD_ID), 
AUTO_INCREMENT=1;

Compare failed. One or more differences found.

# mysqldump --compact -u root -pxxxxxxxx -h localhost -P 16426 auth brws_lbl_spc
Warning: Using a password on the command line interface can be insecure.
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `brws_lbl_spc` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `LBL_ID` bigint(20) DEFAULT NULL,
  `PRD_ID` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_BRWS_LBL_SPC` (`PRD_ID`),
  KEY `BROWSE_LBL_SPC_LBL_IDX` (`LBL_ID`),
  CONSTRAINT `FK_Browse_LBL_SPC_TO_LBL` FOREIGN KEY (`LBL_ID`) REFERENCES `lbl` (`ID`),
  CONSTRAINT `FK_Browse_LBL_SPC_TO_PRD` FOREIGN KEY (`PRD_ID`) REFERENCES `prd` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

# mysqldump --no-data --compact -u tungsten -pxxxxxxxx -h t1db-a01 -P 6425 TEST_DC_AUTH brws_lbl_spc
Warning: Using a password on the command line interface can be insecure.
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `brws_lbl_spc` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `LBL_ID` bigint(20) DEFAULT NULL,
  `PRD_ID` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_BRWS_LBL_SPC` (`PRD_ID`),
  KEY `BROWSE_LBL_SPC_LBL_IDX` (`LBL_ID`),
  CONSTRAINT `FK_Browse_LBL_SPC_TO_PRD` FOREIGN KEY (`PRD_ID`) REFERENCES `prd` (`ID`),
  CONSTRAINT `FK_Browse_LBL_SPC_TO_LBL` FOREIGN KEY (`LBL_ID`) REFERENCES `lbl` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=163 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

Continue from previous comments and adding two more problems with the
mysqldiff test case:

[4] The mysqldiff output cannot be run, it gets an error:

ERROR 1022 (23000): Can't write; duplicate key in table '#sql-25f2_498cf'

[5] After running first half ("DROP", but you have to omit DROP PRIMARY KEY as it is auto increment column
and you cannot drop the key), and second half ("ADD") separately, you still get the diff.

Pura,

thank you for the feedback. Please open separate bug report about issue you hit.

The key order problem (is back|still happens) in 1.3.6:

$ mysqldiff --skip-table-options --server1=root:[...]@127.0.0.1:49164 --server2=root:[...]@127.0.0.1:49165 fixture.pakket:fixture.pakket
# server1 on 127.0.0.1: ... connected.
# server2 on 127.0.0.1: ... connected.
# Comparing fixture.pakket to fixture.pakket                       [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- fixture.pakket
+++ fixture.pakket
@@ -45,7 +45,7 @@
   `has_newrelic` tinyint(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`pakket_id`),
   UNIQUE KEY `afkorting` (`afkorting`),
+  KEY `actueel` (`actueel`),
   KEY `auto_upgrade_next` (`auto_upgrade_next`),
-  KEY `actueel` (`actueel`),
   CONSTRAINT `pakket_ibfk_1` FOREIGN KEY (`auto_upgrade_next`) REFERENCES `pakket` (`pakket_id`) ON DELETE SET NULL ON UPDATE SET NULL
 )
Compare failed. One or more differences found.

$ mysqldiff --version
MySQL Utilities mysqldiff version 1.3.6 (part of MySQL Workbench Distribution 5.2.47) 
License type: GPLv2

Python: 2.7.6

Server version: 5.5.31-0+wheezy1 (Debian)

Pura,  Cipriano,

looks like both issues are not repeatable in version 1.5.3. Please check.