MySQL Bugs: #86353: Primary key order lost after updating column structure

Bug #86353	Primary key order lost after updating column structure
Submitted:	17 May 2017 10:52	Modified:	19 May 2017 11:36
Reporter:	Patryk Kozłowski	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S5 (Performance)
Version:	6.3.8	OS:	MacOS (macOS 10.12.x Sierra)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
Column sequence in primary key is lost every time you modify column structure, even if you don't touch any of primary key columns.

How to repeat:
1. Place new table on diagram. 
2. Add two columns of any type.
3. Contain both columns in the primary key.
4. Change # field order in index details for key PRIMARY. Make second #1 and first #2.
5. Add new column of any type to the table.
6. Observe that first column is again #1 and second #1 in primary key.

Suggested fix:
Don't reorder primary key column sequence after altering table.

Thank you for the bug report. Please provide a test case model file, use Files tab for. Thanks.

Test case model file

Attachment: test_case.mwb (application/octet-stream, text), 6.55 KiB.

First column

Attachment: au.png (image/png, text), 63.99 KiB.

Thank you for the feedback. Please see attached screenshot isn't your case. Thanks.

1. This is actually another bug that deserves a separate report. MySQL Workbench shows it every time you put AUTO_INCREMENT flag on a column that is not the first column in the table's primary key. There is no such requirement in InnoDB. According to the documentation (https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html) "To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column must be defined as part of an index such that it is possible to perform the equivalent of an indexed SELECT MAX(ai_col) lookup on the table to obtain the maximum column value.". record_id_UNIQUE index provided in the test case fulfills this requirement and thus the message is inappropriate and misleading.

2. Going back to the topic, I've recorded a very short video to demonstrate what's wrong with column order in primary key. You can watch it on youtube (https://youtu.be/e2sy7B-dpdc), I'll attach it to this report as well.

Demonstration video

Attachment: MWB_86353_bug_demonstration.mov.zip (application/zip, text), 2.43 MiB.

Hello Patryk,

Thank you for your feedback and test case.
Verified this behavior on MySQL Workbench in 6.3.9 version

Thanks,
Chiranjeevi.