MySQL Bugs: #76510: mysqldiff tool drops indexes and adds duplicates and causes errors

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

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.

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.

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

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>