Include --no-tablespaces option by default in db-export

[yorkshire-pudding]

--no-tablespaces is needed in mysqldump for versions of MySQL from 5.7.31 and 8.0.21 unless the user (which will be the backdrop database user) has the PROCESS privilege, which is not available on cPanel based hosting and possibly others.

Therefore, we should include this by default within the db-export command.
It is supported by MariaDB from version 10.2.

We could provide an option to not include this option this if there was a need for it.

(Note, we currently have an option to add this option in via the extra option:

bee db-export --extra="--no-tablespaces" ../db_export.sql

Any thoughts @BWPanda @indigoxela @ElusiveMind @quicksketch @jenlampton @herbdool @klonos
Tagging a number of people who have engaged with the issue queue lately

[herbdool]

I'm not familiar with this at all.

[quicksketch]

I'm not familiar with this problem, but I found this helpful article that summarizes the situation: https://anothercoffee.net/how-to-fix-the-mysqldump-access-denied-process-privilege-error/

[yorkshire-pudding]

@quicksketch - yes - that's the one I've been looking at. I can't think of any reason not to do this.

[quicksketch]

As far as my understanding goes, this seems like a safe thing to do as the default. Could we come up with a way to override it, in the event that tablespaces are needed for some situations? Maybe we could do something where if any --extra="" options are passed in at all, none of the default options are automatically applied?

[yorkshire-pudding]

Further checking in the article does suggest that as PROCESS is a server administration privilege, it would not be normal practice to give this to the Backdrop database user, so I do think this is the right default, as hosts using MySQL update to versions where this is required.

According to the MySQL documentation, the PROCESS privilege controls access to information about statements being executed by sessions.

It is a server administration privilege and should not be given to all users. This is because it may show text from currently executing queries. Any user with the PROCESS privilege may therefore see queries issued by others. Here’s the danger: these queries, such as UPDATE user SET password=PASSWORD, may show secrets.

For more information, see General Security Issues and the MySQL Access Privilege System from the O’Reilly MySQL Reference Manual.

I can't find any information that suggests Mariadb needs this privilege (or if so, from what version) and I never encountered it before my host moved to MySQL. Looking in lando for mariadb (10.3 - default), backdrop user is not given Process_priv

[yorkshire-pudding]

PR added if anyone wants to test. If not, I'll merge.

[indigoxela]

Sorry, not a big help here, as I have MariaDB in all testing environments. The only thing I can test: things still work fine with MariaDB (but they also do without patch).

[yorkshire-pudding]

Not breaking MariaDb is a good result!