MySQL Bugs: #11415: Primary Key definition ignored when creating tables

Bug #11415	Primary Key definition ignored when creating tables
Submitted:	17 Jun 2005 14:26	Modified:	2 Dec 2005 0:17
Reporter:	Daniel Kasak (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.0.22	OS:
Assigned to:	Alfredo Kojima	CPU Architecture:	Any

Description:
When I use Administrator to create tables under OS X, the primary key definition is ignored. If the primary key is an auto_increment column, this prevents the table from being successfully created.

The problem can be worked around by manually adding the 'primary key' directory in the appropriate place in the SQL when applying updates.

How to repeat:
Create table in Administrator, with a Primary Key defined.
Click button to save changes.
Inspect constructed SQL and note lack of 'primary key' directive.

Suggested fix:
Inspect primary key checkbox and act on it.

Hello,

Thank you for the report, but I can't reproduce it.
Please, describe step by step how you create table and show CREATE TABLE statement.

'works' every time for me :)

Open administrator.
Select 'table actions' ==> 'create table'.

Add a field. Call it 'ID'.
Select the following options in the 'column details' tab at the bottom:
- primary key
- auto increment

Click 'Apply'. Inspect the SQL. Mine says:

CREATE TABLE `incidents`.`new_table` (
  `ID` INT NOT NULL AUTO_INCREMENT
)
ENGINE = MYISAM
CHARACTER SET utf8;

It *SHOULD* say:

CREATE TABLE `incidents`.`new_table` (
  `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
)
ENGINE = MYISAM
CHARACTER SET utf8;

I'm also seeing this problem on MySQL Administrator 1.0.22 on OS X 10.4.2.

I'm also seeing this in the Linux version of administrator ( 1.0.20 ).
From memory I've tried upgrading to the latest version of administrator, but it won't compile ... separate issue, I know.

I'm also reconsidering the severity level I initially assigned. All edited tables are dropping their primary keys! Can you imagine what Postges / Oracle / SQL Server users would say if they learned of such an embarrassing bug? Perhaps there are bugs of a higher priority? I'd like to hear of them - I need a good laugh on Monday mornings :)

Just upgraded to 1.0.22 ( compiled against mysql-5.0.9 ) on Linux and I'm still seeing this behaviour.

In a shamless attempt to keep the momentum of this bug at or above it's present level, I'm posting some more information which may or may not be relevant...

When I edit a table from administrator, there is an extra column, with a NULL name, below all the 'real' columns. Apart from having no name, it's defined as INT(11) not null. This occurs on ALL tables.

Perhaps this is related to administrator not recognising the primary key? Parsing error? Screenshot(s) available on request.

I'm seeing the same problem in 1.1.0 on OS X.  It seems that the checkboxes for unique and primary key are being ignored.  In the simplest case, if I try to create a table called "foo" with one column "id" which has the primary key checkbox checked, then the SQL generated is:

CREATE TABLE `mydb`.`foo` (
  `id` INT NOT NULL
)
ENGINE = MYISAM
CHARACTER SET utf8;

The only way I can get it to create a primary key is by explicitly creating one in the indices tab.  The "Primary Key" checkbox in the column details tab seems to be ignored.  Ditto for the "Unique" checkbox, though "Not NULL" and "Auto Increment" seem to work.

I'm running MySQL Administrator 1.1.0 on Mac OS X v10.4.2 (fully patched).  

Here's what the MySQL Administrator "Information" tab says:

Server Status: Server is Running

Connected to MySQL Server Instance:
   User: root
   Host: localhost
   Socket: /tmp/mysql.sock

Server Information:
   MySQL Version: MySQL 4.1.14-standard via socket
   Network Name: localhost
   IP: 127.0.0.1

Client Information
   Version: MySQL Client Version 5.0.10
   Network Name: foo.bar.com
   IP: 192.168.2.40
   Operating System: Darwin 8.2.0
   Hardware: 2x Power Macintosh (PowerPC G5) - 1795.0 Mhz, 2.0 GB RAM

(Note: the Mhz detected is incorrect, but somehow i doubt that's the problem!)

Proof :)

Attachment: wtf_no_primary_key.jpg (image/jpeg, text), 175.77 KiB.

You can see from the screenshot attached that the column 'ID' is detected as the primary key. However when you apply changes, the primary key part is stripped.

Hi. For the sake of supporting the other users here, I'm also having trouble creating simple tables. I just downloaded 1.1.0 today and am running it on OS X 10.4.3; MySQL version 5.0.15. I'm still somewhat new to databases so please forgive me if any of the following is obvious:

- I created a table `test` with one column `id` of type INT, Primary Key checked, Not NULL auto-checked and Auto-Increment checked and this was the statement generated...

CREATE TABLE `test`.`test` (
  `id` INT NOT NULL AUTO_INCREMENT
)
ENGINE = MYISAM
CHARACTER SET utf8;

The Primary Key was ignored. I hit Execute and this was the error generated...

Error executing SQL commands to create table.
Incorrect table definition; there can be only one auto column and it must be defined as a key (error 1075)

- So then I unchecked Primary Key and Auto-Increment and successfully created a table from this statement:

CREATE TABLE `test`.`test` (
  `id` INT NOT NULL
)
ENGINE = MYISAM
CHARACTER SET utf8;

- Last I selected the `id` column and re-checked Primary Key and Auto-Increment which successfully altered the column with this statement:

ALTER TABLE `test`.`test` MODIFY COLUMN `id` INTEGER NOT NULL AUTO_INCREMENT,
 ADD PRIMARY KEY(`id`);

*****

So, I guess I can create Primary Keys after the table/columns are created, however, obviously this is not what I prefer to have to do each time I create a new table.

*****

As an additional sidenote, entering a specific query successfully creates a table:

CREATE TABLE  `test` (
 `id` INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (  `id` )
);

Hope this helps and hope to get an update soon! :) Thanks.

One more comment which may or may not be related...

Each time I add a new column whether creating or altering a table, no matter what column type I choose, the column is inserted as an INT-type. And if I try to alter the column after -- with the type I want it to be -- it returns the message that either nothing was changed or that there is a syntax error.

Then when I look at the table through the UNIX shell, all the columns are INTs. :P

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html