Home / server / Questions / 218887
Accepted
Daniel Magliola
Asked: 2011-01-06 01:08:11 +0800 CST

Error importing large MySQL dump file which includes binary BLOBs in Windows

  • 772

I'm trying to import a MySQL dump file, which I got from my hosting company, into my Windows dev machine, and i'm running into problems.

I'm importing this from the command line, and i'm getting a very weird error:

ERROR 2005 (HY000) at line 3118: Unknown MySQL server host '╖?*á±dÆ╦N╪Æ·h^ye"π╩i╪ Z+-$▼₧╬Y.∞┌|↕╘l∞/l╞⌂î7æ▌X█XE.ºΓ[ ;╦ï♣éµ♂º╜┤║].♂┐φ9dë╟█'╕ÿG∟═0à¡úè♦╥↑ù♣♦¥'╔NÑ' (11004)

alt text

I'm attaching the screenshot because i'm assuming the binary data will get lost...

I'm not exactly sure what the problem is, but two potential issues are the size of the file (2 Gb) which is not insanely large, but it's not trivially small either, and the other is the fact that many of these tables have JPG images in them (which is why the file is 2Gb large, for the most part).
Also, the dump was taken in a Linux machine and I'm importing this into Windows, not sure if that could add to the problems (I understand it shouldn't)

Now, that binary garbage is why I think the images in the file might be a problem, but i've been able to import similar dumps from the same hosting company in the past, so i'm not sure what might be the issue.

Also, trying to look into this file (and line 3118 in particular) is kind of impossible given its size (i'm not really handy with Linux command line tools like grep, sed, etc).

The file might be corrupted, but i'm not exactly sure how to check it. What I downloaded was a .gz file, which I "tested" with WinRar and it says it looks OK (i'm assuming gz has some kind of CRC). If you can think of a better way to test it, I'd love to try that.

Any ideas what could be going on / how to get past this error?

I'm not very attached to the data in particular, since I just want this as a copy for dev, so if I have to lose a few records, i'm fine with that, as long as the schema remains perfectly sound.

Thanks!
Daniel

mysql import binary

5 Answers

  1. Best Answer

    For this reason I always use mysqldump --hex-blob.

    Re-dump the database encoding the blobs using this switch and it will work.

    You can try to import it using a windows mysql client IDE like sqlyog or mysql administrator. It worked for me once.

    • 21

  2. You do not necessarily need to use the --hex-blob option. I have just resolved this problem my self and the issue was that I needed the --max_allowed_packet to be set to a large enough value to accommodate the largest data blob I would be loading. Your restore command should look something like:

    mysql -u user -h hostname --max_allowed_packet=32M dbname < dumpfile.sql

    If you use the --hex-blob option you will significantly increase the size of your backup - by a factor of 2 or more. NOTE: to restore the same data that I restored with the above command required setting the --max_allowed_packet=64M in my.ini(cnf) and restarting the server AS WELL AS setting it to 64M on the command line to restore a dump created with the --hex-blob option.

    • 9

  3. There could still be a problem because of large file size so make sure you set max-allowed-packet to some high value (param for mysql command).

    • 2

  4. Ok, I had this issue today. But my problem was that the database was already dropped when I realized that the backup was broken. So, no --hex-blob for me! To be able to fix it I made a little script in PHP that converts the "binary string" into the hex representation where the values are represented like "_binary '!@{#!@{#'"...

    It's using a REGEX for parsing the SQL, which is not completely safe, but it did the job for me.

    <?php
function convertEncoding($str)
{
    $r = '';
    for ($i = 0; $i < mb_strlen($str); $i++) {
        $r .= sprintf('%02X', mb_ord(mb_substr($str, $i, 1, 'UTF-8'), 'UTF-8'));
    }

    return '0x' . $r;
}


$str = file_get_contents('data.sql');

$newStr = preg_replace_callback('/_binary \'(.+?)\'(,|\))/im', function ($str) {
    $s = convertEncoding(stripcslashes($str[1]));
    echo 'Translated: ' . $str[1] . ' => ' . $s . PHP_EOL;
    echo 'Ending char was: ' . $str[2] . PHP_EOL;
    return $s . $str[2];
}, $str);

file_put_contents('fixed.sql', $newStr) ;

    I hope it saves someone the headache I got!

    • 2

  5. I have similar problem when restore a dump file from Linux server which contains binary data. The errors are something like ERROR 1064 (42000) at line 551: You have an error in your SQL syntax;

    This dump file could be successfully imported into Linux server but not Windows.

    I have tried with --hex-blob option and --max_allowed_packet and even transferring data with pipeline instead of .sql file, but with no luck.

    I finally solved this by using MySQL Workbench, and the generated command is like

    Running: mysql.exe --defaults-file="c:\users\admini~1\appdata\local\temp\tmp1fzxkx.cnf"  --protocol=tcp --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments --database=platform  < "E:\\direcotory\\dump.sql"

    Then I tried with --default-character-set=utf8 from command line and it worked. Hope this will help someone.

    • 0