#136 MySql error: Incorrect integer value

open
nobody
5
2010-04-27
2010-04-27
Anonymous
No

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.

Discussion