DB Error : Uknown error (When a new profile is created in multilingual CiviCRM setup)

Question

Trying to create a new profile to get attached in contribution page, when hit save, a message box "DB Error: unknown error" appears.

This happening on multilingual civiCRM setup on multiple versions 5.35.2 to 5.50.3. On single language CiviCRM setup this error doesn't appear instead.

Looking in civiCRM log appears

    Jun 30 15:39:23  [debug] $Query = SELECT path, data, UNIX_TIMESTAMP(expired_date) as expires FROM civicrm_cache WHERE group_name = "checks" AND path = "systemStatusCheckResult"

Jun 30 15:39:23  [info]  QUERY DONE IN 0.000821  seconds. Result is 0 rows by 3 columns. 

Jun 30 15:39:23  [debug] $Query = BEGIN

Jun 30 15:39:23  [debug] $Query = SELECT path, data, UNIX_TIMESTAMP(expired_date) as expires FROM civicrm_cache WHERE group_name = "contact-20fields" AND path = "custom importableFields UFGroup_0_0_0_0__0__0__0_0__1_7acce319"

Jun 30 15:39:23  [info]  QUERY DONE IN 0.000765  seconds. Result is 0 rows by 3 columns. 

Jun 30 15:39:23  [debug] $Query = INSERT INTO `civicrm_uf_group_en_US` (`is_active` , `title` , `frontend_title` , `help_pre` , `help_post` , `is_update_dupe` , `name` , `created_id` , `created_date` ) VALUES ( 1 , 'New Membership Signup' ,  NULL ,  NULL ,  NULL ,  1 , 'New_Membership_Signup' ,  2 ,  20220630153923 ) 

Jun 30 15:39:23  [debug] $Query = COMMIT

When look in database this record does not exist.

----- Update ----

CiviCRM ver: 5.35.2 to 5.50.3, Wordpress ver: 5.9.1, PHP ver: 7.3.31, MariaDB ver: 10.3.31

Thanks for any guidance!

Answer 1

It's probably because the language fields are set to NOT NULL in the database.

Find out what the underlying table(s) are, and then with phpMyAdmin (or another database tool) you can set these fields to NULL.

I remember I had to do this with the label_en_US, label_fr_FR... columns in the table civicrm_option_value.

Answer 2

Can you do SELECT @@sql_mode? My guess is it has STRICT_TRANS_TABLES in it, which makes multilingual very difficult to use on mariadb. If that's the problem, you can configure mysql to remove that from sql_mode. See also https://github.com/civicrm/civicrm-core/pull/22647#issuecomment-1025391599

Either that or you have logging turned on at administer - system settings - misc, which isn't very compatible with multilingual.

Answer 3

I've had a similar issue and it turned out that when creating a new profile or profile field, it auto-generates a blank translation. That's okay the first time, but the second time it doesn't like it and generates an sql error.

The only work-around solution I've found is to figure out the blank translation and put something in it (which your probably want to do anyway).

If you've got sql access, then look at civicrm_uf_group and see if the non language/en_US fields are blank.