From 8f719ee0848833b42e5f5f2eacabf8cfef2afa44 Mon Sep 17 00:00:00 2001
From: eileen <emcnaughton@wikimedia.org>
Date: Wed, 12 Aug 2020 16:20:38 +1200
Subject: [PATCH] Fix 5.29 regression using temp tables

I just pushed 5.29rc through our WMF CI & it failed on a situation where it created a temp
table and that got joined on a non-temp table with a collation mix error. The issue is that the
DB default is set to utf8_general_ci (at least on our development DBs) whereas the
civicrm tables are utf8_unicode_ci. Until https://github.com/civicrm/civicrm-core/pull/18012/files
there was a list of permitted matches for db collation but now any 'utf' string matches.

From what I can tell utf8_general_ci  is a pretty common collation on drupal DBs and from my tests
it causes problems if not excluded here
---
 CRM/Core/BAO/SchemaHandler.php              | 17 +++++++++++++++++
 CRM/Utils/SQL/TempTable.php                 | 16 +++-------------
 tests/phpunit/api/v3/ReportTemplateTest.php |  2 +-
 3 files changed, 21 insertions(+), 14 deletions(-)

diff --git a/CRM/Core/BAO/SchemaHandler.php b/CRM/Core/BAO/SchemaHandler.php
index bbe11ae3af..5523858d6a 100644
--- a/CRM/Core/BAO/SchemaHandler.php
+++ b/CRM/Core/BAO/SchemaHandler.php
@@ -879,6 +879,23 @@ MODIFY      {$columnName} varchar( $length )
     return CRM_Core_DAO::singleValueQuery('SELECT @@collation_database');
   }
 
+  /**
+   * Get the collation actually being used by the tables in the database.
+   *
+   * The db collation may not match the collation used by the tables, get what is
+   * set on the tables (represented by civicrm_contact).
+   *
+   * @return string
+   */
+  public static function getInUseCollation() {
+    if (!isset(\Civi::$statics[__CLASS__][__FUNCTION__])) {
+      $dao = CRM_Core_DAO::executeQuery('SHOW TABLE STATUS LIKE \'civicrm_contact\'');
+      $dao->fetch();
+      \Civi::$statics[__CLASS__][__FUNCTION__] = $dao->Collation;
+    }
+    return \Civi::$statics[__CLASS__][__FUNCTION__];
+  }
+
   /**
    * Get the database collation.
    *
diff --git a/CRM/Utils/SQL/TempTable.php b/CRM/Utils/SQL/TempTable.php
index f0024d7ef7..8993807f3c 100644
--- a/CRM/Utils/SQL/TempTable.php
+++ b/CRM/Utils/SQL/TempTable.php
@@ -135,23 +135,13 @@ class CRM_Utils_SQL_TempTable {
   /**
    * Get the utf8 string for the table.
    *
-   * If the db collation is already utf8 by default (either
-   * utf8 or utf84mb) then rely on that. Otherwise set to utf8.
-   *
-   * Respecting the DB collation supports utf8mb4 adopters, which is currently
-   * not the norm in civi installs.
+   * Our tables are either utf8_unicode_ci OR utf8mb8_unicode_ci - check the contact table
+   * to see which & use the matching one.
    *
    * @return string
    */
   public function getUtf8String() {
-    if (!$this->utf8) {
-      return '';
-    }
-    $dbUTF = CRM_Core_BAO_SchemaHandler::getDBCollation();
-    if (strpos($dbUTF, 'utf8') !== FALSE) {
-      return '';
-    }
-    return self::UTF8;
+    return $this->utf8 ? ('COLLATE ' . CRM_Core_BAO_SchemaHandler::getInUseCollation()) : '';
   }
 
   /**
diff --git a/tests/phpunit/api/v3/ReportTemplateTest.php b/tests/phpunit/api/v3/ReportTemplateTest.php
index d2fc418cd4..340d7040d5 100644
--- a/tests/phpunit/api/v3/ReportTemplateTest.php
+++ b/tests/phpunit/api/v3/ReportTemplateTest.php
@@ -493,7 +493,7 @@ class api_v3_ReportTemplateTest extends CiviUnitTestCase {
 
     $this->assertEquals(2, $rows['count'], 'Report failed - the sql used to generate the results was ' . print_r($rows['metadata']['sql'], TRUE));
 
-    $expected = preg_replace('/\s+/', ' ', 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
+    $expected = preg_replace('/\s+/', ' ', 'COLLATE utf8_unicode_ci AS
       SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as cid  FROM civicrm_contact contact_civireport    INNER JOIN civicrm_contribution contribution_civireport USE index (received_date) ON contribution_civireport.contact_id = contact_civireport.id
          AND contribution_civireport.is_test = 0
          AND contribution_civireport.receive_date BETWEEN \'20140701000000\' AND \'20150630235959\'
-- 
2.25.1