| 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: | |
| 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: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]
MySQL Verification Team
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]
MySQL Verification Team
// 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.
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.