Craft 3 Database Backups Failing

[jonlivingston]

Description

When running a database backup, either manually from the admin panel (Utilities > Database Backup > Backup) or the auto backup when craft is in the process of updated itself, the backup fails with the following error:

Couldn’t backup the database. How would you like to proceed?

Note I'm using MAMP Pro.

Steps to reproduce

1. Go to Admin -> Utilities -> Database Backup
2. Click Backup - red warning bar at top appears saying to check the log

Additional info

• Craft version: Craft Personal 3.0.0-RC6
• PHP version: 7.1.1
• Database driver & version: MySQL 5.6.35
• Plugins & versions: None

When looking at logs I get the following:

yii\web\HttpException:400

yii\web\BadRequestHttpException: Request missing required body param in /Users/jon/Sites/onboardcreative/vendor/craftcms/cms/src/web/Request.php:497
Stack trace:
#0 /Users/jon/Sites/onboardcreative/vendor/craftcms/cms/src/controllers/UtilitiesController.php(376): craft\web\Request->getRequiredBodyParam('params')
#1 [internal function]: craft\controllers\UtilitiesController->actionDbBackupPerformAction()
#2 /Users/jon/Sites/onboardcreative/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#3 /Users/jon/Sites/onboardcreative/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#4 /Users/jon/Sites/onboardcreative/vendor/craftcms/cms/src/web/Controller.php(80): yii\base\Controller->runAction('db-backup-perfo...', Array)
#5 /Users/jon/Sites/onboardcreative/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('db-backup-perfo...', Array)
#6 /Users/jon/Sites/onboardcreative/vendor/craftcms/cms/src/web/Application.php(251): yii\base\Module->runAction('utilities/db-ba...', Array)
#7 /Users/jon/Sites/onboardcreative/vendor/craftcms/cms/src/web/Application.php(453): craft\web\Application->runAction('utilities/db-ba...', Array)
#8 /Users/jon/Sites/onboardcreative/vendor/craftcms/cms/src/web/Application.php(211): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#9 /Users/jon/Sites/onboardcreative/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#10 /Users/jon/Sites/onboardcreative/web/index.php(21): yii\base\Application->run()
#11 {main}

[angrybrad]

That's weird...

The stack trace you provided is from the Database backup utility. Can you share the stack trace for the error when backing up during an update?

Have you made any .htaccess modifications from Craft's stock one in the public web folder?

[jonlivingston]

Thanks for the reply.

I haven't made any changes to the .htaccess file. Here's what I have:

<IfModule mod_rewrite.c>
	RewriteEngine On

	# Send would-be 404 requests to Craft
	RewriteCond %{REQUEST_FILENAME} !-f
	RewriteCond %{REQUEST_FILENAME} !-d
	RewriteCond %{REQUEST_URI} !^/(favicon\.ico|apple-touch-icon.*\.png)$ [NC]
	RewriteRule (.+) index.php?p=$1 [QSA,L]
</IfModule>

Here's the stack trace for the error when backing up during an update.

craft\web\ServiceUnavailableHttpException in /Users/jon/Sites/onboardcreative/vendor/craftcms/cms/src/web/Application.php:564
Stack trace:
#0 /Users/jon/Sites/onboardcreative/vendor/craftcms/cms/src/web/Application.php(165): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#1 /Users/jon/Sites/onboardcreative/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#2 /Users/jon/Sites/onboardcreative/web/index.php(21): yii\base\Application->run()
#3 {main}

[angrybrad]

The utilities backup stack trace is complaining because it's expecting a required value to be in POST with a key of params and it doesn't exist, so it halts the process.

Code-wise, I'm having a hard time seeing how that key wouldn't exist or it would have an empty value: https://github.com/craftcms/cms/blob/develop/src/web/assets/dbbackup/dist/DbBackupUtility.js#L35-L40

The 2nd stack trace is just a 503 status for when someone hits the front-end of the site while Craft is in "maintenance mode" when updating a site. The update error should be somewhere else in the logs for this case.

[jonlivingston]

Is this the correct error? I'm a little unsure of where I'm suppose to look.
503 Service Unavailable.pdf

[jonlivingston]

