MySQL Bugs: #17660: Unable to auto increment

Bug #17660	Unable to auto increment
Submitted:	23 Feb 2006 7:06	Modified:	24 Feb 2006 11:37
Reporter:	Shawn Berg	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Windows (Win XP Professional)
Assigned to:		CPU Architecture:	Any

Description:
When creating an id field that auto_increments,such as the following: id INT(10) NOT NULL auto_increment and set as a Primary Key, I get this error message when attempting to perform a simple INSERT command: #1264 - Out of range value adjusted for column 'id' at row 1

How to repeat:
Same as above

Suggested fix:
Unknown

Thank you for a problem report. Please, send the results of SHOW CREATE TABLE statement for your table and exact INSERT statement that gives you this error message.

Here is my CREATE statement as requested:

CREATE TABLE `event` (
  `event.id` INT(10) NOT NULL AUTO_INCREMENT, 
  `event.date` VARCHAR(25) DEFAULT 'None Specified' NOT NULL, 
  `event.type` CHAR(3) DEFAULT 'N/A' NOT NULL, 
  `event.item` VARCHAR(250) DEFAULT 'N/A' NOT NULL, 
  `event.city` VARCHAR(25) DEFAULT 'None Specified' NOT NULL, 
  `event.state` CHAR(3) DEFAULT 'N/A' NOT NULL, 
  `event.contact` VARCHAR(25) DEFAULT 'None Specified' NOT NULL, 
  `event.phone` VARCHAR(15) DEFAULT 'None Specified' NOT NULL,
  PRIMARY KEY (`event.id`)
)
TYPE = myisam;

My INSERT statement as requested:

 INSERT INTO `event` ( `event.id` , `event.date` , `event.type` , `event.item` , `event.city` , `event.state` , `event.contact` , `event.phone` )
VALUES (
'', 'Mar. 13, 2006', 'PW', 'Test Event 1', 'Duluth', 'MN', 'Wiley Coyote', '555-555-1234'
) 

Which, again, produces this error:

#1264 - Out of range value adjusted for column 'event.id' at row 1

I've got the same results:

mysql> CREATE TABLE `event` (
    ->   `event.id` INT(10) NOT NULL AUTO_INCREMENT,
    ->   `event.date` VARCHAR(25) DEFAULT 'None Specified' NOT NULL,
    ->   `event.type` CHAR(3) DEFAULT 'N/A' NOT NULL,
    ->   `event.item` VARCHAR(250) DEFAULT 'N/A' NOT NULL,
    ->   `event.city` VARCHAR(25) DEFAULT 'None Specified' NOT NULL,
    ->   `event.state` CHAR(3) DEFAULT 'N/A' NOT NULL,
    ->   `event.contact` VARCHAR(25) DEFAULT 'None Specified' NOT NULL,
    ->   `event.phone` VARCHAR(15) DEFAULT 'None Specified' NOT NULL,
    ->   PRIMARY KEY (`event.id`)
    -> )
    -> TYPE = myisam;
Query OK, 0 rows affected, 1 warning (0.14 sec)

mysql> INSERT INTO `event` ( `event.id` , `event.date` , `event.type` , `event.item` ,
    -> `event.city` , `event.state` , `event.contact` , `event.phone` )
    -> VALUES (
    -> '', 'Mar. 13, 2006', 'PW', 'Test Event 1', 'Duluth', 'MN', 'Wiley Coyote',
    -> '555-555-1234'
    -> ) ;
ERROR 1264 (22003): Out of range value adjusted for column 'event.id' at row 1
mysql> show variables like 'sql%'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
*************************** 2. row ***************************
Variable_name: sql_notes
        Value: ON
*************************** 3. row ***************************
Variable_name: sql_warnings
        Value: ON
3 rows in set (0.01 sec)

The problem here is the default SQL Mode. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html).

As a workaround either use explicit 0 or NULL:

mysql> INSERT INTO `event` ( `event.id` , `event.date` , `event.type` , `event.item` ,
    -> `event.city` , `event.state` , `event.contact` , `event.phone` )
    -> VALUES (
    -> NULL, 'Mar. 13, 2006', 'PW', 'Test Event 1', 'Duluth', 'MN', 'Wiley Coyote',
    -> '555-555-1234'
    -> ) ;
Query OK, 1 row affected (0.06 sec)

Or reset sql mode:

mysql> set sql_mode= '';
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `event` ( `event.id` , `event.date` , `event.type` , `event.item` ,
    -> `event.city` , `event.state` , `event.contact` , `event.phone` )
    -> VALUES (
    -> '', 'Mar. 13, 2006', 'PW', 'Test Event 1', 'Duluth', 'MN', 'Wiley Coyote',
    -> '555-555-1234'
    -> ) ;
Query OK, 1 row affected, 1 warning (0.00 sec)

But I think it is really not a bug, because manual (same page) clearly states:

"For non-transactional tables, the behavior is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged."