After enabling multilingual site, DB error 'underlying table doesn't have a default value' prevents saving any future language settings or enable extensions

Note: it only appears to be broken with WP sites, we have multilingual Drupal/CiviCRM sites with the same server setup not having the issue.

On the first one - the following should be NOT NULL in the option_value table

id
option_group_id
value
weight

All of which are set above.

The following fields have defaults - of these filter changed recently

name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Stores a fixed (non-translated) name for this option value. Lookup functions should use the name as the key for the option value row.', grouping varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Use to sort and/or set display properties for sub-set(s) of options within an option group. EXAMPLE: Use for college_interest field, to differentiate partners from non-partners.', filter int(10) unsigned DEFAULT NULL COMMENT 'Bitwise logic can be used to create subsets of options within an option_group for different uses.', is_default tinyint(4) DEFAULT '0' COMMENT 'Is this the default option for the group?', weight int(10) unsigned NOT NULL COMMENT 'Controls display sort order.', description text COLLATE utf8_unicode_ci COMMENT 'Optional description.', is_optgroup tinyint(4) DEFAULT '0' COMMENT 'Is this row simply a display header? Expected usage is to render these as OPTGROUP tags within a SELECT field list of options?', is_reserved tinyint(4) DEFAULT '0' COMMENT 'Is this a predefined system object?', is_active tinyint(4) DEFAULT '1' COMMENT 'Is this option active?', component_id int(10) unsigned DEFAULT NULL COMMENT 'Component that this option value belongs/caters to.', domain_id int(10) unsigned DEFAULT NULL COMMENT 'Which Domain is this option value for', visibility_id int(10) unsigned DEFAULT NULL, icon varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'crm-i icon class', color varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Hex color value e.g. #ffffff',

See also https://github.com/civicrm/civicrm-core/pull/17228, which isn't in 5.26 but which will be in 5.27

@DaveD @eileen thank you so much - I searched high and low and did not find that item but it does look like this might be a duplicate to pull/17228. I will install 5.27 when it's out and give it another go (and try and remember to search github too before posting!) Thanks again!

Actually as of Saturday you can get the 5.27 release candidate as a tarball - https://download.civicrm.org/latest/civicrm-RC-wordpress.zip (https://download.civicrm.org/latest/)

Note that 5.27 will be in rc for a month - really just a cool down in case anyone finds a bug. Personally I do go live with rc releases but it's a risk trade off (we do try to jump on any regressions that are reported on the rc)

We installed the RC release but are still experiencing issues.

Scenario 1: install was already broken by the bug (missing available currencies). Install to the RC release and attempt to re-add the available currency, still unable to save any changes to the language page:

