sql:dump fails with mysql 5.7.31+ and mysql 8.0.21+

[ericbarrette]

Describe the bug When upgrading to MySQL 8.0.21 sql-dump produce the following errors.

> mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

To Reproduce Update MySQL to 8.0.21 and run drush sql-drop --result-file=<filename>

Expected behavior No errors.

Actual behavior The backup is still produced, but this error is showing up.

Workaround The backup is still produced.

System Configuration

Q	A
Drush version?	10.3.1
Drupal version?	8.9.2
PHP version	7.4.8
OS?	Linux / Docker

Additional information Is it possible to remove the error by adding --no-tablespaces to the mysqldump call if we don't need it.

Apparently this is expected since 8.0.21 https://bugs.mysql.com/bug.php?id=100219 (see developer comment)

[xmacinfo]

I confirm having this error while using drush sql:dump --result-file=<filename>; the backup is still produced.

[piridium]

I confirm this error, too, while using drush sql-dump > db_test.sql.

• MySQL 5.7.31
• Drush 8.3.5 and 10.3.2
• PHP 7.3.21

[weitzman]

If --no-tablespaces is not supported on all MySQL and MariaDB versions, we cant add it. You can solve this by passing the drush sql:dump --extra-dump=--no-tablespaces. Or add config similar to https://github.com/drush-ops/drush/issues/4410#issuecomment-658784698

[johnbburg]

Can this be used with sql:sync?

[AdamPS]

Can this be used with sql:sync?

I cannot see a way to do that - there doesn't seem to be the same option.

[AdamPS]

Can this be used with sql:sync?

On the command line, no. However sql:sync calls sql:dump so if you add config in drushrc.php to fix the problem for sync you will also fix it for dump.

NB there is a typo in the earlier answer from @weitzman - the option is named --no-tablespaces with only one s in the middle.

[RalfEisler]

drush sql:dump --extra-dump=--no-tablespaces dumps the database in the terminal but does not save it on disk.

[mataski]

If you do drush help sql-dump it shows it's --extra Add custom options to the dump command.

So --extra=--no-tablespaces works for me. Not --extra-dump=

edit: For Drush 8.* for Drupal 7

[RalfEisler]

I forgot > mydatabase.sql. So drush sql:dump --extra-dump=--no-tablespaces > mydatabase.sql works.

[weitzman]

@RalfEisler --extra option would be appropriate for drush 8 and earlier.

[AdamPS]

In my experience the --extra option does not work well in drushrc.php. It means that other commands such as sql:sync fail because mysql --no-tablespaces generates an error.

[greg-1-anderson]

You can also use command-specific opitons in drushrc.php to set extra for sql:dump, but not for other commands.

[AdamPS]

command-specific opitons in drushrc.php

Thanks Greg. I checked and I had already done exactly what you suggest. The problem comes when I call drush sql-dump --skip-tables-key which causes a call to $this->listTables(); and hence a call to mysql --no-tablespaces. This presumably explains why the --extra-dump option was introduced in Drush 9.

So drush 8 users beware: if you add the drushrc.php setting then all the options relating to tables are broken.

[greg-1-anderson]

Not sure I follow the code path / option passing path you're describing, but if --extra-dump still has a distinct and useful use-case, I'd accept a PR backporting it to Drush 8.

[AdamPS]

Thanks but unfortunately personally I don't have time for a PR right now.

I think it is like this SqlBase::dump calls ::get_expanded_table_selection calls listTables() calls $this->query('SHOW TABLES;');

[njt1982]

I think adding the --extra params seems to break the common table data exclusions... I usually have my dump setup to exclude data from cache, sessions, watchdog, etc... This means the gzip'd SQL is <1Mb for my site.

Currently my choices are:

1. Error
2. SQL dump with all data 😉

USER@SERVER:/var/www/html/REPO_ROOT/web$ ../vendor/bin/drush --uri=EXAMPLE.COM  sql:dump --gzip --result-file=~/test.sql.gz && ls -lh ~/test.sql.gz.gz
> mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
> mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
 [success] Database dump saved to /home/USER/test.sql.gz.gz
-rw-rw-r-- 1 USER USER 894K Feb  7 14:27 /home/USER/test.sql.gz.gz
USER@SERVER:/var/www/html/REPO_ROOT/web$ ../vendor/bin/drush --uri=EXAMPLE.COM  sql:dump --gzip --extra=--no-tablespaces --extra-dump=--no-tablespaces --result-file=~/test.sql.gz && ls -lh ~/test.sql.gz.gz
 [success] Database dump saved to /home/USER/test.sql.gz.gz
