Bug #76619 mysqldiff drops and recreates PK and indexes when it is not needed
Submitted: 8 Apr 2015 12:35 Modified: 16 Dec 2016 22:48
Reporter: Miguel Angel Nieto Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Utilities Severity:S3 (Non-critical)
Version:1.5.4,1.6.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysql utilities, mysqldiff

[8 Apr 2015 12:35] Miguel Angel Nieto
Description:
mysqldiff's SQL output sometimes include some changes that are not needed, usually related with keys and PK. 

How to repeat:
I have two servers. 

On server1:

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

On Server 2:

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `last_name` (`last_name`,`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

So, the only differences are:

- On employees there is a UNIQUE KEY on Server2 that doesn't exist on Server1.

- On departments dept_name is varchar(256) while on Server1 is varchar(40).

If you run mysqldiff it will output the following SQL:

# Transformation for --changes-for=server1:
#

ALTER TABLE `employees`.`employees`
  DROP PRIMARY KEY,
  ADD PRIMARY KEY(`emp_no`),
  ADD INDEX last_name (last_name,first_name);

# Transformation for --changes-for=server1:
#

ALTER TABLE `employees`.`departments`
  DROP INDEX dept_name,
  ADD UNIQUE INDEX dept_name (dept_name),
  CHANGE COLUMN dept_name dept_name varchar(256) NULL;

Problems:

The ALTER for employees adds a regular INDEX when it is actually a UNIQUE. IT also drops and adds the Primary Key, something that is not needed at all.

The ALTER for deparments drops the index dept_name to add it again. Once again, that's not needed.
[8 Apr 2015 12:45] Miguel Angel Nieto
"The ALTER for employees adds a regular INDEX when it is actually a UNIQUE."

That's not correct, sorry.
[9 Apr 2015 9:18] Umesh Shastry
Hello Miguel,

Thank you for the report.
Could you please tell us which exact mysql utilities version you are using? I've tried with 1.5.4, 1.6.1 and not seeing primary key recreation issue.
But, observed that dept_name is dropped/recreated.

Thanks,
Umesh
[9 Apr 2015 9:19] Umesh Shastry
// 1.5.4/1.6.1

[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]#
[root@cluster-repo mysql-5.6.24]# mysqldiff --force --server1=root@localhost:15000 --server2=root@localhost:15000 db1:db2 --changes-for=server1 --skip-table-options --difftype=sql
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing `db1` to `db2`                                         [FAIL]

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

ALTER TABLE `db1`.`departments`
  DROP INDEX dept_name,
  ADD UNIQUE INDEX dept_name (dept_name),
  CHANGE COLUMN dept_name dept_name varchar(256) NULL;

# Comparing `db1`.`employees` to `db2`.`employees`                 [FAIL]
# Transformation for --changes-for=server1:
#

ALTER TABLE `db1`.`employees`
  ADD INDEX last_name (last_name,first_name);

Compare failed. One or more differences found.

########

[root@cluster-repo setups]# mysqldiff --version
MySQL Utilities mysqldiff version 1.5.4
License type: GPLv2

[root@cluster-repo setups]# mysqldiff --force --server1=root@localhost:15000 --server2=root@localhost:15000 db1:db2 --changes-for=server1 --skip-table-options --difftype=sql
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing `db1` to `db2`                                         [FAIL]

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

ALTER TABLE `db1`.`departments`
  DROP INDEX dept_name,
  ADD UNIQUE INDEX dept_name (dept_name),
  CHANGE COLUMN dept_name dept_name varchar(256) NULL;

# Comparing `db1`.`employees` to `db2`.`employees`                 [FAIL]
# Transformation for --changes-for=server1:
#

ALTER TABLE `db1`.`employees`
  ADD INDEX last_name (last_name,first_name);

Compare failed. One or more differences found.
[9 Apr 2015 9:23] Miguel Angel Nieto
I'm using:

# rpm -qa |grep -i utilities
mysql-utilities-1.5.4-1.el7.noarch
[16 Dec 2016 22:48] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Utilities 1.6.5 release, and here's the changelog entry:

When the mysqldiff utility compared columns for indexes, it failed to take
into account cardinality changes. That is, if the cardinality of one
column differed from the corresponding column in the compared table, the
index was considered different. This fix eliminates unnecessarily changes
to indexes by the utility when cardinality differences are detected.

Thank you for the bug report.