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

[7 May 2003 16:16] Timothy Crider
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
[8 May 2003 19:03] Miguel Solorzano
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.
[1 Jun 2003 14:34] Michael Widenius
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.
[28 Apr 2005 2:07] LifeNT koson
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.