MySQL Bugs: #392: Problem With Auto_increment/Default Value in MySQL 4.1

Bug #392	Problem With Auto_increment/Default Value in MySQL 4.1
Submitted:	7 May 2003 18:16	Modified:	1 Jun 2003 16:34
Reporter:	Timothy Crider
Status:	Won't fix
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Linux (Red Hat 8.0)
Assigned to:	Bugs System	Target Version:

Description:
When issuing a create statement in MySQL 4.1 and trying to make a default value for the
ID that is auto_increment, MySQL rejects the table as an invalid default value for ID. My
Configuration Information is as follows:

./configure --prefix=/usr/local/mysql \
--libexecdir=/usr/local/libexec \
--libdir=/usr/local/lib \
--includedir=/usr/local/include \
--enable-local-infile \
--with-unix-socket-path=/var/lib/mysql/mysql.sock \
--with-mysqld-username=mysql \
--with-vio \
--without-isam \
--with-openssl=/usr/local \
--without-innodb

If you need to see anything else, please let me know.

How to repeat:
SQL-query :  

CREATE TABLE `a` (

`id` INT UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT,
PRIMARY KEY ( `id` ) 
) TYPE = MYISAM 

MySQL said: 

Invalid default value for 'id'

and
SQL-query :  

CREATE TABLE `a` (

`id` INT UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT,
PRIMARY KEY ( `id` ) 
) TYPE = MYISAM 

MySQL said: 

Invalid default value for 'id'

AND

SQL-query :  

CREATE TABLE `b` (

`my_id` INT UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT,
PRIMARY KEY ( `my_id` ) 
) TYPE = MYISAM 

MySQL said: 

Invalid default value for 'my_id'
Back

Thanks you for the bug report.

The behavior of 4.1.XX is different than 4.0.XX and 3.23.XX when is used
the default value with an auto_increment column.
This was added with the changeset: 1.1494.38.1 2003/03/26

/sql/sql_parse.cc

 if (default_value)
  {
.....
#ifdef MYSQL41000
    else if (type_modifier & AUTO_INCREMENT_FLAG)
    {
      net_printf(&thd->net, ER_INVALID_DEFAULT, field_name);
      DBUG_RETURN(1);
    }
#endif

So the bug should be:

1- Isn't documented that typing a default value for auto_increment column 
   isn't more supported on servers 4.1.XX.
2- The message error isn't well descriptive.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

I have now updated the CREATE and upgrading to 4.1 manual sections about this.

the auto_increment colum can only accept default value as "default null",yeah,can only
accept "null" as default value,all the other value is not invalied.