MySQL Bugs: #20645: Backup files created with mysqldump cannot be restored

Bug #20645	Backup files created with mysqldump cannot be restored
Submitted:	23 Jun 2006 2:14	Modified:	27 Jul 2006 16:49
Reporter:	Sean Molloy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Administrator	Severity:	S2 (Serious)
Version:	1.1.19	OS:	Windows (WinXP SP2)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Backup

Description:
If you create a .sql backup file using mysqldump on linux, and then attempt to restore this file using MySQL Administrator on Windows XP, the restore process will begin, go very very slowly, and complete seemingly without error - however - the data will not actually be restored into the schema.  If you click the "restore content" tab and attempt to analyze the file, you will get an immediate error "the analyzation failed". 

How to repeat:
Create a .sql file using mysqldump on a linux machine.
Copy the file to a windows machine.
Open MySQL Manager and open the backup file in "restore" window.
Leave all settings as default, click "start restore"
Process is very very slow and no data is restored

Alternatively:
Create a .sql file using mysqldump on a linux machine.
Copy the file to a windows machine.
Open MySQL Manager and open the backup file in "restore" window.
Leave all settings as default, click "restore content", then "analyze backup file content" - immediate error (no parsing attempted)

Suggested fix:
Workaround, partial:
Open the .sql file in an editor and delete the first few comment lines
Change the character set to "latin1" (even though the charset in the .sql file says "utf8")
Now procede with the restore - it will go very very slowly again, but this time the data is being restored into the target schema.  NOTE: blob/longblob fields will crash and can not be restored using this work around.

Thank you for the report.

---

I created dump using server built from last 5.0.23 sources on Linux machine. After I tried to restore it on Windows using server built from last 5.0.23 sources too. Behaviour was as described by reporter, unless I only remove first comment from the dump ("-- MySQL dump 10.10"). After it tables has successfull restored.

Thank you for taking the time to write to us, but this is not a bug. Read here http://dev.mysql.com/doc/administrator/en/mysql-administrator-restore-introduction.html about the fact that MA cannot restore mysqldump backups.

If this item is not a "bug", then when will the "feature" of restoring files created by mysqldump be added to the MySQL Administrator program?  Or has it even been requested yet?

I understand your wish that you want MA to be able to restore from dumps created by the command line client, however the internal handling of such dumps is quite different in MA and the CLI client. The primary purpose to offer a backup and restore feature which already is available in another tool was to add some extra values, mainly the ability to select what is to be written out and what should be restored (schema, tables etc.). This requires to parse the dump file in MA. However supporting the full syntax of MySQL in all its glory is a major task and MA only supports a (large) subset. This limits the input that can be handled by it and hence the format of the dumped data. This is the reason why we cannot accept other dump files than those created by MA. 

Unfortunately, changing the code to accept any (allowed) input would require too much time we don't have now. However, we have planned to include a full parser in new versions of our tools.