incorrect integer value '' for column 'id' at row 1 mysql Solution

I just found that one of my applications no longer works in one server but same program working another server. 

A lot of head scratching ensued, as the same code is working still working perfectly elsewhere. I checked the php versions and configs, checked the apache setups, all similar enough not to cause any problems. The only clue to what was going on was a mysql log file entry of:

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

Now, this isn't a bug, as int_column = '', is basically saying set the int_column to be a string of no value or a NULL value, which is not an integer, so it shouldn't be allowed to be inserted into the table, but previously MySQL would have converted this to the empty value and auto-incremented my int_column.

You can test this using the following:

CREATE TABLE `int_column_test` (

`int_column_id` INT NOT NULL

) TYPE = MYISAM ;


INSERT INTO int_column_test SET int_column_id = '';

You should get the Incorrect integer value: '' for column 'int_column_id' at row 1 error.

This is an sql-mode issue, the mode defines what SQL syntax should be supported and what kind of data validation should be performed. In my problem MySQL is trying to assign an empty string to an auto-increment INT field and, as we should all know, strings into INTs don't go. Cue errors and the script dies.

Longer term I am going to have to re-work my code to fix this issue, but in the short term, I am going to reduce the sensitivity of the control. To lower the level of data validation we can set the sql-mode to a lower level or comment it out altogether.

Short Term Solution

Edit the my.cnf (my.ini in windows) file and find and comment out the line:

#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

The STRICT_TRANS_TABLES or it might be STRICT_ALL_TABLES is the bit that is causing the problem for me and with that line gone, STRICT mode is disallows invalid or missing values in a statement and aborts the command.

With this line commented out, STRICT mode is no longer applied and MySQL should now insert adjusted values for invalid or missing values and more specifically, it should allow my empty string to be put into the int column.

Long Term Solution

I think, I will have to remove all mentions of the id field from my INSERT commands, so that a value is not assigned and the auto-increment is left to do its own thing.