Use utf8mb4 character set if possible (#15618)

1 files changed, 49 insertions, 0 deletions

diff --git a/core/DbHelper.php b/core/DbHelper.php
index 4d93ea43f3..e025db4a1a 100644
--- a/core/DbHelper.php
+++ b/core/DbHelper.php
@@ -205,6 +205,55 @@ class DbHelper
     }
 
     /**
+     * Returns the default database charset to use
+     *
+     * Returns utf8mb4 if supported, with fallback to utf8
+     *
+     * @return string
+     * @throws Tracker\Db\DbException
+     */
+    public static function getDefaultCharset()
+    {
+        $result = Db::get()->fetchRow("SHOW CHARACTER SET LIKE 'utf8mb4'");
+
+        if (empty($result)) {
+            return 'utf8'; // charset not available
+        }
+
+        $result = Db::get()->fetchRow("SHOW VARIABLES LIKE 'character_set_database'");
+
+        if (!empty($result) && $result['Value'] === 'utf8mb4') {
+            return 'utf8mb4'; // database has utf8mb4 charset, so assume it can be used
+        }
+
+        $result = Db::get()->fetchRow("SHOW VARIABLES LIKE 'innodb_file_per_table'");
+
+        if (empty($result) || $result['Value'] !== 'ON') {
+            return 'utf8'; // innodb_file_per_table is required for utf8mb4
+        }
+
+        return 'utf8mb4';
+    }
+
+    /**
+     * Returns sql queries to convert all installed tables to utf8mb4
+     *
+     * @return array
+     */
+    public static function getUtf8mb4ConversionQueries()
+    {
+        $allTables = DbHelper::getTablesInstalled();
+
+        $queries   = [];
+
+        foreach ($allTables as $table) {
+            $queries[] = "ALTER TABLE `$table` CONVERT TO CHARACTER SET utf8mb4;";
+        }
+
+        return $queries;
+    }
+
+    /**
      * Get the SQL to create Piwik tables
      *
      * @return array  array of strings containing SQL