Getting Error No: 1366: Incorrect integer value

Created by Don Schoeman on on 2009-05-18
Keywords:
mysql installation troubleshooting
Last updated by:
Greg on on 2010-08-09

I installed PHPDevShell but I get the following MySQL error when doing various things within PHPDevShell:
1366 Incorrect integer value: '' for column 'column_name' at row 1

Solution:

This error could occur due to a new default setting within MySQL.

PHPDevShell would often use the "auto-fill" feature of MySQL to complete unspecified values for fields. For example:

INSERT INTO some_table (id_field, data_field) VALUES ('', 'Some Data')

If id_field is an auto-increment field MySQL would automatically "complete" this field by filling in the next generated number. However, from MySQL v5 this "feature" is enabled by default on some MySQL installations and is called the MySQL strict mode. To stop this error from happening, change the following value in your my.ini (Windows) my.cfg (linux) configuration file:

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

to

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"