Translation issueshttps://lab.civicrm.org/dev/translation/-/issues2021-03-18T00:43:45Zhttps://lab.civicrm.org/dev/translation/-/issues/58Cannot create proper `Group`s on multilingual with MySQL 5.6 and Civi 5.31-rc2021-03-18T00:43:45ZtottenCannot create proper `Group`s on multilingual with MySQL 5.6 and Civi 5.31-rcThe 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
-----------...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](https://gist.github.com/totten/e1244d9a98c9d9fd1ebc899f5e780252) and [create two groups](https://gist.github.com/totten/b405a10cd82f35d125d64c792786d6f1). (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](https://github.com/civicrm/civicrm-core/pull/18599/files#diff-9f8f69e770bcc926a5831339a92183bbb70aad496a16dee07beaf9c6ff7b42d9L34). 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:
1. __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 hack `CRM_Core_DAO::init()` to omit `STRICT_TRANS_TABLES`, then appears to be more forgiving. It creates the `Group`:
```
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 *second* `Group`, it will violate the uniqueness constraint (`UI_title_fr_FR`).
2. __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.