MySQL Bugs: #100578: mysqldiff messes the order of multiple PK if PK and fields are in diff. order

Bug #100578	mysqldiff messes the order of multiple PK if PK and fields are in diff. order
Submitted:	19 Aug 2020 14:24	Modified:	20 Aug 2020 12:11
Reporter:	Julien Ricard	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	1.6.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysql utilities, mysqldiff

Description:
When comparing two tables containing a primary key on multiple fields, if the fields are not in the same order than the primary key is defined, mysqldiff outputs an erroneous sql diff :

ALTER TABLE `dest_db`.`the_table`
  DROP PRIMARY KEY,
  DROP PRIMARY KEY,
  ADD PRIMARY KEY(`id`,`network_id`);

This sql query fails obviously. The only thing that is different between source and dest is the order between the fields versus the primary key.

The workaround is obviously to set the primary key on the same order than the fields, but it's not always doable.

How to repeat:
source table :

CREATE TABLE `the_table`  (
  `id` bigint(20) NOT NULL,
  `network_id` bigint(20) NOT NULL,
  `type` enum('SOMETHING','SOMETHINGELSE') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`network_id`, `id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

dest on other DB :

CREATE TABLE `the_table`  (
  `id` bigint(20) NOT NULL,
  `network_id` bigint(20) NOT NULL,
  `type` enum('SOMETHING','SOMETHINGELSE') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `network_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

As you can see, the primary key is on source (network_id, id) and on the dest : (id, network_id)

I get the issue when running this command :

mysqldiff --changes-for=server2 --difftype=sql -v --server1=u:p@h1 --server2=u:p@h2 dbsource.the_table:dbdest:the_table

Suggested fix:
The alter suggested by mysqldiff is wrong

Actually, my bug description is not correct. When I run the mysqldiff the first time, the sql that is generated contains this string for the ALTER :

ADD PRIMARY KEY(`id`,`network_id`),

The two fields are not in the correct order.

Then, when I run the same command again, I get what I describe in the bug report.

fixed the title of the bug report

ok to add to this issue about primary key :
- mysqldiff adds two DROP PRIMARY KEY in the SQL output when the PK is on multiple fields
- mysqldiff tries to add the PRIMARY KEY with fields ordered not in the same way than the other base, putting them in alphabetical order :

ALTER TABLE `db`.`the_table`
  DROP PRIMARY KEY,
  DROP PRIMARY KEY,
  ADD PRIMARY KEY(`id`,`network_id`);

It should output :

ALTER TABLE `db`.`the_table`
  DROP PRIMARY KEY,
  ADD PRIMARY KEY(`network_id`, `id`);

ok I thought it was an alphabetical order, I have another table with a PK on 3 fields, and the result seems not correlated with any order I could understand.

So it seems to be unreliable with the fields order, and the PK fields.

Hi Mr. Ricard,

Thank you for your bug report.

I repeated your test case with standalone `mysqldiff` utility. 

Do note that this is a very low priority bug, since all this functionality will be soon moved to MySQL Shell.

Verified as reported.