MySQL Bugs: #20853: Table Editor generates improper PK when selecting Auto Increment

Bug #20853	Table Editor generates improper PK when selecting Auto Increment
Submitted:	4 Jul 2006 20:23	Modified:	4 Sep 2006 23:05
Reporter:	David Shrewsbury	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.18	OS:	MacOS (Mac OSX 10.4.7)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any

Description:
When creating a new table using Table Editor, if you add a new column
and make it the primary key, then choose to make the same column
autoincrement, the primary key will contain this new column more than
once. Each time Auto Increment checkbox is clicked, the column is appended
to the primary key definition.

How to repeat:
1) In Query Browser (or Administrator), select any database (e.g., test) and choose Create Table...
2) Click + to add a new column
3) Select Primary Key checkbox
4) Select Auto Increment checkbox
5) Click Apply button

The SQL generated looks like:

CREATE TABLE `test`.`new_table` (
  `newcolumn` INT NOT NULL DEFAULT 0,
  PRIMARY KEY(`newcolumn`, `newcolumn`)
)
ENGINE = MYISAM
CHARACTER SET utf8;

Notice that the 'newcolumn' column is included twice in the PK definition.
Each time you select or deselect the Auto Increment checkbox, this column
gets repeatedly appended to the PK definition.

Thank you for a bug report. Please, try to repeat with a newer version, 1.2, available at http://dev.mysql.com/downloads/gui-tools/5.0.html, and inform about the reuslts.

This still occurs with the version of Query Browser in the MySQL GUI Tools 5.0-r1b bundle. I also discovered that the behaviour is different when you click the "AUTO INC" checkbox next to the new column in the Columns table rather than the "Auto Increment" checkbox in the Column Details tab area. You would expect the same behaviour, but not so.

-Dave

I've tried repeating the bug but was not able to. 
If I click Auto Increment (in either lower checkbox or in the list view)
the AUTO_INCREMENT keyword appears correctly in the SQL.
Is your machine PPC or Intel? Also, what is the difference you get
when you click in the different checkboxes? Please provide details
like what happens visually when you click and also the resulting SQL.
Thank you.

Hello Alfredo. I am using a PPC. The issue is not with the AUTO_INCREMENT keyword appearing in the table definition (although my initial report somehow left this out). The issue is with the PRIMARY KEY definition in the resulting SQL. The issue appears only when using both the Primary Key and Auto Increment checkboxes in the Column Details tab view at the bottom of Table Editor. The order in which you select these two boxes makes a difference. You must first select the Primary Key checkbox, then the Auto Increment checkbox below it. When you do these steps, you will see the column name appear twice in the primary key definition after hitting the Apply button. If you select these boxes in the reverse order (Auto Increment then Primary Key), or if you use the Auto Inc check box in the list view, then the behavior works as expected and the primary key is generated correctly.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html