search

drush-ops / drush

Drush is a command-line shell and scripting interface for Drupal, a veritable Swiss Army knife designed to make life easier for those who spend their working hours hacking away at the command prompt.
https://www.drush.org
2.34k stars 1.08k forks source link

Can't sql:dump with MySQL 5.7.41 #5368

Open joelstein opened 1 year ago

joelstein commented 1 year ago

Running sql:dump with MySQL 5.7.41 produces the following error:

drush sql:dump --result-file=/tmp/backup.sql

mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

I ran the same command on another server, where the only difference is MySQL 5.7.40, and it worked as expected.

Craft CMS fixed what appears to be the same issue by excluding the --single-transaction option for MySQL 5.7.41 and 8.0.32.

https://github.com/craftcms/cms/issues/12557 https://github.com/craftcms/cms/pull/12560

System Configuration

Q A
Drush version? 11.4.0
Drupal version? 9.5.2
PHP version 8.1.9
OS? Ubuntu 18.04.6 LTS
joelstein commented 1 year ago

It works when running as the root user, even with the --single-transaction option:

mysqldump example --no-autocommit --single-transaction --opt --quote-names > /tmp/backup.sql

But it fails when running as my specific database user:

mysqldump example -u example -p --no-autocommit --single-transaction --opt --quote-names > /tmp/backup.sql

My "example" user has the following grants:

GRANT USAGE ON . TO 'example'@'localhost' GRANT ALL PRIVILEGES ON example.* TO 'example'@'localhost'

mrsimonbennett commented 1 year ago

@joelstein Did you flush privileges after you run your grant commands?

You can limit it to

GRANT RELOAD,PROCESS ON *.* TO 'backups'@'%';
FLUSH PRIVILEGES;

It's also worth checking when you connect to the database are you doing it over localhost? Have you tried doing it at @'%'

We had to write a guide to help our customers after the patch update to mysqldump 🤷‍♂️

joelstein commented 1 year ago

This fixed the issue for me:

GRANT RELOAD ON *.* TO 'example'@'localhost';
FLUSH PRIVILEGES;

I didn't need the PROCESS grant.

Thank you!

adolfoasp commented 1 year ago

Seems to be related to the following bug: https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/2003866 Mysql versions 5.7.41 and 8.0.32 are affected.

By default, drush sql-dump uses the flag: --single-transaction internally when it launches the mysqldump command. The main problem for us is that we are using AWS MariaDB RDS as a database. As described in the following link: https://aws.amazon.com/es/premiumsupport/knowledge-center/mysqldump-error-rds-mysql-mariadb/ even using the master user of this MariaDB RDS we have no privileges to launch 'FLUSH TABLES'

So, we hope mysql-client will be fixed with a new Mysql patch version or drush deletes "--single-transaction" as a default flag used by mysqldump command.

alexxed commented 1 year ago

drush9 sql-dump --extra-dump="--single-transaction=false --no-tablespaces" > /tmp/db-backup works on RDS until there's a better way;

--single-transaction=false makes the FLUSH call go away (beware though it can produce inconsistency with innodb) --no-tablespaces makes the warning about PROCESS go away

fabrer commented 1 year ago

In a BLT context using both sql-dump and sql-query, the best solution should be: Add the following lines to your drush.yml file. (local and remote).

command: sql: dump: options: extra-dump: '--no-tablespaces --single-transaction=false --set-gtid-purged=OFF'

Regards,

esolitos commented 1 year ago

‼️ To everyone suggesting --single-transaction=false: be aware that this can lead to inconsistent dumps!

This can be ok as temporary workaround, but can bite you back if you end up with non reliable backups!

joelpittet commented 1 year ago

Sharing the sentiment "I hope this change get's reverted, adding global perms to many isolated server users does not seem like a good work-around."

jonpugh commented 1 year ago

This is wild, MySQL. Just wild.

Since 2011, Aegir/provision has used "GRANT ALL PRIVILEGES" to create a new user for each DB. I was curious so I looked it up: https://git.drupalcode.org/project/provision/-/commit/91b377031974bd4962c7460f08453daee49bca3c#392fe358ab66f7e176afab943011e18a38a74fae_0_57

So why do my backups and syncs fail?

Well... wait for it.

From my testing, GRANT ALL PRIVILEGES does NOT include RELOAD.

So maybe GRANT ALL,RELOAD? Nope.

I have to add a second GRANT to get this to work:

$statement = "GRANT ALL PRIVILEGES ON `%s`.* TO `%s`@`%s`";
$statement .= "; GRANT RELOAD ON `%s`.* TO `%s`@`%s`";

So there's that.

joelpittet commented 1 year ago

@jonpugh They apparently fixed it in upcoming MySQL Server 5.7.42 / 8.0.33 releases. See the latest comment on the bug report https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/2003866

Revert back until that release is my suggestion (hopefully you can)