Results 1 to 3 of 3

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

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
  •