AnsweredAssumed Answered

Update fails if mySQL mode Strict

Question asked by Francesca Shiekh on Apr 16, 2015
I just moved off my DEV database server onto what will eventually be PRD.
MySQL on this new server was set to strict and in the cases module it failed to save some records.

The Sugar Log showed:

[FATAL]  Query Failed: UPDATE cases_cstm SET  case_last_interaction_date_c='' ,case_assigned_date_c='' ,case_queue_c='' ,case_closed_date_c='' ,case_last_status_change_date_c='' ,case_ba_feedback_comments_c='' ,case_production_system_c='' ,case_business_impact_c='' ,case_workaround_c='' 
<all the other custom module fields, some blank some not>
WHERE id_c='2e4f2bfa-e401-57e6-282a-552337180cff'
MySQL error 1292: Incorrect datetime value: '' for column 'case_last_interaction_date_c' at row 1

I do want that to be empty/null, it gets updated when we send out an email or log a call with the customer. i.e. records the last time we had a communication with the requestor.

Turns out that when mySQL is set to Strict datetime fields don't take an empty string for a value and the entire save on the custom table fails.

Question is, should I be really running in non-strict mode? Or is there some other underlying issue with how the update sql is written.