MySQL Bugs: #14236: Very large backup files fail to restore

Bug #14236	Very large backup files fail to restore
Submitted:	23 Oct 2005 8:02	Modified:	28 Jul 2006 15:31
Reporter:	Steve Tanner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.1.4	OS:	Windows (Windows XP SP2)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Backup

Description:
When trying to restore a very large backup file created in mysql administrator, the application will not analyze or restore the data.  During the analysis stage it reports that the total number of bytes is 2350900372 and that 0 bytes were processed.  It also reports that "The analyze operation was finished successfully."  

If I restore a smaller backup file first and then open the very large file, mysql administrator will crash with an access violation trying to read 0xFFFFFFFF.

I've also tried it with the 1.0.x series and 1.1.0rc versions.  I am connecting to server version 5.0.13-rc-log via TCP/IP from a Windows XP SP2 client with 4GB RAM (though interestingly enough, MySQL administrator only reports 2GB).

How to repeat:
The MySQL Administrator backup file that is failing is 10.1GB.  While that may seem a bit obscene, it does serve the purpose of what I'm trying to do for this application.   Other 80-90MB files restore without a problem.  I would imagine any file larger than the bytecount reported above by the analysis dialog would break it.  YMMV.

Suggested fix:
Probably a 32-bit pointer problem, but I'm not familiar with the inner workings of the app so I couldn't tell you.  2,350,900,372 isn't 2^31, but then again this is a windows dialog so who knows ;).  All around this app is fantastic and getting better with each version, this is the first time I've ever had an issue with it in well over a year's worth of moderatly heavy use.

Hi!

Thanks for your bug report.  I was able to reproduce this bug using a huge backup (2,000,000 rows)

I've the same problem, my file ist 8 GB ... Looking forward to a solution :(

I have upgraded to version 1.1.5 and I am running Administrator on a Windows 2003 Server with the same result.

Another problem which you might have noticed - and a quick fix for this problem - 

The number incrementing on the progress Window repeats itself in the top lefthand corner of the your screen - which I believe is related to this problem.

TO FIX - when you start analyzing the backup, be quick and click on the Analyze icon on the task bar.  This will hide/minimize the Analyze progress window behind the Administrator window.  This speeds up the analyze process tremendously and it runs right through to the end without bombing out.  Only problem is that you will have to guess how long it takes to complete the Analyze process.

If you open up any other window - like notepad, and hide the progress window behind Notepad, it also works.   Hope this helps until a permanent fix becomes available.

Just ran into this nasty bit of integer - long confusion.  It bombs out on a smaller file than 2 Gb, however, I cannot restore a 600 Mb file.  Is there a suggested or preferred work around that we can add to this bug?  I tried using the command line, but got the dreaded "MySQL server has gone away".

My restore fails at byte 153776465  that's about halfway through a (303859609)
approx 300MB file with:MySQL Error Nr.2006-MySQL server has gone away

I forgot to mention, but the Analyse seems to work OK.
Although it only processed 303859426 of 303859609 bytes....

Is there a workaround to this problem ? For example can we dump the db file as an image (not backup) to CD/DVD and then point MySQL at the image on the new machine ?

Hi,

We are already working on a similar problem and hope to find a solution soon. Unfortunately there's no MA-based workaround so far. You can try to use command line tool to restore data.

I've backed up manually with the mysqldump utility.
Now I try  to restore:
mysql --user=<user> --password="<password>" --database=mydb --quick < mydocsdb.sql
ERROR 2006 (HY000) at line 39: MySQL server has gone away

If this is correct (i.e. that restoration fails through the command line as well as the admin GUI); this seems to indicate that restores (and by implication backups) are useless.
Can this really be a NON-CRITICAL bug  ?

Hi Peter,

can you please provide an example of sql dump which cannot be restored along with versions of mysql software you're using.

thanks

Peter, I've sent you the instructions how to send the file, please check your email

Thanks

The backup file size doesn't seem to be the issue, here. I have a case where a ~1MB backup file will not restore correctly, resulting in the following error:

MySQL Error Nr.2006-MySQL server has gone away

Interesting to note is that I suspected it might be caused by the contents of one LONGTEXT field in one record with almost 1MB of text (this was a test record - looks like it worked as a test!). Using a text editor I truncated this field in the .sql file to just a line or two of text and it restored just fine.

I haven't gone through the steps to isolate where it breaks *exactly* within that record, but it was definitely that field in that record that broke things.

I have also encountered this error.  It is not a problem unique to the QUI Administrator as  the following command line:

mysql -uroot -p < backup.sql

also fails with the following error - 
Error 2006 (HY000) at line 3,211,319: Mysql server has gone away.

This occured while trying to create a replicated slave from a master running mysql 4.1.14-nt-max-log to a slave running 4.1.16-nt-max-log.

The backup sql file is over 5gb so I can't just send it over.

I've also tried establishing a connection then using "source", turning off foreign_key_checks and autocommit.  Fails.

The database file does have some BLOBs which are probably 4-6mb in it.

Any suggestions would be greatly appreciated.  

Ross Carver

Hi,

I got the MySQL Error Nr.2006-MySQL server has gone away when trying to restore a 580MB file with mySQL administrator 1.1.9. However when I set the max_allowed_packet=100M in the my.ini file everything works fine. I dont know if this helps anyone.

The above solution worked for me.

I was getting the dreaded "server has gone away" error while restoring a 1.75MB file....

after setting:

set-variable  = max_allowed_packet=12M

in my.ini I was able to import the file via MySQL administrator.....

MySQLAdministrator v1.1.9

By adding set-variable  = max_allowed_packet to my.ini of the MySQL server it resolved this issue for me too.

The issue begun when I added a 500K pdf file to VBulletin forum based on MySQL.

Glad I checked I could restore feature now!

Lea

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html

I have the same problem when I try to restore tables with large files stored in. I see the bug status Closed, but with the last recomended version (5.0.27) I can't restore. The only solution is to modify the value of the variable max_allowed_packet as suggested in previous comments. Is it the only solution?

Adding...

set-variable  = max_allowed_packet=12M

To my.ini solved my problem too.  Thanks!!

I have a table with a longblob column and when it it tries to insert in the restore process I get an error.

Error while executing this query:INSERT INTO `nikos_upload` (`id`,`name`,`type`,`size`,`content`,`comment`) VALUES
(38,'2007.jpg','image/jpeg',999622,0xFFD8FFE000104A46494600010101000000000000FFFE000C4170706..........................and so on.

total number of bytes           2165177
total number of bytes processed 2160110

"ERROR 2006 (HY000) at line 78: MySQL server has gone away" 

I had the same problem, i changed:

[InnoDB]
  InnoDB_fast_shutdown = off

[networking]
   [data/memory size]
     max_allowed_Packet = 100MB

   [timeout settings]
    connect_timeout = 60 (seconds)
    interactive_timeout = 57600

   [advanced]
    max_connections = 200

and in preferences turn of:
"Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)." 

not sure if its related, but it worked for me...