Results 1 to 5 of 5

Thread: Ever needed to change collation of table?

  1. #1

    Default 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!";
    OM MANI PEME HUNG! how many has to die for freedom and dignity. Save this world

  2. #2
    User Andreas's Avatar
    Join Date
    07-11-09.
    Location
    Mönchengladbach
    Posts
    485

    Default

    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.
    Web-Development, Freelancer, Burgtech, XHTML, HTML5, CSS, PHP, Javascript, MooTools, MySQL and more
    Amazon wishlist

  3. #3

    Default

    Good catch, thanks for your input.
    OM MANI PEME HUNG! how many has to die for freedom and dignity. Save this world

  4. #4
    User Andreas's Avatar
    Join Date
    07-11-09.
    Location
    Mönchengladbach
    Posts
    485

    Idee Contao 4 with auto parameters

    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.
    Last edited by Andreas; 04/08/2019 at 13:52.
    Web-Development, Freelancer, Burgtech, XHTML, HTML5, CSS, PHP, Javascript, MooTools, MySQL and more
    Amazon wishlist

  5. #5
    User Andreas's Avatar
    Join Date
    07-11-09.
    Location
    Mönchengladbach
    Posts
    485

    Idee Contao 3 with auto parameters

    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.
    Last edited by Andreas; 04/08/2019 at 13:48.
    Web-Development, Freelancer, Burgtech, XHTML, HTML5, CSS, PHP, Javascript, MooTools, MySQL and more
    Amazon wishlist

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •