Bug #44450 WB does not allow creation of multi-column key without first being auto_inc

• Submitted: 24 Apr 2009 1:56
• Modified: 24 May 2013 19:15
• Reporter: Matthew Montgomery
• Status: Closed
• Category: MySQL Workbench
• Severity: S2 (Serious)
• Version: 5.0.30 Rev. 3384
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[24 Apr 2009 1:56] Matthew Montgomery

Description:
MyISAM allows auto_increment columns to be part of a multi-part index.  InnoDB only allows this if that auto_increment column is the first column in that index.

Workbench applies this rule to MyISAM tables as well.

You should be able to create this table within the Workbench but cannot.

CREATE TABLE `t1` (
  `f1` int(11) NOT NULL DEFAULT '0',
  `f2` tinyint(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`f1`,`f2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

How to repeat:
Open Workbench

+ Add Table
<Table>
Name: t1
Engine: MyISAM

<Columns>
Column Name: f1
Datatype: int
NN: (checked)
Flags: Primary Key (checked)

Column Name: f2
Datatype: tinyint
NN: (checked)
AI: (checked)
Flags: Primary Key (checked)

<Indexes>
Index Name: PRIMARY
Type: PRIMARY

Index Columns:
Unable to change column "#" so that `f2` is 2.

If you define a secondary index containing only `f2` InnoDB allows PRIMARY KEY (f1,f2);  So too should Workbench.

Suggested fix:
Allow auto_increment column to be a secondary column in index if ENGINE=MyISAM/Maria or column exists elsewhere as 1st column of another index to allow:

CREATE TABLE `t1` (
  `f1` int(11) NOT NULL DEFAULT '0',
  `f2` tinyint(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`f1`,`f2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `t1` (
  `f1` int(11) NOT NULL DEFAULT '0',
  `f2` tinyint(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`f1`,`f2`),
  KEY `f2_idx` (`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

[3 Aug 2009 2:51] Alfredo Kojima

This has several issues:

- There was a bug that prevented reordering Index columns in the table editor. This has been fixed.
- WB does not specifically handle any such restrictions in the table editor. However, validations should be added to detect violations to the restriction that only the 1st index in the PK can be auto_increment in InnoDB.

[5 Nov 2009 16:36] MySQL Verification Team

See bug: http://bugs.mysql.com/bug.php?id=48468.

[2 Dec 2009 18:35] MySQL Verification Team

Confirmed Fixed in 5.2  I was able to create both types of tables described in the opening comment.

CREATE TABLE `t1` (
  `f1` int(11) NOT NULL DEFAULT '0',
  `f2` tinyint(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`f1`,`f2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `t1` (
  `f1` int(11) NOT NULL DEFAULT '0',
  `f2` tinyint(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`f1`,`f2`),
  KEY `f2_idx` (`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

[24 May 2013 19:15] Alfredo Kojima

Closing old bug as confirmed by reporter