MySQL backup restore gives “Invalid command” error

Doing the MySQL backup

In this short post I would like to share with you a problem I encountered and the solution found while working with the MySQL tool mysqldump. The story is quite simple: doing some maintenance work on one production application server it was standard procedure to make a full MySQL backup before we did any work. So, we did using the following command:

mysqldump -u root - p --all-databases > DUMP-FILE-NAME.dmp

Dump file was created, it had about 3.5 GB and everything seemed fine. We started doing the operations planed and we hit an error which resulted in the database becoming compromised. We said, no problem, we just restore the backup and that’s all. So, we used the following command:

mysql -u root -p < DUMP-FILE-NAME.dmp

This started processing for a while and them booom, we got errors, lots of them:

ERROR at line 12282: Unknown command '\0'.

Invalid command "\0"

Invalid command "\\"

PAGER set to stdout

ERROR: Unknown command '\0'. ERROR 1102 (42000): Incorrect database name 'q0~0?sq

Looking into the file (with some difficulty due to it’s size) we realized that our BLOB columns were exported “as-is”, so with binary characters inside the insert queries. No wonder why the queries were not accepted on import. Doing some research it turns out that you can export BLOBs and later import them as long as the content of the BLOBs do not mess up the queries – so sometimes it works, sometimes it doesn’t. After more investigation it turns out we should have used something like:

mysqldump -u root -p --all-databases --hex-blob > DUMP-FILE-NAME.dmp

–hex-blob option encodes BLOBs, so the queries don’t get messed up anymore. Oh, how we wished we knew that before making the export and compromising the database. What is beyond me is why mysqldump does not do the encoding by default when exporting since chances are that the import will not work. Or at least why doesn’t it give a warning. Anyway, now we knew the problem, but the solution was not so obvious.

Solution that worked

After trying several things like:

  • splitting the dump file per database (we had aprox 100 databases in the dump out of which 30 were live)
  • thinking about extracting the values of the BLOB columns and replacing it with the encoded version
  • trying all sorts of googling

we were pretty miserable (it was already 11 pm), so we contacted our hosting provider to make the restore of the entire virtual machine – meaning we basically lost aprox 24h of work for our clients…

However, we had downloaded the 3.5 GB dump file and just for the heck of it we tried an import on our MySQL installation. To our amazement it worked. Why did it work? Turns out that on the server we had MySQL 5.0 while on our internal servers we had MySQL 5.5.

So, that means that the newer version of MySQL can import dumps with BLOBs even without the –hex-blob option. Interesting, isn’t it?

The obvious solution was therefore:

  • import the dump in MySQL 5.5
  • export it again but WITH the –hex-blob option; this resulted in a 6.3 GB file
  • import it on MySQL 5.0

We did just that, everything worked, we were all happy and around 1 am we could all go home “happy”.

Hope this experience is useful to others. Please leave me some comments if you have any questions/remarks.

John Negoita

View posts by John Negoita
I'm a Java programmer, been into programming since 1999 and having tons of fun with it.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top