Bug #40789 Impossible to restore a database
Submitted: 17 Nov 2008 16:02 Modified: 24 Dec 2010 10:06
Reporter: james bond Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.67 OS:Solaris (5.11 snv_91)
Assigned to:
Tags: Backup, dump, mysqldump, restore

[17 Nov 2008 16:02] james bond
Description:
I'm trying to restore a database from a backup made with mysqldump (with those arguments: --default-character-set=latin1 --opt --skip-extended-insert)

I'm using this command to restore the database: mysql --default-character-set=latin1 -w -p -f db < db.sql

This database contains is around 50gig and it contains BLOB

After a while, i get this error:

ERROR 1064 (42000) at line 87105: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 4▒▒▒▒▒' at line 1
ERROR 1064 (42000) at line 87105: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'yv▒▒ˡ▒▒▒▒▒!:' at line 1
ERROR 1064 (42000) at line 87105: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '▒▒▒▒8c▒▒w▒9`0▒▒c▒z▒8▒\\▒㳳 ▒`▒7X' at line 1
ERROR at line 87105: Unknown command '\0'.
PuTTYERROR 2005 (HY000) at line 87105: Unknown MySQL server host '▒▒▒▒'▒ā▒;N▒c▒n0!Cnb▒90CYP▒▒�▒J▒,}'{S0q▒t▒▒!Ѕ▒▒▒A▒\,▒▒47|vz▒▒▒7▒0▒Ҵ▒|▒' (3)
PAGER set to stdout
PAGER set to stdout
ERROR at line 87105: Unknown command '\0'.
ERROR at line 87105: Unknown command '\''.
ERROR at line 87105: Unknown command '\0'.
ERROR at line 87105: Unknown command '\\'.
ERROR at line 87105: Unknown command '\0'.
ERROR at line 87105: Unknown command '\0'.
ERROR at line 87105: Unknown command '\0'.
ERROR at line 87105: Unknown command '\0'.
ERROR at line 87105: Unknown command '\\'.
ERROR at line 87105: Unknown command '\"'.
ERROR at line 87105: Unknown command '\0'.
ERROR at line 87105: Unknown command '\0'.
ERROR at line 87105: Unknown command '\0'.
ERROR at line 87105: Unknown command '\0'.
ERROR at line 87105: Unknown command '\0'.
ERROR at line 87105: Can't connect to the server

How to repeat:
Dump the database, try to restore it.
[26 Nov 2008 10:59] Sveta Smirnova
Thank you for the report.

Please provide output of SHOW VARIABLES LIKE 'max_allowed_packet'
[27 Dec 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[15 Mar 2010 10:57] jeff to
my my.cnf 
max_allowed_packet = 32M

i also have this problem.

--------------
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
ERROR 1102 (42000): Incorrect database name 'q0~0?sq0~0000000@000000!00000000?000?0000000004000rq0~0?sq0~0000000l000000?00000000?00'
ERROR: 
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
PAGER set to stdout
ERROR: 
Unknown command '\0'.
--------------
[15 Mar 2010 11:28] Sveta Smirnova
Jeff,

thank you for the feedback. max_allowed_packet = 32M looks too small for large dumps. Please check if this is higher than length of maximum query in the dump.
[15 Mar 2010 11:56] jeff to
sorry sveta,

i change 
max_allowed_packet = 32M
to 
max_allowed_packet = 100M
i got the error again.

but i change it to  
max_allowed_packet = 1000M
i got same infomation!!

the sql file i want to restore about 11G, what can i do?

the error follow this..
-------------
ERROR: 
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
ERROR: 
Unknown command '\0'.
No connection. Trying to reconnect...
ERROR 1102 (42000): Incorrect database name 'q0~0?sq0~0000000700000000000000?000?0000000004000rq0~0?sq0~0000000700000000000000?00'
ERROR: 
Can't connect to the server
------------
[1 Apr 2010 20:06] Ben Wheeler
I know this is an old thread but ran across the same problem. Looks like to effectively backup a database with blobs you need to add --hex-blob to your mysqldump command. When backing up with mysqldump, only text values are written and when it writes the binary data from the blob field there will most likely be a syntax error when trying to execute the SQL Statement created. If you use --hex-blob though it will write hex values instead of binary values eliminating the issue... Only bad thing is any blob data backed up with be twice as big as each character is converted to a hex..
[15 Apr 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[24 Nov 2010 2:15] Desmond Chew
I encountered similar issue while trying to import.

 mysql -uroot -p -Bs --max_allowed_packet=1000M --default-character-set=utf8 db1 < db.sql

Enter password:
ERROR at line 545: Unknown command '\0'.
[24 Nov 2010 10:06] Sveta Smirnova
Desmond,

thank you for the feedback. Could you please send us backed up datadir compressed and command you used to do dump. But if problem table is MyISAM just *frm, *MYD and *MYI file are enough.
[25 Dec 2010 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".