Out of memory during manual database backup with error in MySQLiSource.php

To workaround the “Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 294912 bytes) in ../backup_migrate/src/Core/Source/MySQLiSource.php on line 456'” error message, simply insert this line of code into your script at the top:

ini_set("memory_limit","16M");

This will set your memory limit to 16 MB, rather than 8 MB. You can, and should, fiddle with this number so that it is as low as possible without repeating that error message. This will only alter the memory limit for that particular PHP file.

Alternately, you can alter your php.ini file to up the memory limit. This will affect all scripts on your server. Simply open php.ini and find the line that reads “memory_limit” and alter it:

memory_limit=16M

A memory limit of 16M is not enough to run Drupal. 536870912 bytes is 512M, so if B&M is failing at 512M reducing it to 16M won't fix anything and would just cause more problems.

Noting that I got something very similar, WSOD and all, though I was trying to download the DB (not put in the private dir), with message 268435456 bytes exhausted (tried to allocate 172032 bytes) instead (same MySQLiSource.php:456 location), on a site that's newly on Pantheon (before remembering that Pantheon can just generate a backup for me). Also, the error and its message didn't show up for me in the dblog, but rather I found it on Pantheon's dashboard. The site is on Drupal 9.4.3, nginx/1.21.6, php 8.0.22, 10.4.25-MariaDB-log. And the .gz of the backup file I eventually got from Pantheon is 145M.

To try to narrow down possible variables, I also tried to replicate on another site where I know I've successfully used it before, and it still worked again there today. Same nginx/php/maria versions and 256M php memory limit as above, the only differences were that it's on 9.4.5, that I tested it from the Pantheon "Dev" site (can't do from "Live" at the moment), and that the downloaded .gz was only 1.5M. Obviously the biggest suspect is the size difference; is trying to use B&M to download a 145M-when-compressed DB too much (with the 256M memory limit)?

By coincidence, I found that after a deployment, the DB of the site where I got the WSOD today (i.e. when trying to use B&M to download its compressed 145M DB) had dropped down to 30MB (which was alarming at first, but figure out it was due to cache* tables being cleared). And I retried the download with B&M, and it worked this time (unlike the Pantheon gz, the B&M gz came in at just 19M). (Forgot to add, for completeness, just in case it's unexpectedly still relevant, the deployment did include an update to 9.4.5)

I guess that means that, if nothing else, as a workaround, a cache clear before using B&M might help avoid running into this?

I am having similar issue when using backup and migrate. Seems to work fine on smaller sites but fails on larger sites with a bigger db.

I was looking to use drush to run the backup to circumvent any memory issues but afaict there is no support for drush with latest drush and backup and migrate.

I also notice the message displayed :

It is recommended to not use the "Entire site" backup as it has a tendency of failing on anything but the tiniest of sites. Hopefully this will be fixed in a future release.

Obviously there are other tools available for backing up a large db's, but it looks to me as if backup and migrate is broken when using via the ui.

I was able to fix the issue by making MySQLiSource use unbuffered queries instead of the default, buffered queries.

Running into the same issue, I'm now testing the MR by @DieterHolvoet! Would be fantastic, if the fix is simple as that!

Whao IT WORKS!!! Today I learned something new! :D

Thank you @DieterHolvoet!!!

Can we please merge this and tag a new release with this important fix for larger backups @damienmckenna? This is absolutely fantastic!

Our dump is 5,8 GB uncompressed (700M compressed) and it just WORKS (using drush bb with no memory_limit and max_exectuion_time).

It also seems to fix #2919308: Module is leaving huge (+3GB) files in tmp directory potentially!

LGTM

Whao thank you so much for merging this @ivnish! Really important fix. Could you plan a stable release containing the major fix maybe?

Done! https://www.drupal.org/project/backup_migrate/releases/5.1.4

Huh. I never knew about unbuffered queries, either. Good to know!

Thanks