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)
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)