Bug #94849 mysqldbcompare in SQL mode re-orders indexes
Submitted: 1 Apr 2019 9:14 Modified: 30 Sep 2021 17:02
Reporter: Buchan Milne Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Utilities Severity:S3 (Non-critical)
Version:1.6.5 OS:MacOS
Assigned to: CPU Architecture:Any

[1 Apr 2019 9:14] Buchan Milne
Description:
mysqldbcompare in SQL mode re-orders the order of columns in a composite index.

We use mysqldbcompare to generate Liquibase SQL-formatted changelogs, and one of our changesets was generated incorrectly due to this bug.

If it is not intended to retain column order for composite indexes, there should be a warning or error.

How to repeat:
Create two databases each with a table named index_test with definition:

CREATE TABLE `index_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `state_id` int(11) NOT NULL,
  `user_id` varchar(24) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Add a composite key to the table in the 2nd database, but in an order different to the column order (to correctly match the way data is queried on this table):
create index test_user_name_state_id_idx on index_test(user_id, name, state_id, id);

CLI command:

mysql -u root -e 'create database diff_test_1;create table diff_test_1.index_test ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `state_id` int(11) NOT NULL, `user_id` varchar(24) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`));create database diff_test_2;create table diff_test_2.index_test ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `state_id` int(11) NOT NULL, `user_id` varchar(24) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `test_user_name_state_id_idx` (`user_id`,`name`,`state_id`,`id`));'

Diff the two databases with mysqldbcompare:
$ mysqldbcompare -t --skip-data-check --skip-row-count -d sql -c --server1=$MYSQL_USER:$MYSQL_PWD@localhost diff_test_1:diff_test_2
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# Checking databases diff_test_1 and diff_test_2 on server1
#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     index_test                              FAIL    SKIP    SKIP
#
# Transformation for --changes-for=server1:
#

ALTER TABLE `diff_test_1`.`index_test`
  ADD INDEX test_user_name_state_id_idx (name,id,user_id,state_id);

# Database consistency check failed.
#
# ...done

Observe that the index order is incorrect, it should have been:
  ADD INDEX test_user_name_state_id_idx (user_id,name,state_id,id);

For comparison, use a different diff output format:
$ mysqldbcompare -t --skip-data-check --skip-row-count -c --server1=$MYSQL_USER:$MYSQL_PWD@localhost diff_test_1:diff_test_2
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# Checking databases diff_test_1 and diff_test_2 on server1
#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     index_test                              FAIL    SKIP    SKIP
#
# Object definitions differ. (--changes-for=server1)
#

   `state_id` int(11) NOT NULL,
   `user_id` varchar(24) DEFAULT NULL,
   `name` varchar(255) DEFAULT NULL,
-  PRIMARY KEY (`id`)
+  PRIMARY KEY (`id`),
+  KEY `test_user_name_state_id_idx` (`user_id`,`name`,`state_id`,`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

# Database consistency check failed.
#
# ...done

Suggested fix:
I think the problem is somewhere in utilities/common/table.py's Index.__get_column_list

    def __get_column_list(self, backtick_quoting=True):
...
        col_list = []
        for col in self.columns:
            name, sub_part = (col[0], col[1])
            if backtick_quoting:
                name = quote_with_backticks(name, self.sql_mode)
            if sub_part > 0:
                col_str = "{0}({1})".format(name, sub_part)
            else:
                col_str = name
            col_list.append(col_str)
        return ', '.join(col_list)

Or:Table.get_tbl_indexes

        """Return a result set containing all indexes for a given table

        Returns result set
        """
        res = self.server.exec_query("SHOW INDEXES FROM %s" % self.q_table)
[1 Apr 2019 9:59] MySQL Verification Team
Hello Buchan Milne,

Thank you for the report and test case.
Verified as described.

regards,
Umesh
[30 Sep 2021 17:02] Buchan Milne
There is a pull request at https://github.com/mysql/mysql-utilities/pull/17 that works.

However, it wasn't accepted in https://bugs.mysql.com/bug.php?id=83839