#136 MySql error: Incorrect integer value
I'm getting an error when updating contact addresses:
Failed to do query: Incorrect integer value: '' for column 'refid' at row 1
In query: REPLACE INTO address VALUES ('',2,'','','','','','',0,'55584862341','','','')
The empty string can no longer to converted to a numeric.
This has been mentioned in the forums but not logged as a bug:
http://sourceforge.net/projects/tab-2/forums/forum/590643/topic/1900733?message=4693227
http://sourceforge.net/projects/tab-2/forums/forum/590643/topic/1736307?message=4406126
This is relevant to MySql 5.0. When tab3 wants to insert a null to a decimal or integer field, the default value supplied is an empty string. MySql 5.0 behaves differently to MySql 4 in that it doesn't automatically convert this to 0.
See MySql bug entry, which state its not a bug, but a code design issue:
http://bugs.mysql.com/bug.php?id=18551
http://www.webmasterworld.com/forum88/13136.htm
I've implemented a workaround for the issue by adding the following statement to the query method on the DB class.
set sql_mode = '';
It now works with MySql 5.0.
Thanks,
Scott.
I just came across this myself (I recently moved my personal copy of address book to a new server, so hadn't seen this bug before.
I fixed it in a different way, by copying code from another section of the software which actually does the right thing and includes the refid when it is needed and uses the auto-increment value from the database when it isn't. We probably need to check other places in the code that use auto-increment values that depended on the old mysql behavior.
This particular issue is fixed in rev. 701