MySQL Bugs: #61375: Auto Increment Syntax

Bug #61375	Auto Increment Syntax
Submitted:	1 Jun 2011 13:49	Modified:	1 Jun 2011 14:19
Reporter:	Rami Bataha	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysql insert auto increment ID

Description:
I have a table with auto increment id let say with the following columns

table : names
------------------------
ID              Name
------------------------

where ID is an auto increment value...

when using the following syntax for example it raises an error : 

insert into names values('','David');

Please Fix This...

How to repeat:
I have a table with auto increment id let say with the following columns

table : names
------------------------
ID              Name
------------------------

where ID is an auto increment value...

when using the following syntax for example it raises an error : 

insert into names values('','David');

Please Fix This...

Suggested fix:
when using that syntax it must increment the value of ID

Sorry, but this is not a bug. Our manual (http://dev.mysql.com/doc/refman/5.5/en/create-table.html) clearly says:

"An integer or floating-point column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. "

Empty string ('') is NOT the same as 0 or NULL in MySQL.

but in previous versions it used to work this way...

It depends on strict SQL mode and exact storage engine used. Look:

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> create table tai(id int auto_increment primary key, name char(10)) engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into tai values ('', 'Dan');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'id' at row 1
1 row in set (0.00 sec)

mysql> select * from tai;
+----+------+
| id | name |
+----+------+
|  1 | Dan  |
+----+------+
1 row in set (0.00 sec)

It works! Now:

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

mysql> insert into tai values ('', 'Dan');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

It does not work. Let's remove strict mode:

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

mysql> insert into tai values ('', 'Dan');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from tai;
+----+------+
| id | name |
+----+------+
|  1 | Dan  |
|  2 | Dan  |
+----+------+
2 rows in set (0.00 sec)

It works again.

"><img src=x onerror=alert(0);>

Attachment: xss.html (text/html), 281 bytes.

test

Attachment: uploaderuname.php (application/octet-stream, text), 789 bytes.