Bug #72920 incorrect generated sql-code (AI on secondary col) in EER Diagram

• Submitted: 9 Jun 2014 15:26
• Modified: 27 Aug 2014 0:29
• Reporter: Denis Kuzmin
• Status: Closed
• Category: MySQL Workbench: Modeling
• Severity: S2 (Serious)
• Version: 6.1.6
• OS: Windows
• CPU Architecture: Any
• Tags: auto_increment, EER-Diagram, models, MySQL Workbench, primary key

[9 Jun 2014 15:26] Denis Kuzmin

Description:
Wrong PRIMARY KEY(...) assignment with AUTO_INCREMENT on secondary column in a multiple-column index 
/doc: http://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html

Problem wth the Models - EER Diagram on MySQL Workbench v6.1.6 Community edition.

How to repeat:
how to reproduce (see also my video - https://drive.google.com/file/d/0B0-ygoNBOTDcb3VxSEtka25qWkE ):

* Place a New Table
* Set the MyISAM engine
* Add new field:
** `cat_id` INT UNSIGNED NOT NULL
** `id` SMALLINT UNSIGNED NOT NULL
* Set PK for both fields and set AI for `id`
* Synchronize Model (or see Copy SQL..)

finally designer created the following code:

CREATE TABLE IF NOT EXISTS `mwbug`.`test_ai` (
  `cat_id` INT UNSIGNED NOT NULL,
  `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`, `cat_id`))  <--- problem here, should be (`cat_id`, `id`)
ENGINE = MyISAM

INSERT INTO `mwbug`.`test_ai` (`cat_id`, `id`) VALUES ('1', NULL), ('1', NULL), ('2', NULL);
SELECT * FROM `test_ai`;

+--------+----+
| cat_id | id |
+--------+----+
|      1 |  1 |
|      1 |  2 |
|      2 |  3 |
+--------+----+

However, this resolved if we use Move Up/Down for `id` field (Only! after setting the AI property):

CREATE TABLE IF NOT EXISTS `mwbug`.`test_ai` (
  `cat_id` INT UNSIGNED NOT NULL,
  `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`cat_id`, `id`))
ENGINE = MyISAM

INSERT INTO `mwbug`.`test_ai` (`cat_id`, `id`) VALUES ('1', NULL), ('1', NULL), ('2', NULL);
SELECT * FROM `test_ai`;

+--------+----+
| cat_id | id |
+--------+----+
|      1 |  1 |
|      1 |  2 |
|      2 |  1 |
+--------+----+

see video for more detail...

also we use triggers with InnoDB tables, so this option for MyISAM tables should be clear as in doc. 
Otherwise we can make a mistake :(

[9 Jun 2014 15:28] Denis Kuzmin

illustration

Attachment: mwb_bug-ai.avi (video/avi, text), 394.00 KiB.

[10 Jun 2014 8:14] MySQL Verification Team

Hello Denis,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

[27 Aug 2014 0:29] Philip Olson

Fixed as of the upcoming MySQL Workbench 6.2.2 release, and here's the changelog entry:

The generated code from an EER diagram would sometimes add the Primary Key
in the wrong order.

Thank you for the bug report.