MySQL Bugs: #11031: Adding new table through designer throws 1064 MySQL error

Bug #11031	Adding new table through designer throws 1064 MySQL error
Submitted:	2 Jun 2005 11:53	Modified:	3 Jun 2005 14:58
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S1 (Critical)
Version:	1.1.9	OS:	Windows (Windows XP SP1)
Assigned to:		CPU Architecture:	Any

Description:
When adding a table through the designer and selected a primairy key column with Auto INC, the tool throws MySQL error: 1064

How to repeat:
Add a table through the designer and selected a primairy key column with Auto INC, the designer creates the following SQL:

CREATE TABLE `pmsmetrics`.`Test` (
  `ID` INTEGER UNSIGNED NOT NULL DEFAULT  AUTO_INCREMENT,
  `Namre` VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY(`ID`)
)
TYPE = InnoDB;

Which throws MySQL error: 1064

Suggested fix:
Remove the default keyword by hand:

CREATE TABLE `pmsmetrics`.`Test` (
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `Namre` VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY(`ID`)
)
TYPE = InnoDB;

This problem is present when you add any integer column to a table.  The designer will always add a DEFAULT statement even when you have nothing in the default field on the designer page.  When nothing is put into the form field, then the table designer generates a DEFAULT statement without a following value, which is invalid SQL.  I'm not sure if this is a problem with the validation of the form (the DEFAULT value is mandatory) or a problem with the SQL generation.  I usually just remove the spurious DEFAULT statements by hand and it works just fine.

Thanks for your bug report.

pic showing the bug

Attachment: Image1.png (image/x-png, text), 12.33 KiB.

added a screenshot showing the bug

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/