Bug #5850 mysql can't restore a backup created by mysqlbackup (MRG file not found)
Submitted: 1 Oct 2004 16:59 Modified: 22 Oct 2004 21:40
Reporter: P Fudd Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:mysql-3.23.58-1 OS:Linux (RedHat EL3/FC2)
Assigned to: CPU Architecture:Any

[1 Oct 2004 16:59] P Fudd
Description:
I've managed to create a database that mysqldump can back up, but mysql cannot restore it to a new machine.

Backup command:
mysqldump -ubackup -ppassword -a --opt --force -A | gzip -c > backup.sql.gz

Error when restoring:  Can't find "tablename.MRG"

Strace shows that the file is there, mysql finds it found, but one of the two tables specified in it hasn't been created yet.

Note: the backing up machine is mysql-3.23.58-1, the restoring machine is mysql-3.23.58-10.

How to repeat:
Create two identical tables, create a merge table, backup on one machine and restore to another.

Suggested fix:
Mysqlbackup should create the tables in the right order.
The error message should mention the missing table instead of the MRG file.
[1 Oct 2004 17:05] P Fudd
Sample backup file

Attachment: mysqldump.out.Friday.sql (text/x-sql), 22.61 KiB.

[1 Oct 2004 17:11] P Fudd
The restore command was 'mysql < mysqldump.out'.
I manually edited the dump file to remove all databases except the first, and the problem is unaffected.

Note: when I just copy the /var/lib/mysql/dbname directory to the new machine and try to restore again with 'mysql < mysqldump.out', I get the error "ERROR 1031 at line 552: Table handler for 'studentall' doesn't have this option".

And now when I do it, I get "ERROR 1062 at line 455: Duplicate entry '1' for key 1".

Could you put a better restore example onto the mysqldump page?

Thanks!
[1 Oct 2004 17:32] P Fudd
By 'better' I mean 'restore the entire database onto a fresh server'.  I'm unsure if 'mysql < dumpfile' is correct for entire-database dumps.

Thanks!
[22 Oct 2004 21:40] Hartmut Holzgraefe
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Verified on 3.23.57 but not on 4.0.21 or 4.1.5