Fixing Corrupt MySQL Dumps with Windows 10 Power Shell

This article is intended for people who had the unfortunate event of restoring a a dump of databases created via Windows 10 Power Shell without any regards for the encoding, just a straight:

mysqldump.exe -u root -p --all-databases > alldb.sql

The problem comes from the strange encoding the dump is using which is most probably ISO-10646-UCS-2.The thing was I had no idea what the encoding was and all efforts to import it to the new MySQL / MariaDB server were failing. The initial debugging showed that the encoding (not UTF-8) was making the problems.

The solution

  1. Download and install LibIconv for Windows, it is a nice free tool when it comes to converting the encoding of text files or in this case SQL dumps.
  2. Open up command prompt and run the following commands replacing the paths of the files with your own (iconv.exe location + the corrupt SQL dump):
    "C:\Program Files (x86)\GnuWin32\bin\iconv.exe" -f ISO-10646-UCS-2 -t 437//TRANSLIT "D:\all-db-dump.sql" > "D:\out.sql"

    This will create a UTF-8 version of the dump, regardless of the 437 encoding specified in the file.

  3. Import the converted dump on the server.

If this does not work for you and you are still getting errors with your import, that means that the combination of from / to encoding is a bit different. So how to find the correct one?

For this purpose I have created a simple PHP script that will go through all the combinations, try them and see if they work. The only downside is that you need to know at least one string from the database, any column, any field, to be used as a check for the successful conversion.

$enc = shell_exec('"C:\Program Files (x86)\GnuWin32\bin\iconv.exe" -l'); 
$tmp = str_replace(array("\r", "\n"), " ", trim($enc)); 
$tmp = explode(' ', $tmp); 
$test_string = 'Друго демо'; 
function check(){ $data = file_get_contents('D:/out.sql'); $data2 = iconv(mb_detect_encoding($data, mb_detect_order(), true), "UTF-8", $data); if(stripos($data, $test_string) !== false || stripos($data2, $test_string) !== false){ return true; } return false; } 
foreach($tmp as $encoding_from){ 
	foreach($tmp as $encoding_to){ 
		$cmd = '"C:\Program Files (x86)\GnuWin32\bin\iconv.exe" -f '.$encoding_from.' -t '.$encoding_to.'//TRANSLIT "D:\all-db-dump.sql" > "D:\out.sql"';
			file_put_contents('found.txt', $encoding_from.' '.$encoding_to.'//TRANSLIT'."\n", FILE_APPEND);
			echo $encoding_from.' '.$encoding_to.'//TRANSLIT ';
			echo 'YES';

		$cmd = '"C:\Program Files (x86)\GnuWin32\bin\iconv.exe" -f '.$encoding_from.' -t '.$encoding_to.' "D:\all-db-dump.sql" > "D:\out.sql"';
			echo $encoding_from.' '.$encoding_to.' ';
			echo 'YES';				
			file_put_contents('found.txt', $encoding_from.' '.$encoding_to."\n", FILE_APPEND);

The idea is to loop through all the combinations and check for the $test_string. If it is found, all the combinations are saved to the found.txt file so you can try them manually.

Good luck!

Category : MySQL, PHP
Tags :