Ever needed to change collation of table?

When you are upgrading Contao 3.5 installation to Contao 4.4, you may be left with old table collation "utf8_general_ci" instead of new collation "utf8_unicode_ci" used by Contao 4.4. With the following script you can change it in one go.

Code:

---

<?php
// Database credentials
$strHost = 'host';
$strUser = 'user';
$strPassword = 'password';
$strDatabase = 'database';

// Main Script
$con = mysqli_connect( $strHost, $strUser, $strPassword, $strDatabase);

if (mysqli_connect_errno()) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging error : " . mysqli_connect_errno() . PHP_EOL;
    exit;
}

$result = $con->query('show tables');

while($tables = mysqli_fetch_array($result)) {
    foreach ($tables as $key => $value) {
        $con->query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
    }
}
echo "The collation of your database has been successfully changed!";

---

With your script you do it twice on every table. To do it only once use this:

PHP Code:

---

$result = $con->query('show tables');

while($tables = mysqli_fetch_array($result)) {
  $con->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
} 


---

For some inexplicable reasons, the tables tl_search and tl_newsletter_recipients were unfortunately not converted. I then manually converted these two with phpMyAdmin. So you should check if all tables have been converted.

Good catch, thanks for your input.

Contao 4 with auto parameters:

PHP Code:

---

<?php

$parameters = file('../app/config/parameters.yml');
$params = array();
foreach($parameters as $value)
{
  if(strpos($value, ': ') === false)
  {
    continue;
  }
  $param = explode(': ', trim($value));
  $params[$param[0]] = $param[1];
}

// Database credentials
$strHost     = $params['database_host'].':'.$params['database_port'];
$strUser     = $params['database_user'];
$strPassword = $params['database_password'];
$strDatabase = $params['database_name'];

// Main Script
$con = mysqli_connect( $strHost, $strUser, $strPassword, $strDatabase);

if (mysqli_connect_errno()) {
  echo "Error: Unable to connect to MySQL." . PHP_EOL;
  echo "Debugging error : " . mysqli_connect_errno() . PHP_EOL;
  exit;
}

$result = $con->query('show tables');

while($tables = mysqli_fetch_array($result)) {
  #$con->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
  $con->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
}

echo "The collation of your database has been successfully changed!";

---

Set the default collation of your database to utf8[mb4]_unicode_c manually.

If you have problems with utf8mb4_unicode_ci use utf8_unicode_ci instead.

Contao 3 with auto parameters

PHP Code:

---

<?php

include 'system/config/localconfig.php';

// Database credentials
$strHost     = $GLOBALS['TL_CONFIG']['dbHost'].':'.$GLOBALS['TL_CONFIG']['dbPort'];
$strUser     = $GLOBALS['TL_CONFIG']['dbUser'];
$strPassword = $GLOBALS['TL_CONFIG']['dbPass'];
$strDatabase = $GLOBALS['TL_CONFIG']['dbDatabase'];

// Main Script
$con = mysqli_connect( $strHost, $strUser, $strPassword, $strDatabase);

if (mysqli_connect_errno()) {
  echo "Error: Unable to connect to MySQL." . PHP_EOL;
  echo "Debugging error : " . mysqli_connect_errno() . PHP_EOL;
  exit;
}

$result = $con->query('show tables');

while($tables = mysqli_fetch_array($result)) {
  #$con->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
  $con->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
}

echo "The collation of your database has been successfully changed!";

---

Set the default collation of your database to utf8[mb4]_unicode_ci manually.

If you have problems with utf8mb4_unicode_ci use utf8_unicode_ci instead.