Drupal/Lagoon - Fix collation mismatch between MariaDB and MySQL8

drupal-lagoon-fix-collation-mismatch-mariadb-mysql8.md

Prerequisites

Database settings in settings.php

// Default database configuration.
$databases = [
  'default' =>
    [
      'default' =>
        [
          // ...
          'charset' => 'utf8mb4',
          'collation' => 'utf8mb4_general_ci',
        ],
    ],
];        

Script

fix-collation.php

<?php

$database = \Drupal::database();
$connection_options = $database->getConnectionOptions();
$database_name = $connection_options['database'] ?? 'drupal';
$charset = $connection_options['charset'] ?? 'utf8mb4';
$collation = $connection_options['collation'] ?? 'utf8mb4_general_ci';
echo PHP_EOL;
$tables = $database->query('SHOW TABLE STATUS where Collation <> :collation', [':collation' => $collation ])->fetchAllAssoc('Name');
if (count($tables)) {
  echo 'Converting collation of ', count($tables), ' tables to ', $collation, PHP_EOL;

  foreach ($tables as $table_name => $table) {
    // Check if this table uses any different charset for its columns.
    echo '- checking column collation of table ', $table_name, PHP_EOL;
    $columns = $database->query('SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.columns WHERE TABLE_SCHEMA = :table_schema AND TABLE_NAME = :table_name AND CHARACTER_SET_NAME <> :charset;', [
      ':table_schema' => $database_name,
      ':table_name' => $table_name,
      ':charset' => $charset,
    ])->fetchAll();

    // It's safe to convert the collation for the whole table.
    if (!count($columns)) {
      echo '  + altering table ', $table_name, PHP_EOL;
      $database->query('ALTER TABLE ' . $table_name . ' CONVERT TO CHARACTER SET ' . $charset . ' COLLATE ' . $collation . ';');
    }
    // We have to recreate the table and copy its data over.
    else {
      // Use PDO to retrieve the full CREATE TABLE syntax.
      if (!isset($pdo)) {
        if (isset($connection_options['unix_socket'])) {
          $dsn = 'mysql:unix_socket=' . $connection_options['unix_socket'];
        }
        else {
          // Default to TCP connection on port 3306.
          $dsn = 'mysql:host=' . $connection_options['host'] . ';port=' . (empty($connection_options['port']) ? 3306 : $connection_options['port']);
        }
        $dsn .= ';charset=utf8mb4';
        if (!empty($connection_options['database'])) {
          $dsn .= ';dbname=' . $connection_options['database'];
        }
        $pdo = new \PDO($dsn, $connection_options['username'], $connection_options['password'], $connection_options['pdo']);
      }

      $sql = $pdo->query('SHOW CREATE TABLE ' . $table_name . ';', \PDO::FETCH_ASSOC);
      $result = $sql->fetch();
      if (empty($result['Create Table'])) {
        echo '  + [ERROR] unable to retrieve CREATE TABLE syntax for table ', $table_name, PHP_EOL;
      }
      else {
        $table_name_copy = $table_name . '_copy';
        $table_name_old = $table_name . '_old';
        $create_table = str_replace(
          [
            'CREATE TABLE `' . $table_name . '` (',
            'COLLATE=' . $table->Collation,
          ],
          [
            'CREATE TABLE `' . $table_name_copy . '` (',
            'COLLATE=' . $collation,
          ],
          $result['Create Table']);
        echo '  + creating temporary table ', $table_name_copy , PHP_EOL;
        $pdo->exec($create_table);

        echo '  + copying data to temporary table ', $table_name_copy, PHP_EOL;
        $pdo->exec('INSERT INTO ' . $table_name_copy . ' SELECT * FROM ' . $table_name);

        echo '  + promoting temporary table ', $table_name_copy, ' to ', $table_name, PHP_EOL;
        $pdo->exec('RENAME TABLE ' . $table_name . ' TO ' . $table_name_old);
        $pdo->exec('RENAME TABLE ' . $table_name_copy . ' TO ' . $table_name);
        $pdo->exec('DROP TABLE ' . $table_name_old);
      }
    }
  }

  $leftover_tables = $database->query('SHOW TABLE STATUS where Collation <> :collation', [':collation' => $collation ])->fetchAllAssoc('Name');
  if (!count($leftover_tables)) {
    echo 'DONE', PHP_EOL;
  }
  else {
    echo count($leftover_tables), ' tables not using the collation ', $collation, PHP_EOL;
    foreach (array_keys($leftover_tables) as $table_name) {
      echo ' - ', $table_name, PHP_EOL;
    }
  }
}
else {
  echo 'All tables are already using the collation ', $collation, PHP_EOL;
}

Run script via Drush:

drush php:script fix-collation.php

Sample output:

Converting collation of 3 tables to utf8mb4_general_ci
- checking column collation of table entity_usage
  + creating temporary table entity_usage_copy
  + copying data to temporary table entity_usage_copy
  + promoting temporary table entity_usage_copy to entity_usage
- checking column collation of table migrate_tools_sync_source_ids
  + altering table migrate_tools_sync_source_ids
- checking column collation of table xntt_rest_queries
  + altering table xntt_rest_queries
DONE