add mysql row format dynamic option (#18002)

10 files changed, 31 insertions, 36 deletions

diff --git a/core/Db/Adapter/Mysqli.php b/core/Db/Adapter/Mysqli.php
index a47f5bb4eb..87f91ac32f 100644
--- a/core/Db/Adapter/Mysqli.php
+++ b/core/Db/Adapter/Mysqli.php
@@ -226,16 +226,6 @@ class Mysqli extends Zend_Db_Adapter_Mysqli implements AdapterInterface
         return $rowsAffected;
     }
 
-    /**
-     * Is the connection character set equal to utf8?
-     *
-     * @return bool
-     */
-    public function isConnectionUTF8()
-    {
-        $charset = mysqli_character_set_name($this->_connection);
-        return strpos($charset, 'utf8') === 0;
-    }
 
     /**
      * Get client version
diff --git a/core/Db/Adapter/Pdo/Mysql.php b/core/Db/Adapter/Pdo/Mysql.php
index 6127276419..1170afe3f5 100644
--- a/core/Db/Adapter/Pdo/Mysql.php
+++ b/core/Db/Adapter/Pdo/Mysql.php
@@ -246,6 +246,7 @@ class Mysql extends Zend_Db_Adapter_Pdo_Mysql implements AdapterInterface
         return strpos($charset, 'utf8') === 0;
     }
 
+
     /**
      * Return number of affected rows in last query
      *
diff --git a/core/Db/AdapterInterface.php b/core/Db/AdapterInterface.php
index 8a0ee15f2c..dea97cfc9c 100644
--- a/core/Db/AdapterInterface.php
+++ b/core/Db/AdapterInterface.php
@@ -69,5 +69,4 @@ interface AdapterInterface
      *
      * @return bool
      */
-    public function isConnectionUTF8();
 }
diff --git a/core/Db/Schema/Mysql.php b/core/Db/Schema/Mysql.php
index 940f694e70..92d2b882fb 100644
--- a/core/Db/Schema/Mysql.php
+++ b/core/Db/Schema/Mysql.php
@@ -515,11 +515,12 @@ class Mysql implements SchemaInterface
         $dbSettings   = new Db\Settings();
         $charset      = $dbSettings->getUsedCharset();
 
-        $statement = sprintf("CREATE TABLE IF NOT EXISTS `%s` ( %s ) ENGINE=%s DEFAULT CHARSET=%s ;",
+        $statement = sprintf("CREATE TABLE IF NOT EXISTS `%s` ( %s ) ENGINE=%s DEFAULT CHARSET=%s %s;",
                              Common::prefixTable($nameWithoutPrefix),
                              $createDefinition,
                              $this->getTableEngine(),
-                             $charset);
+                             $charset,
+          $dbSettings->getRowFormat());
 
         try {
             Db::exec($statement);
diff --git a/core/Db/Settings.php b/core/Db/Settings.php
index f931249bb1..9eeaeae786 100644
--- a/core/Db/Settings.php
+++ b/core/Db/Settings.php
@@ -6,6 +6,7 @@
  * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
  *
  */
+
 namespace Piwik\Db;
 
 use Piwik\Db;
@@ -32,9 +33,14 @@ class Settings
         return strtolower($this->getDbSetting('charset'));
     }
 
+    public function getRowFormat()
+    {
+        return $this->getUsedCharset() === 'utf8mb4' ? 'ROW_FORMAT=DYNAMIC' : '';
+    }
+
     private function getDbSetting($key)
     {
         $dbInfos = Db::getDatabaseConfig();
-        return $dbInfos[$key];
+        return $dbInfos[$key] ?? null;
     }
 }
diff --git a/core/DbHelper.php b/core/DbHelper.php
index c153fffbb2..ea0b4e5e4e 100644
--- a/core/DbHelper.php
+++ b/core/DbHelper.php
@@ -150,15 +150,6 @@ class DbHelper
         }
     }
 
-    /**
-     * Check database connection character set is utf8.
-     *
-     * @return bool  True if it is (or doesn't matter); false otherwise
-     */
-    public static function isDatabaseConnectionUTF8()
-    {
-        return Db::get()->isConnectionUTF8();
-    }
 
     /**
      * Checks the database server version against the required minimum
diff --git a/core/Updater/Migration/Db/CreateTable.php b/core/Updater/Migration/Db/CreateTable.php
index 77a3973662..5a47704012 100644
--- a/core/Updater/Migration/Db/CreateTable.php
+++ b/core/Updater/Migration/Db/CreateTable.php
@@ -5,6 +5,7 @@
  * @link https://matomo.org
  * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
  */
+
 namespace Piwik\Updater\Migration\Db;
 
 use Piwik\Db;
@@ -33,8 +34,9 @@ class CreateTable extends Sql
             $columns[] = sprintf('PRIMARY KEY ( `%s` )', implode('`, `', $primaryKey));
         }
 
-        $sql = sprintf('CREATE TABLE `%s` (%s) ENGINE=%s DEFAULT CHARSET=utf8',
-                       $table, implode(', ' , $columns), $dbSettings->getEngine());
+
+        $sql = rtrim(sprintf('CREATE TABLE `%s` (%s) ENGINE=%s DEFAULT CHARSET=%s %s',
+          $table, implode(', ', $columns), $dbSettings->getEngine(), $dbSettings->getUsedCharset(), $dbSettings->getRowFormat()));
 
         parent::__construct($sql, static::ERROR_CODE_TABLE_EXISTS);
     }
