From 25374ca7425a5086437a6468ec6cc07c722ba7f6 Mon Sep 17 00:00:00 2001
From: eileen <emcnaughton@wikimedia.org>
Date: Sat, 30 Nov 2019 10:26:37 +1300
Subject: [PATCH] Update temp table handler to support utf8mb4 if that is the
 db collation

---
 CRM/Core/BAO/SchemaHandler.php | 18 ++++++++++++++++++
 CRM/Utils/SQL/TempTable.php    | 25 ++++++++++++++++++++++++-
 2 files changed, 42 insertions(+), 1 deletion(-)

diff --git a/CRM/Core/BAO/SchemaHandler.php b/CRM/Core/BAO/SchemaHandler.php
index 591421c6f2..2f3f70241e 100644
--- a/CRM/Core/BAO/SchemaHandler.php
+++ b/CRM/Core/BAO/SchemaHandler.php
@@ -875,4 +875,22 @@ MODIFY      {$columnName} varchar( $length )
     return TRUE;
   }
 
+  /**
+   * Get the database collation.
+   *
+   * @return string
+   */
+  public static function getDBCollation() {
+    return CRM_Core_DAO::singleValueQuery('SELECT @@collation_database');
+  }
+
+  /**
+   * Get the database collation.
+   *
+   * @return string
+   */
+  public static function getDBCharset() {
+    return CRM_Core_DAO::singleValueQuery('SELECT @@character_set_database');
+  }
+
 }
diff --git a/CRM/Utils/SQL/TempTable.php b/CRM/Utils/SQL/TempTable.php
index 6ca62379c6..da507cbcc2 100644
--- a/CRM/Utils/SQL/TempTable.php
+++ b/CRM/Utils/SQL/TempTable.php
@@ -125,7 +125,7 @@ class CRM_Utils_SQL_TempTable {
     $sql = sprintf('%s %s %s AS %s',
       $this->toSQL('CREATE'),
       $this->memory ? self::MEMORY : self::INNODB,
-      $this->utf8 ? self::UTF8 : '',
+      $this->getUtf8String(),
       ($selectQuery instanceof CRM_Utils_SQL_Select ? $selectQuery->toSQL() : $selectQuery)
     );
     CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE);
@@ -133,6 +133,29 @@ class CRM_Utils_SQL_TempTable {
     return $this;
   }
 
+  /**
+   * 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.
+   *
+   * @return string
+   */
+  public function getUtf8String() {
+    if (!$this->utf8) {
+      return '';
+    }
+    $dbUTF = CRM_Core_BAO_SchemaHandler::getDBCollation();
+    if (in_array($dbUTF, ['utf8_unicode_ci', 'utf8mb4_unicode_ci'])
+      && in_array($dbUTF, ['utf8', 'utf8mb4'])) {
+      return '';
+    }
+    return self::UTF8;
+  }
+
   /**
    * Create the empty table.
    *
-- 
2.25.1