[debug_info] => INSERT INTO civicrm_option_value_en_CA (option_group_id , label , value , is_default , weight , is_active ) VALUES ( 103 , 'CAD ($)' , 'CAD' , 1 , 1 , 1 ) [nativecode=1423 ** Field of view 'example_db.civicrm_option_value_en_CA' underlying table doesn't have a default value]

Scenario 2: Upgrade a not previously broken site to RC release and then enable multilingual options. It appears saving any settings on this page now works, great. But, enabling an extension still triggers the error (I tested by enabling CiviCASE):

string(348) "INSERT INTO civicrm_option_value_en_CA (option_group_id , label , value , name , is_default , weight , is_active , icon ) VALUES ( 99 , 'Task' , 'task' , 'task' , 0 , 1 , 1 , 'fa-check-circle' ) [nativecode=1423 ** Field of view 'example_db.civicrm_option_value_en_CA' underlying table doesn't have a default value]"

backtrace_5.27_enabling-an-extension.txt

Can you do show create table civicrm_option_value in mysql? Then we can see if it's the filter column and maybe for whatever reason it didn't get the default added. If it's not the filter, then maybe it will give us info about some other field.

CREATE TABLE `civicrm_option_value` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Option ID',
 `option_group_id` int(10) unsigned NOT NULL COMMENT 'Group which this option belongs to.',
 `value` varchar(512) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The actual value stored (as a foreign key) in the data record. Functions which need lookup option_value.title should use civicrm_option_value.option_group_id plus civicrm_option_value.value as the key.',
 `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Stores a fixed (non-translated) name for this option value. Lookup functions should use the name as the key for the option value row.',
 `grouping` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Use to sort and/or set display properties for sub-set(s) of options within an option group. EXAMPLE: Use for college_interest field, to differentiate partners from non-partners.',
 `filter` int(10) unsigned DEFAULT NULL COMMENT 'Bitwise logic can be used to create subsets of options within an option_group for different uses.',
 `is_default` tinyint(4) DEFAULT 0 COMMENT 'Is this the default option for the group?',
 `weight` int(10) unsigned NOT NULL COMMENT 'Controls display sort order.',
 `is_optgroup` tinyint(4) DEFAULT 0 COMMENT 'Is this row simply a display header? Expected usage is to render these as OPTGROUP tags within a SELECT field list of options?',
 `is_reserved` tinyint(4) DEFAULT 0 COMMENT 'Is this a predefined system object?',
 `is_active` tinyint(4) DEFAULT 1 COMMENT 'Is this option active?',
 `component_id` int(10) unsigned DEFAULT NULL COMMENT 'Component that this option value belongs/caters to.',
 `domain_id` int(10) unsigned DEFAULT NULL COMMENT 'Which Domain is this option value for',
 `visibility_id` int(10) unsigned DEFAULT NULL,
 `icon` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'crm-i icon class',
 `color` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Hex color value e.g. #ffffff',
 `label_en_CA` varchar(512) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.',
 `description_en_CA` text COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Optional description.',
 `label_fr_CA` varchar(512) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.',
 `description_fr_CA` text COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Optional description.',
 PRIMARY KEY (`id`),
 KEY `index_option_group_id_value` (`value`(128),`option_group_id`),
 KEY `index_option_group_id_name` (`name`(128),`option_group_id`),
 KEY `FK_civicrm_option_value_option_group_id` (`option_group_id`),
 KEY `FK_civicrm_option_value_component_id` (`component_id`),
 KEY `FK_civicrm_option_value_domain_id` (`domain_id`),
 CONSTRAINT `FK_civicrm_option_value_component_id` FOREIGN KEY (`component_id`) REFERENCES `civicrm_component` (`id`),
 CONSTRAINT `FK_civicrm_option_value_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`),
 CONSTRAINT `FK_civicrm_option_value_option_group_id` FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=882 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

(comment edited to add backticks/formatting --totten)

Hmm, this does seem to be wordpress specific but I don't know enough about wordpress to know why. I was able to reproduce it on wpmaster.demo.civicrm.org but not on a drupal install.

added git:civicrm-wordpress regression sig:bug triaged labels

I have given this the regression label as I assumed this worked previously in wordpress. Feel free to remove that label when you do think it is not a regression.

I tried to replicate this on a copy of a live site. I had issues because the site had 2 domains - which is resolved with this https://github.com/civicrm/civicrm-core/pull/17733 but I cannot now replicate the issue on the newly-multilingual site - although I did succeed on wpmaster.demo.civicrm.org yesterday (or the day before)

So the two domain issue is probably affected by this: https://github.com/civicrm/civicrm-core/pull/17646 As a quick fix, does reverting that PR solve the problem?

@colemanw that PR is too recent for the main issue - although it might apply to what I hit

mentioned in issue #1852 (closed)

Going back to the errors like this:

[debug_info] => INSERT INTO civicrm_option_value_en_CA (option_group_id , label , value , is_default , weight , is_active ) VALUES ( 103 , 'CAD ($)' , 'CAD' , 1 , 1 , 1 ) [nativecode=1423 ** Field of view 'example_db.civicrm_option_value_en_CA' underlying table doesn't have a default value]

I poked for a few more minutes and just want to verbalize a little of what I see when I try it.

In civicrm_currency, there's a list of all available currencies.
In civicrm_option_value, there's a list of selected currencies.
When saving the "Localization" form, it takes your chosen list of currencies, grabs the data from civicrm_currency, and copies some of it over to civicrm_option_value.
The error occurs while copying over the values - basically, it's saying: "You want me to insert data in civicrm_option_value? Ha! Not good enough! You can't insert into this VIEW unless you tell me the name of the record!"

So I tried this:


diff --git a/CRM/Admin/Form/Setting/Localization.php b/CRM/Admin/Form/Setting/Localization.php
index 55bb965cf4..fdbc2943b9 100644
--- a/CRM/Admin/Form/Setting/Localization.php
+++ b/CRM/Admin/Form/Setting/Localization.php
@@ -246,6 +246,7 @@ class CRM_Admin_Form_Setting_Localization extends CRM_Admin_Form_Setting {
     for ($i = 0; $i < count($currencies); $i++) {
       $options[] = [
         'label' => $currencySymbols[$currencies[$i]],
+        'name' => $currencies[$i],
         'value' => $currencies[$i],
         'weight' => $i + 1,
         'is_active' => 1,

And the software replied, "Thank you for giving the name -- NOT! You still need to give lots of other fields! Muahaha"

Which seems like a really not-nice thing, given that the entire multilingual layer relays on creating views and inserting data, and it's fairly common to have INSERT statements wherein some irrelevant/default fields are omitted.

--

OK, so the problem appears to depend on the MySQL version - it fails on MySQL 5.6 (bknix-min) but works on MySQL 5.7 (bknix-dfl).

Yeah, I'm pretty sure that the key variable is MySQL version. The CMS does not appear to be relevant, ie

wpmaster and dmaster - both fail in mysql56
wpmaster and dmaster - both work in mysql57

@totten I think we can remove the regression label then - is that your take?

Good question. So I just tested on Civi 5.21 with MySQL 5.6 and MySQL 5.5 - the problem does occur there, too. We'd have to test further back to see if it's an old regression... but it's not a very recent regression.

My sense is that this one is really messy to fix. If it's just the one use-case of sync'ing currencies, then OK, that could be done. But it feels like a general liability if INSERTing into a VIEW fails on account of the omission of unnecessary fields.

At a bare minimum, we should maybe flag MySQL 5.7 as a requirement for multilingual...

removed regression label

OK - I guess this is old but edge - @freeform.steph what is your mysql version?

We're running 10.3.23-MariaDB

@freeform.steph hmm - that should be the 5.7 equivalent but is obviously adding something new into the mix - do you have any ability to test on more recent Maria DB versions to see?

@bgm I feel like you use multilingual on Maria DB?

@eileen sorry, we don't have a way to switch a single client to a different version, so we would need to create a virtual environment but the client doesn't want to invest further in this investigation so I won't have the resources to test further.

It should be noted that all of our clients are running the same version of Maria DB, a few have Drupal & CiviCRM with multilingual set-ups that are not experiencing any issues - we've only been able to replicate on the CiviCRM + Wordpress install.

Thanks for all of your work on this!

@freeform.steph I ran into this problem today - see if https://github.com/civicrm/civicrm-core/pull/20753 makes sense as a fix if you're still seeing this.

If you're only seeing this on limited sites, I suspect it has to do with when multilingual was enabled rather than CMS version.

Hi,

Thank you for raising an issue to help improve CiviCRM. As you may know, this issue has not had any activity for quite some time, so we have closed it.

We would like you to help us to determine if this issue should be re-opened:

If this issue was reporting a bug, can you attempt to reproduce it on a latest version of CiviCRM?
If this issue was proposing a new feature, can you verify if the feature proposal is still relevant? Did it get the concept-approved label? Have other people also shown interest? Could it be implemented as an extension?

If the answer to either question is yes, please feel free to comment or re-open the issue. Please also consider:

Is it something that you could help implement, either by sending a patch or hiring someone who can?
Would you be willing to fund the work, either through a CiviCRM partner, or the Core Team?

Thank you for your help and contributions to CiviCRM.

P.S. This is an automated message, see infra/gitlab issue 20. We understand that automatic responses are annoying, but given the number of open issues as the project evolves, we need a bit of help to triage and prioritise the most relevant issues.

closed

After enabling multilingual site, DB error 'underlying table doesn't have a default value' prevents saving any future language settings or enable extensions

Child items 0

Activity