2

Running CiviCRM 5.32.2 on Drupal 7, PHP 7.3, MySQL 8, Apache2, Ubuntu Server 20.04.

This instance of CiviCRM has just been migrated from another server and the migration has largely gone well.

However, there is one issue that has come up to do with Smart Groups that include other Smart Groups in their criteria.

Rebuilding the Smart Group Cache works fine for 'normal' Smart Groups, but Smart Groups relying on data from other Smart Groups fail with the following sort of error, both when building the cache and trying to view the list of contacts in that Smart Group:

backTrace

#0 /var/www/html/dc/sites/all/modules/civicrm/CRM/Core/Error.php(148): CRM_Core_Error::backtrace()
#1 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(944): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/db/DB.php(997): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...")
#3 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...")
#4 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...", "DB_Error", TRUE)
#5 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1928): PEAR->__call("raiseError", (Array:7))
#6 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(936): DB_common->raiseError(-1, NULL, NULL, "INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...", "3995 ** Character set 'utf8mb4_bin' cannot be used in conjunction with 'binar...")
#7 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(406): DB_mysqli->mysqliRaiseError()
#8 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1234): DB_mysqli->simpleQuery("INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...")
#9 /var/www/html/dc/sites/all/modules/civicrm/packages/DB/DataObject.php(2696): DB_common->query("INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...")
#10 /var/www/html/dc/sites/all/modules/civicrm/packages/DB/DataObject.php(1829): DB_DataObject->_query("INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...")
#11 /var/www/html/dc/sites/all/modules/civicrm/CRM/Core/DAO.php(457): DB_DataObject->query("INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...")
#12 /var/www/html/dc/sites/all/modules/civicrm/CRM/Core/DAO.php(1564): CRM_Core_DAO->query("INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...", TRUE)
#13 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(488): CRM_Core_DAO::executeQuery("INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...")
#14 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(3156): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO))
#15 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(3057): CRM_Contact_BAO_Query->addGroupContactCache((Array:1), "civicrm_group_contact_cache_5fe30e787286d", "contact_a", "NOT IN")
#16 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(1819): CRM_Contact_BAO_Query->group((Array:5))
#17 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(2057): CRM_Contact_BAO_Query->whereClauseSingle((Array:5), FALSE)
#18 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(574): CRM_Contact_BAO_Query->whereClause(FALSE)
#19 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(523): CRM_Contact_BAO_Query->initialize(NULL)
#20 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(780): CRM_Contact_BAO_Query->__construct((Array:4), (Array:7), (Array:115), FALSE, FALSE, 1, TRUE, TRUE, FALSE, NULL, (Array:3))
#21 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(466): CRM_Contact_BAO_GroupContactCache::getQueryObjectSQL("23", (Array:4), "61 AS group_id", "NOT IN (\n                        SELECT contact_id FROM civicrm_group_contac...")
#22 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(3156): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO))
#23 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(3057): CRM_Contact_BAO_Query->addGroupContactCache((Array:1), "civicrm_group_contact_cache_5fe30e78711f0", "contact_a", "NOT IN")
#24 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(1819): CRM_Contact_BAO_Query->group((Array:5))
#25 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(2057): CRM_Contact_BAO_Query->whereClauseSingle((Array:5), FALSE)
#26 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(574): CRM_Contact_BAO_Query->whereClause(FALSE)
#27 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(523): CRM_Contact_BAO_Query->initialize(NULL)

The issue seems to be some DB collation mismatch. I've tried changing collations and character sets but to no avail; just gives the same error.

The Smart Group Criteria worked fine on the previous server (CentOS 7 --- yeah, we're bailing early).

Any wisdom on where to look to fix this?

Thanks

2
  • This might be something that hasn't been tested well on mysql 8. Were you on mysql8 before? – Demerit Dec 23 '20 at 13:57
  • Thanks, @Demerit. No, we were using MariaDB. Incidentally, I've found another issue that I think is related to using MySQL8 to do with logging - currently, creating a contact throws an error. Wondering about downgrading to MySQL 5.7 before going too much further. – Rich Smith Dec 23 '20 at 14:12
2

Okay, this appears to be an issue with running CiviCRM on MySQL 8. I downgraded to MySQL 5.7 and it's now working. Maybe worth investigating further.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.