Incorrect integer value: '' for column 'x'
I ran into a issue today due to a difference between MySql 4.1 and 5.1, or at least that is what I'm currently thinking. We have a ColdFusion template that is running as a scheduled task to determine new and updated records in a table on one server and then either updates or creates new records on a second server at a different location. Pretty straight forward, and it has worked well for the last several years.
Now, I'm in the process of replacing the second server in this equation and ran into the following error when trying to run the update template:
Incorrect integer value: '' for column 'x' in line 1.
The original table that is being queried contains an integer field that is sometimes null. When the update runs, it is producing the error mentioned above. On my current live server running MySQL 4.1.22, this scenario completes without an error. On the new server running MySQL 5.1.42 I get the error.
Both the original and destination tables have this field defined as an integer. What appears to be happening is that my template is attempting to update the existing record with a blank string which is causing the error. I set the cf_sql_type as an integer in myy cfqueryparam tag, but it still did not stop the error. I solved this issue by updating the blank values on the original table to 0, but still am wondering why this was valid in MySQL 4.1 but not 5.1.
If anyone has any further information about this I would love some feedback in the comments. Is this purely a difference in MySQL versions, or could my change from CF7 to CF9 also be part or all of the issue? In this scenario, updating the null values in the orginal table to 0 works out OK but it may not always be an option, so I'm wondering if there is another or a better way to deal with this issue.
Comments
Oh right can't use < tags ... cfqueryparam cfsqltype="cf_sql_INT" value="#someValueString#" null="#not len(someValueString)#" /
@Marcel - Thanks for the feedback and advice. I will look into your suggestions.
@ Marcel - using the null="#not len(someValueString)#" attribute of the cfqueryparam tag resolved my issue. Thanks again for the suggestion.
hi it has something to do with setting mysql to be run as a strict database server when you first configure it. ive run into this problem before... at the moment im trying to insert '0' into a int field ad its returning a incorect integer error. think im gonna take off the strict mode :( insert into yada (checked) values ('0') lol is being a pain :P good luck "The option to enable strict mode is not adequately explained in the dialog box. You should make a choice based upon whether or not there is a chance you will be using your database with applications, such as a proprietary shopping cart, whose code you cannot modify — specifically, any SQL queries. If that is the case, then do not enable strict mode, because it could result in an error if and when the application tries to set a non-nullable column to a default value by specifying an empty value of a different type, and you get an error 1366."
CF displays NULL values in the database as ''. The way mysql handles '' to integer fields is probably changed since version 5.x (just a guess of me). A correct way to fix this would be to write null to the field, since that is the correct value.