Answers.Body: <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-12-29T17:26:41.027Z
Answers.Text: Could you create an issue in https://lab.civicrm.org for core development and assign it to me? Thanks.
Answers.UserDisplayName: Joe Murray
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-01-17T20:54:52.940Z
Answers.Text: Not sure if it's exactly the same since the full query isn't shown above but this patch might also fix it https://github.com/civicrm/civicrm-core/pull/19399
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-12-23T17:28:38.053Z
Answers.LastActivityDate: 2020-12-23T17:28:38.053Z
Answers.UserDisplayName: Rich Smith
Body: <p>Running CiviCRM 5.32.2 on Drupal 7, PHP 7.3, MySQL 8, Apache2, Ubuntu Server 20.04.</p>
  <p>This instance of CiviCRM has just been migrated from another server and the migration has largely gone well.</p>
  <p>However, there is one issue that has come up to do with Smart Groups that include other Smart Groups in their criteria.</p>
  <p>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:</p>
  <pre><code>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-&gt;__construct(&quot;DB Error: unknown error&quot;, -1, 16, (Array:2), &quot;INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...&quot;)
  #3 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error-&gt;__construct(-1, 16, (Array:2), &quot;INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...&quot;)
  #4 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR-&gt;_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), &quot;INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...&quot;, &quot;DB_Error&quot;, TRUE)
  #5 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1928): PEAR-&gt;__call(&quot;raiseError&quot;, (Array:7))
  #6 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(936): DB_common-&gt;raiseError(-1, NULL, NULL, &quot;INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...&quot;, &quot;3995 ** Character set 'utf8mb4_bin' cannot be used in conjunction with 'binar...&quot;)
  #7 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(406): DB_mysqli-&gt;mysqliRaiseError()
  #8 /var/www/html/dc/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1234): DB_mysqli-&gt;simpleQuery(&quot;INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...&quot;)
  #9 /var/www/html/dc/sites/all/modules/civicrm/packages/DB/DataObject.php(2696): DB_common-&gt;query(&quot;INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...&quot;)
  #10 /var/www/html/dc/sites/all/modules/civicrm/packages/DB/DataObject.php(1829): DB_DataObject-&gt;_query(&quot;INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...&quot;)
  #11 /var/www/html/dc/sites/all/modules/civicrm/CRM/Core/DAO.php(457): DB_DataObject-&gt;query(&quot;INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...&quot;)
  #12 /var/www/html/dc/sites/all/modules/civicrm/CRM/Core/DAO.php(1564): CRM_Core_DAO-&gt;query(&quot;INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...&quot;, TRUE)
  #13 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(488): CRM_Core_DAO::executeQuery(&quot;INSERT IGNORE INTO civicrm_tmp_e_gccache_3aec9ce4a5fb0a567be02bc71a5f7774 (gr...&quot;)
  #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-&gt;addGroupContactCache((Array:1), &quot;civicrm_group_contact_cache_5fe30e787286d&quot;, &quot;contact_a&quot;, &quot;NOT IN&quot;)
  #16 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(1819): CRM_Contact_BAO_Query-&gt;group((Array:5))
  #17 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(2057): CRM_Contact_BAO_Query-&gt;whereClauseSingle((Array:5), FALSE)
  #18 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(574): CRM_Contact_BAO_Query-&gt;whereClause(FALSE)
  #19 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(523): CRM_Contact_BAO_Query-&gt;initialize(NULL)
  #20 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(780): CRM_Contact_BAO_Query-&gt;__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(&quot;23&quot;, (Array:4), &quot;61 AS group_id&quot;, &quot;NOT IN (\n                        SELECT contact_id FROM civicrm_group_contac...&quot;)
  #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-&gt;addGroupContactCache((Array:1), &quot;civicrm_group_contact_cache_5fe30e78711f0&quot;, &quot;contact_a&quot;, &quot;NOT IN&quot;)
  #24 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(1819): CRM_Contact_BAO_Query-&gt;group((Array:5))
  #25 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(2057): CRM_Contact_BAO_Query-&gt;whereClauseSingle((Array:5), FALSE)
  #26 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(574): CRM_Contact_BAO_Query-&gt;whereClause(FALSE)
  #27 /var/www/html/dc/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(523): CRM_Contact_BAO_Query-&gt;initialize(NULL)
  </code></pre>
  <p>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.</p>
  <p>The Smart Group Criteria worked fine on the previous server (CentOS 7 --- yeah, we're bailing early).</p>
  <p>Any wisdom on where to look to fix this?</p>
  <p>Thanks</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-23T13:57:33.557Z
Comments.Text: This might be something that hasn't been tested well on mysql 8. Were you on mysql8 before?
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-23T14:12:02.047Z
Comments.Text: 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.
Comments.UserDisplayName: Rich Smith
Tags: <smartgroups><mysql><cache><mysql-8.0>
Title: CiviCRM Smart Group Cache SQL Collation Error
ThreadText: # CiviCRM Smart Group Cache SQL Collation Error
  
  (Asked by: username_0 on 2020-12-23)
  
  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
  
  **Tags:** smartgroups, mysql, cache, mysql-8.0
  
  > **Comments:**
  > **username_1**: This might be something that hasn't been tested well on mysql 8. Were you on mysql8 before?  
  > **username_0**: Thanks, @username_1. 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.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2020-12-23. Score: 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.
  
  > **Comments:**
  > **username_2**: Could you create an issue in https://lab.civicrm.org for core development and assign it to me? Thanks.  
  > **username_1**: Not sure if it's exactly the same since the full query isn't shown above but this patch might also fix it https://github.com/civicrm/civicrm-core/pull/19399  
  
  ---
  
  