On a side note I have reverted the update when trying to update from the CP. Maybe that's why I'm not getting the full error. I was worried if I continued it would really mess up something.

Should I select "Continue Anyway" like seen below

[angrybrad]

@jonlivingston for the 2nd one (where you can an error backing up during an update), can you reproduce with devMode enabled, the share a zip of your craft/storage/logs folder? You can either attach it here or send to [email protected] if there is anything sensitive in there you don't want to share.

[jonlivingston]

@angrybrad devMode is enabled and I tried updating Craft. Here's the stack trace. The log files are attached. Thanks for trying to get an answer on this.

craft\web\ServiceUnavailableHttpException in /Users/jon/Sites/onboardcreative/vendor/craftcms/cms/src/web/Application.php:564
Stack trace:

#0 /Users/jon/Sites/onboardcreative/vendor/craftcms/cms/src/web/Application.php(165): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#1 /Users/jon/Sites/onboardcreative/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#2 /Users/jon/Sites/onboardcreative/web/index.php(21): yii\base\Application->run()
#3 {main}

logs.zip

[angrybrad]

Ahh... here's the error from the failed backup during updating:

2018-01-28 14:01:18 [::1][1][-][error][craft\controllers\UpdaterController::actionBackup] Error backing up the database: The shell command "mysqldump --defaults-extra-file="/Users/jon/Sites/onboardcreative/storage/runtime/temp/my.cnf" --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --set-charset --triggers --single-transaction --no-data --result-file="/Users/jon/Sites/onboardcreative/storage/backups/onboard-creative_180128_220118_rjcjsc7wom_v3.0.0-rc7.1.sql" onboardcreativedb && mysqldump --defaults-extra-file="/Users/jon/Sites/onboardcreative/storage/runtime/temp/my.cnf" --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --set-charset --triggers --no-create-info --ignore-table=onboardcreativedb.assetindexdata --ignore-table=onboardcreativedb.assettransformindex --ignore-table=onboardcreativedb.cache --ignore-table=onboardcreativedb.sessions --ignore-table=onboardcreativedb.templatecaches --ignore-table=onboardcreativedb.templatecachecriteria --ignore-table=onboardcreativedb.templatecacheelements onboardcreativedb >> "/Users/jon/Sites/onboardcreative/storage/backups/onboard-creative_180128_220118_rjcjsc7wom_v3.0.0-rc7.1.sql"" failed with exit code 127: sh: mysqldump: command not found

Which is just saying that Craft/PHP can't find the mysqldump executable, so it'll need to be put in something like the global PATH variable so PHP has access to it.

The first error from the backup utility still doesn't make sure, but I'd fix the mysqldump path issue and I bet that one will be resolved, too. If it's not, just comment back here again and we can re-open.

[jonlivingston]

Ahh! Thank you @angrybrad!

If anyone runs into this issue not being able to update Craft 3 using MAMP or MAMP Pro here's what was done to resolve the issue.

Create an "envvars" file with the following code:
export PATH=/usr/local/bin:$PATH

Place it in the following directory:
/Applications/MAMP/Library/bin/

[codyjames]

I'm getting this same issue on Mac OS, not using MAMP. Any ideas on how I'd resolve this?

[angrybrad]

@codyjames regardless of the development stack you are using, mysqldump needs to be available for PHP to access. The easiest way to do that is to add the path it lives in to your PATH environment variable.

[juban]

Hi @angrybrad.

I can understand that the backup process depends on the mysqldump command.
Anyway, this adds some weird strong dependency at an architecture level.
Needed PHP dependancies, like required extensions are totally fine.
But here, Craft adds some additional "external" dependency which could be problematic at some point.
For instance, let's say your MySQL database is handled by a tier server or you run your PHP stack in a lightweight PHP-FPM container. In that case, you would have to add the entire MySQL server software to that container just to be able to perform the backup process.
I don't see an easy solution here, but I guess there could be a better way to handle that (fallback to pure PHP backup process maybe).
A least, there should be some warning in the system requirements check system.

Thanks.

[angrybrad]

@juban fair points. should probably also mention there is a backupCommand config setting, which can be set to run any logic you want. i.e. shell script, http request, etc. where you could remove the mysqldump dependency if needed.

