Bug #76510 mysqldiff tool drops indexes and adds duplicates and causes errors
Submitted: 27 Mar 2015 15:23 Modified: 29 Mar 2015 9:33
Reporter: Michael Vuoncino Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Utilities Severity:S3 (Non-critical)
Version:1.5.3, 1.6.1 OS:Linux (debian wheezy)
Assigned to: CPU Architecture:Any

[27 Mar 2015 15:23] Michael Vuoncino
Description:
I'm using the mysqldiff tool (v1.5.3) from MySQL to synchronize the schema between two databases. The databases should have the same schema, but would like to produce a file that only has the ALTER TABLE differences between them so it can (after thorough testing) be run in production.

The problem is that the mysqldiff tool produces some weirdness. For example:

ALTER TABLE `database1`.`ACCOUNT_TRANSACTIONS` 
DROP INDEX MATCHING_TRANSACTION_ID, 
DROP INDEX PAYEE_ID, 
ADD INDEX CHECK_NUMBER (ACCOUNT_ID),
ADD INDEX PAYEE_ID (PAYEE_ID), 
ADD INDEX CHECK_NUMBER (PAYEE_ID,CHECK_NUMBER);
It seems to not only drop the indexes, but tries to recreate the new index through two statements that produce an error. The final schema should have CHECK_NUMBER (ACCOUNT_ID, PAYEE_ID, CHECK_NUMBER) as the index.

What might be causing this?

How to repeat:
Use two tables that differ only by indexes.
[27 Mar 2015 15:30] Trent Lloyd
Please run SHOW CREATE TABLE on the table from both servers and post it, along with the exact command line parameters you are using so we can try and reproduce.
[27 Mar 2015 15:47] Michael Vuoncino
Command: /usr/local/bin/mysqldiff --force --server1=root@localhost database1:database2 --skip-table-options --difftype=sql 

Final table:

CREATE TABLE `ACCOUNT_TRANSACTIONS` (
  `TRANSACTION_ID` int(12) NOT NULL AUTO_INCREMENT,
  `ACCOUNT_ID` int(1) DEFAULT NULL,
  `REF_NUMBER` varchar(15) DEFAULT NULL,
  `PAYEE_ID` int(6) DEFAULT NULL,
  `ACCOUNT_CHART_ID` int(12) DEFAULT NULL,
  `TRANSACTION_AMOUNT` decimal(10,2) DEFAULT NULL,
  `TRANSACTION_DATE` date DEFAULT NULL,
  `MEMO` varchar(100) DEFAULT NULL,
  `ACCOUNT_BALANCE` decimal(10,2) DEFAULT NULL,
  `DEBIT_OR_CREDIT` char(1) DEFAULT 'D',
  `MATCHING_TRANSACTION_ID` int(11) DEFAULT '0',
  `RECONCILED_DATE` date DEFAULT NULL,
  `RECONCILED_EMPLOYEE_ID` int(12) DEFAULT NULL,
  `TIME_UPDATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CHECK_NUMBER` bigint(20) unsigned DEFAULT NULL,
  `STATEMENT_ID` int(12) DEFAULT NULL,
  PRIMARY KEY (`TRANSACTION_ID`),
  KEY `PAYEE_ID` (`PAYEE_ID`),
  KEY `CHECK_NUMBER` (`CHECK_NUMBER`,`ACCOUNT_ID`,`PAYEE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7837406 DEFAULT CHARSET=latin1

Original table:

CREATE TABLE `ACCOUNT_TRANSACTIONS` (
  `TRANSACTION_ID` int(12) NOT NULL AUTO_INCREMENT,
  `ACCOUNT_ID` int(1) DEFAULT NULL,
  `REF_NUMBER` varchar(15) DEFAULT NULL,
  `PAYEE_ID` int(6) DEFAULT NULL,
  `ACCOUNT_CHART_ID` int(12) DEFAULT NULL,
  `TRANSACTION_AMOUNT` decimal(10,2) DEFAULT NULL,
  `TRANSACTION_DATE` date DEFAULT NULL,
  `MEMO` varchar(100) DEFAULT NULL,
  `ACCOUNT_BALANCE` decimal(10,2) DEFAULT NULL,
  `DEBIT_OR_CREDIT` char(1) DEFAULT 'D',
  `MATCHING_TRANSACTION_ID` int(11) DEFAULT '0',
  `RECONCILED_DATE` date DEFAULT NULL,
  `RECONCILED_EMPLOYEE_ID` int(12) DEFAULT NULL,
  `TIME_UPDATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CHECK_NUMBER` bigint(20) unsigned DEFAULT NULL,
  `STATEMENT_ID` int(12) DEFAULT NULL,
  PRIMARY KEY (`TRANSACTION_ID`),
  KEY `PAYEE_ID` (`PAYEE_ID`),
  KEY `MATCHING_TRANSACTION_ID` (`MATCHING_TRANSACTION_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=5165891 DEFAULT CHARSET=latin1
[29 Mar 2015 9:33] Umesh Shastry
Thank you for the report and test case.

// my test shows

[root@cluster-repo mysql-5.6.24]# mysqldiff --version
MySQL Utilities mysqldiff version 1.6.1
License type: GPLv2

[root@cluster-repo mysql-5.6.24]# mysqldiff --force --server1=root@localhost --server2=root@localhost database1:database2 --changes-for=server1 --skip-table-options --difftype=sql               # server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing `database1` to `database2`                             [FAIL]

# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- `database1`
+++ `database2`
@@ -1,1 +1,1 @@
-CREATE DATABASE `database1` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `database2` /*!40100 DEFAULT CHARACTER SET latin1 */
# WARNING: Could not generate SQL statements for differences between `database1` and `database2`. No changes required or not supported difference.
# Comparing `database1`.`ACCOUNT_TRANSACTIONS` to `database2`.`ACCOUNT_TRANSACTIONS`   [FAIL]
# Transformation for --changes-for=server1:
#

ALTER TABLE `database1`.`ACCOUNT_TRANSACTIONS`
  DROP INDEX PAYEE_ID,
  DROP INDEX MATCHING_TRANSACTION_ID,
  ADD INDEX CHECK_NUMBER (ACCOUNT_ID),
  ADD INDEX PAYEE_ID (PAYEE_ID),
  ADD INDEX CHECK_NUMBER (PAYEE_ID,CHECK_NUMBER);

Compare failed. One or more differences found.

// Results in duplicate keyname

mysql>
mysql> ALTER TABLE `database1`.`ACCOUNT_TRANSACTIONS`
    ->   DROP INDEX PAYEE_ID,
    ->   DROP INDEX MATCHING_TRANSACTION_ID,
    ->   ADD INDEX CHECK_NUMBER (ACCOUNT_ID),
    ->   ADD INDEX PAYEE_ID (PAYEE_ID),
    ->   ADD INDEX CHECK_NUMBER (PAYEE_ID,CHECK_NUMBER);
ERROR 1061 (42000): Duplicate key name 'CHECK_NUMBER'
mysql>