diff --git a/core/Updates/4.5.0-b1.php b/core/Updates/4.5.0-b1.php
index e79ebe7107..1ac0cae445 100644
--- a/core/Updates/4.5.0-b1.php
+++ b/core/Updates/4.5.0-b1.php
@@ -9,9 +9,9 @@
 
 namespace Piwik\Updates;
 
+use Piwik\DbHelper;
 use Piwik\Updater;
 use Piwik\Updates as PiwikUpdates;
-use Piwik\Updater\Migration;
 use Piwik\Updater\Migration\Factory as MigrationFactory;
 
 class Updates_4_5_0_b1 extends PiwikUpdates
@@ -29,11 +29,11 @@ class Updates_4_5_0_b1 extends PiwikUpdates
 
     public function getMigrations(Updater $updater)
     {
-        $migration1 = $this->migration->db->changeColumnType('session', 'data', 'MEDIUMTEXT');
+        $migrations = [];
+        $migrations[] = $this->migration->db->changeColumnType('session', 'data', 'MEDIUMTEXT');
+
+        return $migrations;
 
-        return [
-            $migration1,
-        ];
     }
 
     public function doUpdate(Updater $updater)
diff --git a/plugins/Installation/Controller.php b/plugins/Installation/Controller.php
index 4a64b73d05..099496728c 100644
--- a/plugins/Installation/Controller.php
+++ b/plugins/Installation/Controller.php
@@ -149,6 +149,7 @@ class Controller extends \Piwik\Plugin\ControllerAdmin
 
                 DbHelper::checkDatabaseVersion();
 
+
                 Db::get()->checkClientVersion();
 
                 $this->createConfigFile($dbInfos);
@@ -569,7 +570,7 @@ class Controller extends \Piwik\Plugin\ControllerAdmin
 
         return AssetManager::compileCustomJs($files);
     }
-    
+
     private function getParam($name)
     {
         return Common::getRequestVar($name, false, 'string');
diff --git a/tests/PHPUnit/Integration/Updater/Migration/Db/FactoryTest.php b/tests/PHPUnit/Integration/Updater/Migration/Db/FactoryTest.php
index 9ba7cbeacf..f817b1f1a0 100644
--- a/tests/PHPUnit/Integration/Updater/Migration/Db/FactoryTest.php
+++ b/tests/PHPUnit/Integration/Updater/Migration/Db/FactoryTest.php
@@ -9,7 +9,10 @@
 namespace Piwik\Tests\Integration\Updater\Migration\Db;
 
 use Piwik\Common;
+use Piwik\Config;
+use Piwik\Db;
 use Piwik\Tests\Framework\TestCase\IntegrationTestCase;
+use Piwik\Updater\Migration\Config\Set;
 use Piwik\Updater\Migration\Db\AddColumn;
 use Piwik\Updater\Migration\Db\AddColumns;
 use Piwik\Updater\Migration\Db\AddIndex;
@@ -46,7 +49,7 @@ class FactoryTest extends IntegrationTestCase
     public function setUp(): void
     {
         parent::setUp();
-        
+
         $this->testTablePrefixed = Common::prefixTable($this->testTable);
         $this->factory = new Factory();
     }
@@ -93,15 +96,16 @@ class FactoryTest extends IntegrationTestCase
         $migration = $this->createTable();
 
         $table = $this->testTablePrefixed;
-        $this->assertSame("CREATE TABLE `$table` (`column` INT(10) DEFAULT 0, `column2` VARCHAR(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8;", ''. $migration);
+        $this->assertSame("CREATE TABLE `$table` (`column` INT(10) DEFAULT 0, `column2` VARCHAR(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;", '' . $migration);
     }
 
+
     public function test_createTable_withPrimaryKey()
     {
         $migration = $this->createTable('column2');
 
         $table = $this->testTablePrefixed;
-        $this->assertSame("CREATE TABLE `$table` (`column` INT(10) DEFAULT 0, `column2` VARCHAR(255), PRIMARY KEY ( `column2` )) ENGINE=InnoDB DEFAULT CHARSET=utf8;", ''. $migration);
+        $this->assertSame("CREATE TABLE `$table` (`column` INT(10) DEFAULT 0, `column2` VARCHAR(255), PRIMARY KEY ( `column2` )) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;", '' . $migration);
     }
 
     public function test_dropTable_returnsDropTableInstance()
@@ -116,7 +120,7 @@ class FactoryTest extends IntegrationTestCase
         $migration = $this->factory->dropTable($this->testTable);
 
         $table = $this->testTablePrefixed;
-        $this->assertSame("DROP TABLE IF EXISTS `$table`;", ''. $migration);
+        $this->assertSame("DROP TABLE IF EXISTS `$table`;", '' . $migration);
     }
 
     public function test_dropColumn_returnsDropColumnInstance()
@@ -313,7 +317,7 @@ class FactoryTest extends IntegrationTestCase
         $migration = $this->insert();
 
         $table = $this->testTablePrefixed;
-        $this->assertSame("INSERT INTO `$table` (`column1`, `column3`) VALUES ('val1',5);", ''. $migration);
+        $this->assertSame("INSERT INTO `$table` (`column1`, `column3`) VALUES ('val1',5);", '' . $migration);
     }
 
     public function test_batchInsert_returnsBatchInsertInstance()