"--no-tablespaces" dont work with wp db export

[RoundAboutWEB]

The parameter "--no-tablespaces" causes the error "unknown variable 'tablespaces='" although the parameter works with a mysql-client. The parameter "--compress" on the other hand works without problems. The parameter "--no-tablespaces" is required from mysql 8.0 to be able to create a dump without error message.

without "--no-tablespaces":

wp db export --compress "/html/wordpress/database.sql"
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

with "--no-tablespaces":

wp db export --no-tablespaces --compress "/html/wordpress/database.sql"
mysqldump: [ERROR] unknown variable 'tablespaces='

How to fix this bug?

[wojsmol]

Hi @RoundAboutWEB --no-tablespaces is added by default in WP-CLI 2.5.0. You can update by running wp cli update.

[RoundAboutWEB]

Thanks for fast feedback, but the bug is with version 2.5.0:

> wp cli version
> WP-CLI 2.5.0
>
> wp db export --no-tablespaces --compress "/files/database-20210617.sql"
> mysqldump: [ERROR] unknown variable 'tablespaces='

[wojsmol]

Please post output of wp cli info.

[RoundAboutWEB]

> wp cli info
OS: Linux 5.4.0-47-generic #51-Ubuntu SMP Fri Sep 4 19:50:52 UTC 2020 x86_64
Shell:  /bin/bash
PHP binary: /usr/lib/mw/php/7.4/bin/php
PHP version:    7.4.13
php.ini used:   /etc/php/php.ini
MySQL binary:   /usr/local/bin/mysql
MySQL version:  mysql  Ver 14.14 Distrib 5.7.18, for Linux (i686) using  EditLine wrapper
SQL modes:  
WP-CLI root dir:    phar://wp-cli.phar/vendor/wp-cli/wp-cli
WP-CLI vendor dir:  phar://wp-cli.phar/vendor
WP_CLI phar path:   /html/wordpress
WP-CLI packages dir:    /files/swm2/wp-cli/.wp-cli/packages/
WP-CLI global config:   /files/swm2/wp-cli/.wp-cli/config.yml
WP-CLI project config:  
WP-CLI version: 2.5.0

[wojsmol]

@RoundAboutWEB Please skip --no-tablespaces in your command.

[schlessera]

@RoundAboutWEB If you add --debug, WP-CLI will show you the exact DB command it is trying to execute.

[RoundAboutWEB]

@wojsmol Simply leaving it out is unfortunately not a solution. As of mysql 8.0, at least on my hosts, dumps can only be created with "--no-tablespaces". Moreover, you wrote on 17 June that it supposedly works.

@schlessera The only errormessage with "--debug" will be: "mysqldump: unknown variable 'tablespaces='" If I do it with "mysqldump --no-tablespaces --compress -u ..." it wordk fine. So the problem only will be with wp-cli.

[wojsmol]

@RoundAboutWEB Works by adding it internally, so you can skip it on cli.

[schlessera]

The only errormessage with "--debug" will be: "mysqldump: unknown variable 'tablespaces='"

It's not about the error message, it's about checking the exact command that is to be generated, which should contain --no-tablespaces automatically, but apparently doesn't.

[schlessera]

Ah, I see. You're still adding --no-tablespaces to the WP-CLI command. Leave that one out, WP-CLI adds it automatically since v2.5.0. Try running your export command normally with v2.5.0 (without caring about tablespaces at all), and it should just work, without showing a permission error.

It looks like this is not a bug, but rather a support ticket. If you need more assistance still, please join us in the #cli channel on the Make WordPress Slack Team instead.