[juban]

@angrybrad Thank you for your feedback. I'll take a look to that backupCommand configuration setting for sure. Sounds good.
For anyone wanted to add the needed dependencies and don't want the entier MySQL server libs, just add the mysql client library, and it will do the trick.
For exemple, for an Ubuntu distribution:
sudo apt-get install mysql-client-5.5

[darylknight]

@jonlivingston What's an "envars" file? Trying to do this on Windows and not sure what to save the file as. I'm assuming it's not just "envars" with no file extension?

[engram-design]

@jonlivingston Part of the way there for me, seemed like I needed to symlink these binaries into /usr/local/bin by doing:

sudo ln -s /Applications/MAMP/Library/bin/mysql /usr/local/bin/mysql
sudo ln -s /Applications/MAMP/Library/bin/mysqlcheck /usr/local/bin/mysqlcheck
sudo ln -s /Applications/MAMP/Library/bin/mysqldump /usr/local/bin/mysqldump

[joespiteri]

Just a heads up for anyone still struggling with this, there's a guide on the craft site: https://craftcms.com/guides/database-backups-in-craft-3-with-mamp

[sposhe]

⬆️ the above also solves a related issue with the FeedMe plugin failing.

[gbowne-quickbase]

I solved this issue with Craft not finding the mysqldump in my path. I knew mysqldump was available for me, but the exec command didn't know where to find things. So I added a line to my general.php
putenv('PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin:');
Of course, your path should go there

[proimage]

