MySQL Bugs: #14280: Error 1067 : Invalid default value on create table with auto

Bug #14280	Error 1067 : Invalid default value on create table with auto_inrement field
Submitted:	25 Oct 2005 3:06	Modified:	11 Jan 2006 9:21
Reporter:	Robert Kroes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.1.4	OS:	Windows (Windows XP)
Assigned to:	Mike Lischke	CPU Architecture:	Any

Description:
When creating a new table containing an auto_increment column, the generated code includes 'default 0' in the 'create table' statement. This causes the following error message:-

  MySQL Error Number 1067
  Invalid default value for 'idtest'

Can someone please confirm this?

How to repeat:
From the GUI create a simple table as follows:-

1. Connect to a MySQL 5.0.15 server (WinXP in my case)
2. Select a database (eg. 'test')
3. Click on 'Create Table'
4. Type: 'test' in the table name and press <enter>
5. Press <enter> to accept the default column name 'idtest'
6. Press <enter> to accept default colum type (integer)

Then click 'Apply' & 'Execute'.

Suggested fix:
Work-around: explicitly type "null" in the default value column.

Omit the 'DEFAULT 0' keyword from the generated statement when no value is given in the GUI for the 'Default Value' of the column. 

Alternatively, change it to 'DEFAULT NULL'.

1.1.3 worked OK for me. It has generated and successfully executed the following statement:

CREATE TABLE `test`.`test` (
  `idtest` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(`idtest`)
)
ENGINE = InnoDB;

when performing the described actions.

And in 1.1.4 it generates:

CREATE TABLE `test`.`ttt` (
  `idttt` INTEGER UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT,
  PRIMARY KEY(`idttt`)
)
ENGINE = InnoDB;

that gives the obvious error message upon execution.

So, it is a bug, introduced in version 1.1.4.

The same bug is in Query Browser 1.1.17. Bug #14419 was marked as a duplicate of this one. They both should be fixed by the same change in code.

The same problem exists in 1.1.5.

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