Unresolved MariaDB bug breaks multi-language mode (Was: Can't create custom fields in multi-language mode)
An unresolved bug in MariaDB leads to fatal database errors when multiple languages are enabled in CiviCRM.
In CiviCRM's multi-language mode, text fields that are exposed to the UI are internationalised: The database tables containing these fields are restructured, replacing each internationalised field with multiple fields - one for each locale. However, throughout CiviCRM core and extensions there are many places where PHP code creates a record in one of these tables, but only supplies values for a single locale.
CiviCRM uses database triggers to deal with this issue: When a new record is about to be inserted into an internationalised table, a trigger is supposed to copy the values supplied for one locale to all other locales for which values haven't been supplied.
However, a problem arises when the internationalised text fields are NOT NULL and have no DEFAULT. What the database should do is run the trigger before checking the NOT NULL constraints. However, due to an unresolved bug in MariaDB, it checks the NOT NULL constraints before it runs the trigger. This means that the NOT NULL constraints fail, and when the database connection's SQL mode is strict, this causes a fatal database error.
If you remove strict options (
STRICT_TRANS_TABLES) from the database connection's SQL mode, then
INSERT generates a warning rather than an error, so CiviCRM does not crash. But any NOT NULL string fields get set to empty strings, which means that the trigger doesn't do its job, and may instead overwrite correct values with empty strings.
This can be seen when you try to create a custom field set in multi-language mode. Custom field sets are stored in the table
title field is required and is part of a UNIQUE KEY, so it is rightly constrained to be NOT NULL. But the PHP that creates a custom field set only supplies one value for title - the one for the default/current locale. The
civicrm_custom_group_before_insert trigger is supposed to copy that value to the other locales, but MariaDB doesn't run that trigger before checking the NOT NULL constraints. The results are described below.
This MariaDB bug has resurfaced several times over the last few years. It was reported as MDEV-10002 (supposedly fixed in 10.1.10) and MDEV-11698 (supposedly fixed in 10.1.21 and 10.2.4), and now it's back as MDEV-19761, and remains unresolved.
- In the admin menu, go to Administer -> Localization -> Languages, Currency, Locations.
- Under Multiple Languages Support, tick Enable Multiple Languages.
- Click Save.
- From the Add Languages dropdown, select a second language.
- Click Save.
- In the admin menu, go to Administer -> Customize Data and Screens -> Custom Fields.
- Click Add Set of Custom Fields.
- Fill in the required fields.
- Click Save.
The result depends on whether the SQL mode of CiviCRM's database connection is strict or not.
With a strict SQL mode (the default since MariaDB 10.2.4) you see a fatal error message in the UI - "DB Error: unknown error" - and a fatal error message in the log file:
icon) VALUES ('Event_Feedback' , 'Event Feedback' , 'Activity' , NULL , '28' , 'Inline' , 0 , '' , '' , 6 , 1 , 0 , NULL , 0 , 202 , 20201116132750 , 1 , '' ) [nativecode=1423 ** Field of view 'civicrm.civicrm_custom_group_en_GB' underlying table doesn't have a default value]
With a non-strict SQL mode, CiviCRM doesn't crash, and it does create a new custom field set, but its title is an empty string.
A new custom field set should be created with the specified title, and you should be taken to the form for creating the first custom field in that set.
- CiviCRM: 5.28.4, 5.33.alpha1
- CMS: Backdrop, Drupal 7, Drupal 8, Drupal 9, WordPress 5.5
- Database: MariaDB 10.3.25
CiviCRM's implementation of internationalisation is dependent on database triggers at a deep level, and as such it is dependent on the MariaDB bug being fixed. It's difficult to see how this dependence could be removed without a significant rewrite of core code.
There are many places in CiviCRM core code where new DAO objects are created without regard to internationalisation, not to mention in the API and in third-party extensions. It is practically infeasible to modify all of that code, and therefore any solution needs to work for code that is I18n-agnostic.
I can think of three possible approaches:
Temporarily disable strict SQL mode: Strict options could be removed from the SQL mode immediately before
DB_DataObject, and then reinstated immediately afterwards. This is a nasty hack; not recommended, as it could suppress genuine database errors. Also, the triggers defined in
CRM_Core_I18n_Schema::triggerInfowould have to be rewritten so that they didn't copy empty strings to other locales.
Rewrite SQL before executing: The
CRM_Core_I18n_Schema::rewriteQueryfunction localises SQL statements by replacing the names of internationalised tables (e.g.
civicrm_custom_group) with the names of locale-specific views onto those tables (e.g.
civicrm_custom_group_en_GB). This function could instead be used to perform a much more extensive rewrite of the SQL statements so that INSERTs and UPDATEs would operate directly on the internationalised tables, and supply default values for all locales. This would make the database triggers redundant, removing CiviCRM's dependence on them. However, it would require some heavy lifting with an SQL parser (recommended) or a nightmare of regular expressions (not recommended).
- Fix the underlying MariaDB bug: The current incarnation of this bug was reported over a year ago, and it still hasn't been fixed. A fix needs to be written and submitted as a PR, which needs to be accepted and merged by the MariaDB maintainers. This would give the best outcome long-term, but could take a long time.