search

craftcms / cms

Build bespoke content experiences with Craft.
https://craftcms.com
Other
3.29k stars 638 forks source link

[4.x]: DB export/import error #14648

Closed Michael-Paragonn closed 7 months ago

Michael-Paragonn commented 8 months ago

What happened?

Description

After a server change, I can no longer import the DB dumps generated by Craft 4 on the new server into my local DDEV instance. I reported the issue to DDEV here, but the permissions-adjustment fix mentioned no longer works (or perhaps I'm just doing it wrong), and since the workaround my sysadmin has been using is done at the export end (exporting using --set-gtid-purged=OFF), and not the import end, I thought perhaps there's something Craft could be on the lookout for to prevent this from happening.

Here's the error when I try to import using ddev craft db/restore:

michael@Mike-PC:~/projects/myProject/htdocs$ ddev craft db/restore ./myProject--2024-03-22-073722--v4.8.0.sql 
Restoring database backup ... error: The shell command "mysql --defaults-file="/tmp/uodulpokkula.cnf" db < "./myProject--2024-03-22-073722--v4.8.0.sql"" failed with exit code 1: ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED
Failed to run craft db/restore ./myProject--2024-03-22-073722--v4.8.0.sql: exit status 1

And here's the error when I try to import using DDEV's ddev import-db:

michael@Mike-PC:~/projects/myProject/htdocs$ ddev import-db < ./myProject--2024-03-22-073722--v4.8.0.sql 
mysql: [Warning] Using a password on the command line interface can be insecure. 
ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED 
Failed to import database db for myproject: failed to import database: ComposeCmd failed to run 'COMPOSE_PROJECT_NAME=ddev-myproject docker-compose -f /home/michael/projects/myProject/htdocs/.ddev/.ddev-docker-compose-full.yaml exec -T db bash -c set -eu -o pipefail && mysql -uroot -proot -e "DROP DATABASE IF EXISTS db; CREATE DATABASE IF NOT EXISTS db; GRANT ALL ON db.* TO 'db'@'%';" && perl -p -e 's/^(CREATE DATABASE \/\*|USE `)[^;]*;//' | mysql db', action='[exec -T db bash -c set -eu -o pipefail && mysql -uroot -proot -e "DROP DATABASE IF EXISTS db; CREATE DATABASE IF NOT EXISTS db; GRANT ALL ON db.* TO 'db'@'%';" && perl -p -e 's/^(CREATE DATABASE \/\*|USE `)[^;]*;//' | mysql db]', err='exit status 1', stdout='', stderr='mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED'
stdout: 
stderr: mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

Steps to reproduce

  1. Export a database from Craft on a server that causes this issue. (🤷)
  2. Try to import the database into a local DDEV instance.

Expected behavior

The database imports without problems.

Actual behavior

The database doesn't import because of some GTID-related problem with the export.

Craft CMS version

4.8.0

PHP version

8.1

Operating system and version

Windows 11 + WSL2 + DDEV

Database type and version

MySQL 8.0

Image driver and version

No response

Installed plugins and versions

angrybrad commented 8 months ago

After a server change,

I feel like that’s doing a lot of lifting.

Can you describe the situation in which the dump was generated?

i.e., is it “true” Oracle MySQL or MariaDB or an AWS Aurora flavor of MySQL? In any of those cases, the specific versions that are being reported would be useful.

Michael-Paragonn commented 8 months ago

I feel like that’s doing a lot of lifting.

Indeed; that's obviously the culprit, but the question is, why? :)

This is what the Utilities -> System Report page says:

PHP Info reports this:

mysqli

mysqlnd

I also have SSH access to the server if you need to see the output of some command...?

If you need more than that, I can get my sysadmin to chime in here.

angrybrad commented 8 months ago

So:

If all of that is correct, can you share the database portion of your local .ddev/config.yaml file?

Michael-Paragonn commented 8 months ago

Yes, those are both true (and I generally use the DB backup util in the CP, though using the CLI makes no difference to the outcome).

My local .ddev/config.yaml's database portion is:

...
database:
  type: mysql
  version: "8.0"
...
angrybrad commented 8 months ago

@Michael-Paragonn thanks for that. To confirm, passing --set-gtid-purged=OFF into mysqldump works for you?

Michael-Paragonn commented 8 months ago

To confirm, passing --set-gtid-purged=OFF into mysqldump works for you?

Yes, when exporting from the remote server, the sysadmin said passing that to his usual mysqldump command resolves the issue when importing.

Michael-Paragonn commented 8 months ago

My sysadmin had the idea of adding an "Extra Parameters" field to the DB Backup functionality, where we could add parameters to the backup command used by Craft.

angrybrad commented 7 months ago

Once https://github.com/craftcms/cms/pull/14586 is merged, you’ll be able to add this to your config/general.php file and achieve what you're looking for.

<?php
use craft\config\GeneralConfig;
use mikehaertl\shellcommand\Command as ShellCommand;

return GeneralConfig::create()
    ->backupCommand([
        'callback' => fn(ShellCommand $command) => $command->addArg('--set-gtid-purged=OFF'),
    ]);

I looked to see if there was a way we could "auto-detect" when that was available across the MariaDB and MySQL versions we support, along with the different versions of the mysqldump client tools that works across Windows/macOS/*nix, and it got very complicated, very fast.

Michael-Paragonn commented 7 months ago

That looks promising, thanks! Can that be used in an environment-specific manner (.env vars or something)? Or is that not advisable for MySQL queries?

angrybrad commented 7 months ago

Yup - it will work with the same multi-environment configs that Craft uses: https://craftcms.com/docs/4.x/config/#multi-environment-configs

brandonkelly commented 7 months ago

We took things in a slightly different direction (#14897).

Here’s what you can do with Craft 4.9 and 5.1+:

<?php
use craft\config\GeneralConfig;
use mikehaertl\shellcommand\Command as ShellCommand;

return GeneralConfig::create()
    ->backupCommand(fn(ShellCommand $command) => $command->addArg('--set-gtid-purged=OFF'))
;