Failure of automatic backup with large database
markdionne - December 18, 2006 - 03:09
| Project: | Database Administration |
| Version: | 5.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | patch (code needs work) |
Description
Using Drupal 4.7.4 and dba, database backups of 500KB were being successfully emailed. When the database grew to 1MB, mailing stopped working. The failure is happening in this line of code in the dba_mail_backup function:
mail->add_attachment("$attach", $attachment->filename, "Content-Transfer-Encoding: base64 /9j/4AAQSkZJRgABAgEASABIAAD/7QT+UGhvdG9zaG");
That line of code is reached, but the add_attachment function is not reached. I suspect the "$attach" operation is allocating memory to make a copy of the data, and failing. (Are the quotes even necessary here?)
No matter what you do, PHP is probably going to stop giving memory when the data gets to some size, Is there some way to detect this and fail with a warning?
#1
I am finding this to be a fairly big problem. The way the backup code works at the moment the entire backup is stored as PHP variable, first while it builds the SQL script and then while it builds the compressed file.
The only way I can see this being fixed/improved is if the backup system moves to a file based system, were the SQL script gets appended to a file as the SQL get generated and then execute a command line call to do the compression. That way all that needs to get stored as a PHP variable is file path and at most one record of a table at a time.
I don’t know how this could affect windows based systems. I don’t think windows can do command line calls for compression.
#2
I am a little paranoid about backups, so for my own sanity I had a go at this problem.
I have added 3 function dba_save_to_file(), dba_bzip2(), dba_gzip().
And I have modified the dba_backup_table() function and dba_auto_backup() function so that all the data is handled using files.
I have tested both bz2 and gz with the cron that it seems to work. I also tested a restore using the compressed files and every thing seem to work fine. I have not tested anything else. I see you can also manually back tables. I have not tested this, but I did try to make the dba_backup_table() function work normally when it is not being used by dba_auto_backup().
I did not test this with the email function. The database I tested this on is rather large and the compressed file is over 5 Megs. I don’t think this solves all the problems with large databases. Eventually a database can get so big that the cron will start to run out of time. But if this does happen a person could always just edit the time limit set in the drupal_cron_run() function.
Can somebody else please test this patch?
#3
Thank you so much, this patch solves a big problem for me. The backup of one single subdomain got up to 130MB and without writing directly to files, this was crashing every time.
The patch didn't fully work at the beginning. The patch got rejected for the dba_auto_backup() function, so I had to apply this manually.
Also, I have to include
set_time_limit(0);at the beginning of dba_auto_backup, otherwise the process times out.Other than that it is really great, thank you so much.
#4
Indeed, patch doesn't apply cleanly. If anyone cares to re-roll this, I'll try to review and commit it quickly. Thanks.
#5
Related to my report at: http://drupal.org/node/251012
After applying the patch in the referenced item manually AND increasing the timeout interval for cron AND screening out all of the largest tables (in particular the old_revisions table from my upgrade to v5) the backup ran successfully. There are obviously still some major memory and timeout issues that need addressed.
#6
I'm not competent myself to fix the patch but am wondering if anything is being done on this module for v5 any longer? This bug has been around for more than 18 months now.