Incorrect integer value: '' for column 'id' at row 1

I am trying to insert into my mySQL database. The first column is the 'id' column, since its an auto_increment field, I left it blank. For some reason, I am unable to insert and I am getting the error mentioned below. I appreciate any help with this.

I am getting the following error while trying to insert:

Incorrect integer value: '' for column 'id' at row 1

my query

INSERT INTO workorders VALUES('', ?, ?, ?, ?, ?)

3

7 Answers

That probably means that your id is an AUTO_INCREMENT integer and you're trying to send a string. You should specify a column list and omit it from your INSERT.

INSERT INTO workorders (column1, column2) VALUES (?, ?)

2

To let MySQL generate sequence numbers for an AUTO_INCREMENT field you have three options:

1. specify list a column list and omit your auto_incremented column from it as njk suggested. That would be the best approach. See comments.
2. explicitly assign NULL
3. explicitly assign 0

3.6.9. Using AUTO_INCREMENT:

...No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers.

These three statements will produce the same result:

INSERT INTO workorders (`priority`, `request_type`) VALUES(?, ?, ...)
INSERT INTO workorders VALUES(NULL, ?, ...)
INSERT INTO workorders VALUES(0, ?, ...)

6

Try to edit your my.cf and comment the original sql_mode and add sql_mode = "".

vi /etc/mysql/my.cnf

sql_mode = ""

save and quit...

service mysql restart

5

For the same error in wamp/phpmyadmin, I have edited my.ini, commented the original :

;sql-mode= "STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

and added sql_mode = "".

This is because your data sending column type is integer and your are sending a string value to it.

So, the following way worked for me. Try with this one.

INSERT INTO workorders VALUES ( null, ?, ?, ?, ?, ?
)

Don't use 'null'. use it as null without single quotes.

1

If there is phpMyAdmin just go to variables and look for SQL Mode, variable, edit it and remove all the contents of the variable, then save. That should solve this problem too for those that do not want to touch the code.

When using mysql server, you can fix it by typing set global sql_mode=''.