Cannot create proper `Group`s on multilingual with MySQL 5.6 and Civi 5.31-rc
The unit-test CRM_Mailing_MailingSystemTest::testGitLabIssue1108()
sets up a multilingual configuration and creates two groups. The test is failing, which reveals a problem with Group
s on multilingual.
Steps to reproduce
Use an environment like bknix-min
(e.g. MySQL 5.6 + PHP 7.1). Create a site on 5.31. Then either:
- Run the unit-test
- Use the live site -- Enable multi-lingual and create two groups. (Note: You get the same problem if you create the groups in the web UI.)
Discussion
I bisected to find that the failure originates with a schema update for civicrm_group. Specifically, the title
column now has <required>true</required>
.
The problem shows some environmental variation
Runtime | Base-branch | Group schema | Outcome |
---|---|---|---|
bknix-min (mysql 5.6.47, php 7.1.33) | 5.31 (rc) |
civicrm_group.title required |
Failing |
bknix-min (mysql 5.6.47, php 7.1.33) | 5.31 (rc) |
civicrm_group.title not required |
Passing |
bknix-max (mysql 5.7.27, php 7.3.10) | 5.31 (rc) |
civicrm_group.title required |
Passing |
To see the problem, it helps to inspect what happens after creating the first group. Compare the outcome in different environments:
-
Failing case
By default, when you create the first
Group
, it throws an exception:INSERT INTO `civicrm_group_en_US` (`name` , `title` , `description` , `is_active` , `visibility` , `group_type` , `parents` , `is_reserved` , `created_id` ) VALUES ('First_tmp' , 'First' , NULL , 1 , 'User and User Admin Only' , '' , '' , 0 , 202 ) -- [nativecode=1423 ** Field of view 'dmastercivi_ssc8b.civicrm_group_en_US' underlying table doesn't have a default value]
This is partially because I'm using a dev-build which has
STRICT_TRANS_TABLES
. If I hackCRM_Core_DAO::init()
to omitSTRICT_TRANS_TABLES
, then appears to be more forgiving. It creates theGroup
:mysql> select id, title_en_US, title_fr_FR from civicrm_group; +----+---------------------------+---------------------------+ | id | title_en_US | title_fr_FR | +----+---------------------------+---------------------------+ | 1 | Administrators | Administrators | | 2 | Newsletter Subscribers | Newsletter Subscribers | | 3 | Summer Program Volunteers | Summer Program Volunteers | | 4 | Advisory Board | Advisory Board | | 5 | Case Resources | Case Resources | | 6 | First | | +----+---------------------------+---------------------------+
However, the empty value of
title_fr_FR
is a problem. As soon as you create a secondGroup
, it will violate the uniqueness constraint (UI_title_fr_FR
). -
Passing
In the two passing cases, the first
Group
is created without a problem:mysql> select id, title_en_US, title_fr_FR from civicrm_group; +----+---------------------------+---------------------------+ | id | title_en_US | title_fr_FR | +----+---------------------------+---------------------------+ | 1 | Administrators | Administrators | | 2 | Newsletter Subscribers | Newsletter Subscribers | | 3 | Summer Program Volunteers | Summer Program Volunteers | | 4 | Advisory Board | Advisory Board | | 5 | Case Resources | Case Resources | | 6 | First | First | +----+---------------------------+---------------------------+
Note the synchronized title.