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:
Category:MySQL Workbench: Modeling Severity:S5 (Performance)
Version:6.3.8 OS:Mac OS X (macOS 10.12.x Sierra)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[17 May 2017 10:52] Patryk Kozłowski
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.
[17 May 2017 12:51] MySQL Verification Team
Thank you for the bug report. Please provide a test case model file, use Files tab for. Thanks.
[18 May 2017 12:56] Patryk Kozłowski
Test case model file

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

[18 May 2017 14:18] MySQL Verification Team
First column

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

[18 May 2017 14:23] MySQL Verification Team
Thank you for the feedback. Please see attached screenshot isn't your case. Thanks.
[18 May 2017 14:51] Patryk Kozłowski
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.
[18 May 2017 14:53] Patryk Kozłowski
Demonstration video

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

[19 May 2017 11:36] Chiranjeevi Battula
Hello Patryk,

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