[4.x]: DB export/import error

[Michael-Paragonn]

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

• Architect 4.0.1
• Asset Rev 7.0.0
• Color Mixer 3.0.0
• DigitalOcean Spaces Filesystem 2.0.0
• Feed Me 5.4.0
• Field Manager 3.0.8
• Freeform 4.1.13
• Image Resizer 3.0.11
• Inventory 3.1.1
• Linkit 4.0.4.1
• Logs 4.0.0
• Maps 4.0.4
• Mix 1.6.0
• Postmark 3.0.0
• Redactor 3.0.4
• SEO 4.2.2
• Simple Text 3.0.0
• Super Table 3.0.12
• Table Maker 4.0.7

[angrybrad]

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]

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 version 8.1.2
• OS version Linux 5.15.0-101-generic
• Database driver & version MySQL 8.0.30
• Image driver & version Imagick 3.6.0 (ImageMagick 6.9.11-60)
• Craft edition & version Craft Pro 4.8.0
• Yii version 2.0.48.1
• Twig version v3.8.0
• Guzzle version 7.8.1

PHP Info reports this:

mysqli

• Client API library version mysqlnd 8.1.2-1ubuntu2.14
• Active Persistent Links 0
• Inactive Persistent Links 0
• Active Links 0
• mysqli.allow_local_infile Off
• mysqli.allow_persistent On
• mysqli.default_host no value
• mysqli.default_port 3306
• mysqli.default_pw ••••••••
• mysqli.default_socket /var/run/mysqld/mysqld.sock
• mysqli.default_user no value
• mysqli.local_infile_directory no value
• mysqli.max_links Unlimited
• mysqli.max_persistent Unlimited
• mysqli.reconnect Off
• mysqli.rollback_on_cached_plink Off

mysqlnd

• Version mysqlnd 8.1.2-1ubuntu2.14
• Compression supported
• core SSL supported
• extended SSL supported
• Command buffer size 4096
• Read buffer size 32768
• Read timeout 86400
• Collecting statistics Yes
• Collecting memory statistics No
• Tracing n/a
• Loaded plugins mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_caching_sha2_password,auth_plugin_sha256_password
• API Extensions mysqli,pdo_mysql

---

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]

So:

• The backup was created via Craft 4.8.0 on a remote server either using craft db/backup from the CLI or the database backup utility in the Craft control panel. The remote server database is MySQL 8.0.30.
• Locally, when running ddev craft db/restore (presumably also using Craft 4.8.0), you get the original error?

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

[Michael-Paragonn]

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]

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

[Michael-Paragonn]

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]

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]

Once #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]

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]

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

[brandonkelly]

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'))
;