MySQL 4.0 Gibberish Cannot Convert to Newer MySQL

Question

One of the websites I host still runs on PHP 5.2 with MySQL 4.0. Its text is in Hebrew (displays just fine on the site), but in the DB the text appears as gibberish containing question marks—but not exclusively. It looks some like: ?????£ ?§???¥ ???£ ?×???

I am trying to move this DB to MySQL 5.x with the same website, with no luck so far. I have tried using the MYSQL 40 compatibility mode, as well as other compatibility modes. I have made sure that the destination DB has the hebrew_bin collation as the old one, and I've played around with SET NAMES. The problem is, this is a type of gibberish I am not yet familiar with and therefore have no idea how to convert it to readable text.

score 0 · Accepted Answer · answered Dec 09 '14 at 14:50

I didn't get another answer here, and therefore had no choice but to write a simple script that manually does the migration process through PHP and not through MySQL dumps.

Here is the script, with some obvious modifications. Please note that the script is "dirty code", it might not be secure and it's not the most efficient, but it should get the job done if you're using it internally. Do not use in an environment where the script is accessible to the public without further modifications.

<?php
    $local = mysqli_connect([source server], [source username], [source password], [source DB name]) or die('No local connection');
    mysqli_query($local, "SET NAMES 'hebrew'");

    $remote = mysqli_connect([destination server], [destination  username], [destination password], [destination DB name]) or die('No remote connection');
    mysqli_query($remote, "SET NAMES 'utf8'");

    $table_list = array('table1', 'table2', 'table3'); // you can get a list of tables automatically too if the list is very long

    foreach ($table_list as $table)
    {
        $query_local = "SELECT * FROM `{$table}`";

        $result_local = mysqli_query($local, $query_local) or die('Error in q1: '.mysqli_error($local));

        $delete_remote = mysqli_query($remote, "DELETE FROM `{$table}` WHERE 1") or die('Error deleting table: '.$table); // necessary only if you plan to run it more than once

        $i = 0;
        while ($row_local = mysqli_fetch_assoc($result_local))
        {
            foreach ($row_local as $key => $value)
                $row_local[$key] = mysqli_real_escape_string($remote, $value);

            $query_remote = "INSERT INTO `{$table}` (`".implode('`, `', array_keys($row_local))."`) VALUES ('".implode("', '", $row_local)."')";

            $result_remote = mysqli_query($remote, $query_remote)
                or die('Error in remote q'.$i.' in table '.$table.':<br />&nbsp;&nbsp;'.mysqli_error($remote).'<br />&nbsp;&nbsp;Query: '.$query_remote);

            echo 'Successfully transferred row '.$i.' in table '.$table;
            echo '<br />'.PHP_EOL;

            $i++;
        }
    }
?>

MySQL 4.0 Gibberish Cannot Convert to Newer MySQL

1 Answers1