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: | |
| 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
[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]
MySQL Verification Team
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>
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.