What would be the way to resolve this under Windows (I'm using XAMPP)? Here's an excerpt from my log file when a backup fails:

2019-07-10 06:30:56 [-][1][-][error][yii\base\Exception] yii\base\Exception: Could not create backup: The shell command "mysqldump --defaults-extra-file="E:\User Data\Michael\Projects\My-Project\storage\runtime\temp\my.cnf" --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --set-charset --triggers --single-transaction --no-data --result-file="E:\User Data\Michael\Projects\My-Project\storage\backups/israel-allies-foundation_190710_133056_lfngdk0efw_v3.1.34.sql" iaf_craft && mysqldump --defaults-extra-file="E:\User Data\Michael\Projects\My-Project\storage\runtime\temp\my.cnf" --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --set-charset --triggers --no-create-info --ignore-table=iaf_craft.assetindexdata --ignore-table=iaf_craft.assettransformindex --ignore-table=iaf_craft.sessions --ignore-table=iaf_craft.templatecaches --ignore-table=iaf_craft.templatecachequeries --ignore-table=iaf_craft.templatecacheelements --ignore-table=iaf_craft.cache --ignore-table=iaf_craft.templatecachecriteria iaf_craft >> "E:\User Data\Michael\Projects\My-Project\storage\backups/israel-allies-foundation_190710_133056_lfngdk0efw_v3.1.34.sql"" failed with exit code 1. in E:\User Data\Michael\Projects\My-Project\vendor\craftcms\cms\src\controllers\UtilitiesController.php:348
Stack trace:
#0 [internal function]: craft\controllers\UtilitiesController->actionDbBackupPerformAction()
#1 E:\User Data\Michael\Projects\My-Project\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)
#2 E:\User Data\Michael\Projects\My-Project\vendor\yiisoft\yii2\base\Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#3 E:\User Data\Michael\Projects\My-Project\vendor\craftcms\cms\src\web\Controller.php(110): yii\base\Controller->runAction('db-backup-perfo...', Array)
#4 E:\User Data\Michael\Projects\My-Project\vendor\yiisoft\yii2\base\Module.php(528): craft\web\Controller->runAction('db-backup-perfo...', Array)
#5 E:\User Data\Michael\Projects\My-Project\vendor\craftcms\cms\src\web\Application.php(297): yii\base\Module->runAction('utilities/db-ba...', Array)
#6 E:\User Data\Michael\Projects\My-Project\vendor\craftcms\cms\src\web\Application.php(565): craft\web\Application->runAction('utilities/db-ba...', Array)
#7 E:\User Data\Michael\Projects\My-Project\vendor\craftcms\cms\src\web\Application.php(281): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#8 E:\User Data\Michael\Projects\My-Project\vendor\yiisoft\yii2\base\Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#9 E:\User Data\Michael\Projects\My-Project\web\index.php(21): yii\base\Application->run()
#10 {main}
2019-07-10 06:30:56 [-][1][-][info][application] $_GET = [
    'p' => 'control/actions/utilities/db-backup-perform-action'
]

When I run cmd to open a command prompt, and type mysqldump, the program is found:

It's in my PATH environment variable:

Finally, not sure if it's relevant or not, but both httpd.exe and php.exe are running under my user account:

What am I missing?

[angrybrad]

failed with exit code 1

That indicates it found mysqldump fine, but something else went wrong.

Create a my.cnf file at E:\User Data\Michael\Projects\My-Project\ and put this in it (swapping out your MySQL creds and connection info):

[client]
user=username
password="password"
host=hostname
port=port

Then execute this from the command line and see if that works:

mysqldump --defaults-extra-file="E:\User Data\Michael\Projects\My-Project\my.cnf" --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --set-charset --triggers --single-transaction --no-data --result-file="E:\User Data\Michael\Projects\My-Project\storage\backups/israel-allies-foundation_190710_133056_lfngdk0efw_v3.1.34.sql" iaf_craft && mysqldump --defaults-extra-file="E:\User Data\Michael\Projects\My-Project\my.cnf" --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --set-charset --triggers --no-create-info --ignore-table=iaf_craft.assetindexdata --ignore-table=iaf_craft.assettransformindex --ignore-table=iaf_craft.sessions --ignore-table=iaf_craft.templatecaches --ignore-table=iaf_craft.templatecachequeries --ignore-table=iaf_craft.templatecacheelements --ignore-table=iaf_craft.cache --ignore-table=iaf_craft.templatecachecriteria iaf_craft >> "E:\User Data\Michael\Projects\My-Project\storage\backups/israel-allies-foundation_190710_133056_lfngdk0efw_v3.1.34.sql"

[proimage]

That works, but then again, the built-in backups somehow started working as well. :-|

Incidentally, the && command appending string works in Windows' cmd, but not in Powershell, where they now use a semicolon instead: ; . Because reasons. ¯\_(ツ)_/¯

EDIT: I just tried updating to 3.2 again now, and once again it wasn't able to backup the database. It asked me if I wanted to continue anyway or revert the backup. I chose to revert, after which the Utilities -> Database Backup tool began failing again with the same exit code 1 in the logs.

EDIT 2: A few interesting findings:

1. If the backup process is failing, restarting Apache fixes the issue.
2. The issue seems to get triggered by something during the update prep process. In other words, if Apache is restarted and manual backups are working, they won't suddenly stop working until I try to perform a system update. During the system update, it takes about 40 seconds on "Updating composer dependencies", then goes to "Checking server requirements" for about one second, and then on to the "Backing up database" stage, which fails almost instantly. I then revert the update, with takes another 40 seconds or so and completes successfully. From that point onwards, even manual backups will be broken until I restart Apache.
3. I have multiple Craft installs running locally for different dev sites; when backups get broken by the update process in one of them, they all seem to be broken.
4. From the instant I start an update process that breaks the backup function, backups on other local sites stop working. So it's something in those first commands that run at the start of the "Updating composer dependencies" stage that breaks things, not something further on, or in the update reversion process, or whatever.
5. To be clear: the command-line backup command you provided, based on the manually-created my.cnf, always works, even when the built-in Craft backups no longer work.

[proimage]

@angrybrad Beep! :)

[angrybrad]

@proimage yeah... I'm out of ideas. I installed the latest XAMPP on Windows 10 and I'm not able to reproduce that behavior. It's got to be something environmental?

[proimage]

Hmm, ok. Well, since I can reliably "fix" things by restarting Apache (which takes seconds), it's not the end of the world. :) Thanks anyway!

[rtheunissen]

brew link [email protected] --force helped me on OS X

[kinw3b]

Followed these instructions precisely to try to fix the problem both with backup and feedme: https://craftcms.com/guides/database-backups-in-craft-3-with-mamp

But i still get an error in logs. Anybody else solve this issue? Really need some help.

[brandonkelly]

@kinw3b Can you email your logs over to [email protected] ?