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 16:16	Modified:	1 Jun 2003 14:34
Reporter:	Timothy Crider	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Linux (Red Hat 8.0)
Assigned to:	Arjen Lentz	CPU Architecture:	Any

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.