-rw-rw-r-- 1 USER USER 7.8M Feb  7 14:27 /home/USER/test.sql.gz.gz

(I am aware I didn't need to add .gz to the result filename)

When I zless on the test file, the cache tables have data in the 2nd one and not the first.

[siliconmeadow]

What is the significance of dumping the tablespaces when it comes to the average Drupal sysadmin?

[darthsteven]

Another workaround for Drush 8 is to use one of the MySQL defaults files (e.g. /etc/my.cnf) and add a section like this:

[mysqldump]
no-tablespaces

And then when Drush calls mysqldump, the no-tablespaces option gets added on automatically. Obviously this changes all invocations of mysqldump though, not just Drush ones, so be sure you don't want to dump tablespaces!

[alex-moreno]

really good point from @siliconmeadow (aka Dave), see https://drupal.stackexchange.com/questions/300321/what-is-the-significance-of-dumping-the-tablespaces

[rfay]

@ericbarrette or admin, could you please retitle this issue to something like "sql:dump fails with mysql 5.7.31+ and mysql 8.0.21+" ?

[weitzman]

title edited ... If there is a simple way to determine if this is --no-tablespaces is needed then perhaps we could handle this in core sql:dump

[bradbulger]

I tried the mysql.cnf workaround, and I can see that 'no-tablespaces' is TRUE, but I still get the error about no PROCESS privilege.

Adding, this seems to be the known issue of using --defaults-file instead of --defaults-extra-file

[krauthosting]

@weitzman Determination is version check on minor release plus if user has PROCESS privilege. https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-31.html#mysqld-5-7-31-security https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_process

Unfortunately Oracle's internal Bug #30350829 on this security update isn't accessible. But given communication on this breaking change we don't assume any upstream efforts. It's supposed to be mitigated in Ops via mysql.cnf or better via !include and !includedir.

For drush a sandwhich-version-check and user privilege check are usually possible env wise. Not the nicest and simplest way but so far seems the only mitigation against this breakage. We tackle it with Ops in our hostings but also fallback on --extra-dump for CI and for devs.

[marcvangend]

I was not able to get this working with a remote site alias and Drush 10.5.0. The following command results in an empty file for me:

drush @testsite sql-dump --extra-dump=--no-tablespaces --result-file=./test.sql

Note that the order of options seems to be important. The following command did not even result in an empty file:

drush @testsite sql-dump --result-file=./test.sql --extra-dump=--no-tablespaces

Finally, the following command still printed the sql dump code on the terminal instead of writing it to a file:

drush @testsite sql-dump --extra-dump=--no-tablespaces > test.sql

Tips are welcome.

[caschbre]

I think adding the --extra params seems to break the common table data exclusions... I usually have my dump setup to exclude data from cache, sessions, watchdog, etc... This means the gzip'd SQL is <1Mb for my site.

This was my experience too. I break db dumps into multiple files for various reasons, but had to abandoned that due to the addition of the --extra parameter.

[julien-langlois]

Can you tell me the best way to proceed at this time?

[piridium]

I'm using successfully in my backup script: $drushPath sql-dump --extra-dump=--no-tablespaces > db/$fileName.sql for drush 10 $drushPath sql-dump --extra=--no-tablespaces > db/$fileName.sql for drush 8

[AdamPS]

FYI this error message has just appeared on my system again with MySQL version 8.0.32. I found a related bug report for mysql.

8.0.32 was a security update according to the Ubuntu change log.

[selwynpolit]

I'm also seeing this error when trying to use drush sql-dump on an Acquia site. I've had to revert to using the Acquia CLI. Not sure this is solvable in drush. I guess it will have to be addressed with Acquia. Maybe this will save someone some searching.

My command that I am using is: `drush @zzz.prod sql-dump --extra-dump=--no-tablespaces >dbprod.sql

The error looks like:

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

  In SqlCommands.php line 240:

    Unable to dump database. Rerun with --debug to see any error message.

[gitressa]

I am seeing this running drush archive:dump on Drupal 10.2.1, Drush 12.4.3.0, and PHP 8.2.14 on shared web hosting. but the database file does seem to be included ...:

$ drush archive:dump --exclude-code-paths=web/sites/default --destination=/var/www/example.org/example-archive-2024-1-16.tar.gz
> mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces"