After working with SugarCRM tech support, we determined the error I was getting was because the SQL in the PHP was setting the value of an INTEGER field to ‘’ – a character string. That should have never been allowed in MySql, but it was and made the data 'valid' automatically. Any version from 5.0.2 above or any modern database server will reject that. So, we made a quick change to the code in the modules that we knew it affected – Product Templates, Product Types and Product Categories. We simply did a check for it being ‘’ and replaced it with a zero for the Parent_ID value it was referencing.

As you might guess, this same problem could be spread throughout your code.

I found another one. If you add two custom fields – a date and a character one, then update just the character one, it will fail. The reason is the update statement sets the date field to ‘’ – which is also illegal.

HOWEVER, I have located a workaround!

Make sure the 5.0.x version of MySql does not have the mode STRICT_TRANS_TABLES defined. This can be changed in the my.ini file, command line parameter or I am hoping the Sugar development could turn that mode on from your code for that session, using the set session sql_mode=’modes’ – until you get all your code straightened out (which needs to be done).

Details are here on the data validation changes to MySql.
http://dev.mysql.com/doc/refman/5.0/...alid-data.html

Details are here on the SQL Server mode:
http://dev.mysql.com/doc/refman/5.0/...-sql-mode.html


Wayne Brantley