Cannot add case type in multilingual installation with MariaDB 10.2 strict mode
When adding a new case type via the UI at /en/civicrm/a/#/caseType/new in a multilingual site there is an error
"error_message": "DB Error: unknown error",
"mode": 16,
"debug_info": "INSERT INTO `civicrm_case_type_en_US` (`name` , `is_active` ) VALUES ('ExampleNew' , 1 ) [nativecode=1423 ** Field of view 'sitex.civicrm_case_type_en_US' underlying table doesn't have a default value]",
"type": "DB_Error",
"user_info": "INSERT INTO `civicrm_case_type_en_US` (`name` , `is_active` ) VALUES ('ExampleNew' , 1 ) [nativecode=1423 ** Field of view 'sitex.civicrm_case_type_en_US' underlying table doesn't have a default value]",
"to_string": "[db_error: message=\"DB Error: unknown error\" code=-1 mode=callback callback=CRM_Utils_REST::fatal prefix=\"\" info=\"INSERT INTO `civicrm_case_type_en_US` (`name` , `is_active` ) VALUES ('ExampleNew' , 1 ) [nativecode=1423 ** Field of view 'sitex.civicrm_case_type_en_US' underlying table doesn't have a default value]\"]",
Environment information
Drupal 7.80 / CiviCRM 5.36.1 MariaDB 10.2 with strict mode
Cause
MySQL having a strict mode set which won’t allow INSERT or UPDATE commands with empty fields where the schema doesn’t have a default value set.
Solution
I solved it with disabling strict mode by adding sql_mode=NO_ENGINE_SUBSTITUTION to /etc/my.cnf (and restarting mysql). Because the new Case Type was made from /en/civicrm/a/#/caseType/new I got first an empty label, but could after saving change the label to the desired case type human name.
I suppose there could also be a default value defined for these multilingual tables.