MySQL Bugs: #103416: mysqldiff cannot generate indexes in correct order

Bug #103416	mysqldiff cannot generate indexes in correct order
Submitted:	22 Apr 2021 2:44	Modified:	22 Apr 2021 7:45
Reporter:	yongkang lu	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Utilities	Severity:	S2 (Serious)
Version:	1.6.5	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	mysqldiff

Description:
The table structure is as  follows:

#test1
CREATE TABLE `test1` (
  `id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `deleted` TINYINT (1) NOT NULL DEFAULT '0',
  `cluster_type` VARCHAR (32) NOT NULL DEFAULT 'one_way',
  PRIMARY KEY (`id`),
  KEY `idx_2` (`deleted`, `cluster_type`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

#test2
CREATE TABLE `test2` (
  `id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `deleted` TINYINT (1) NOT NULL DEFAULT '0',
  `cluster_type` VARCHAR (32) NOT NULL DEFAULT 'one_way',
  PRIMARY KEY (`id`),
  KEY `idx_2` (`cluster_type`, `deleted`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

Contrast statement:

mysqldiff --server1=user:"password"@host:port --server2=user:"password"@host:port --changes-for=server2 --skip-table-options --difftype=sql testdb.test1:testdb.test2

After comparison,you get the DDL syntax is as follows:
ALTER TABLE `sthdb`.`test2`
  DROP INDEX idx_2,
  ADD INDEX idx_2 (cluster_type,deleted);

The correct syntax is:
ALTER TABLE `sthdb`.`test2`
DROP INDEX idx_2,
ADD INDEX idx_2 (deleted,cluster_type);

###
It‘s obviously that  the order of the generated DDL statements is wrong.

How to repeat:
You can modify the mysql/utilities/common/sql_transform.py file,there is a function _get_index_sql_clauses. When rows is not empty,you can sort the rows before entering the loop.

MySQL Utilities is now covered under Oracle Lifetime Sustaining Support
Per Oracle's Lifetime Support policy, as of May 30, 2018, MySQL Utilities is covered under Oracle Sustaining Support. Some features of Utilities are on the roadmap for Shell, users are encouraged to migrate to MySQL Shell - https://dev.mysql.com/doc/mysql-shell/8.0/en/