Originally Posted by Zarzal
Thanks again. For my board there is memory problem while converting POSTS. The script must do it in steps or you get:
Something like this should do the trick:
Code
<?php
$db = new mysqli( "localhost", "DATABASE_USERNAME", "DATABASE_PASSWORD", "DATABASE_NAME" );
$db -> set_charset( "utf8" );

$offset = filter_input( INPUT_GET, "offset", FILTER_SANITIZE_NUMBER_INT );
$offset_sql = ( $offset ) ? " OFFSET " . $db -> escape_string( $offset ) : "" ;

$result = $db -> query( "
    SELECT 
    POST_ID, 
    convert(cast(convert(POST_SUBJECT using latin1) as binary) using utf8), 
    convert(cast(convert(POST_BODY using latin1) as binary) using utf8), 
    convert(cast(convert(POST_DEFAULT_BODY using latin1) as binary) using utf8) 
    FROM 
    ubbt_POSTS
    LIMIT 100" . $offset_sql. ";
" );

if( $result -> num_rows )
{
    
    while( list( $post_id, $post_subject, $post_body, $post_default_body ) = $result -> fetch_row() )
    {
        $db -> query( "
            UPDATE 
            ubbt_POSTS 
            SET 
            POST_SUBJECT = '" . $db -> escape_string( $post_subject ) . "',
            POST_BODY = '" . $db -> escape_string( $post_body ) . "',
            POST_DEFAULT_BODY = '" . $db -> escape_string( $post_default_body ) . "'
            WHERE
            POST_ID = '" . $post_id . "';
        " );

        echo "Processing post #".$post_id."<br>";
    }

    $offset = $offset + 100;
    echo "<script>setTimeout(function() {window.location.href = \"" . $_SERVER['PHP_SELF'] . "?offset=" . $offset . "\"}, 2000);</script>";

}
else
{
    echo "Finish";
}

$db ->close();
exit;
?>
I found in the meantime an easier way based on https://www.whitesmith.co/blog/latin1-to-utf8/. However, this requires shell access to run the mysqldump tool directly:
Code
mysqldump -uUSERNAME -pPASSWORD --skip-set-charset --default-character-set=latin1 DATABASENAME > database.sql

Using --skip-set-charset --default-character-set=latin1 will use a Latin1 connection for the SQL dump, so the dump contains the original UTF-8 characters. Then re-import the dump in UTF-8. This method works also for Latin1 tables with UTF-8 data. In this case, you need to adjust CHARSET=latin1 to CHARSET=utf8 in the SQL dump before re-importing the data.