SOLVED [CPANEL-33473] Mysql dump process privilege error after 5.7.31 update

[SJR]

We use mysql 5.7.31.
I set up a cron job to run a perl script to do a mysqldump for my database every couple of hours as a backup.
After a mysql update this morning, I now get an error message each time the cron runs:

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

However, the database dump / backup gets completed as expected.

Any help on this error message?

Thanks.

 

[SJR]

As a further note -
In cPanel > MySQL Databases > Privileged Users column - The user for the database has 'All Privileges' checked and given to the user.

 

[Reiner030]

Hello,
I have same problem since yesterday that mysqldump throws this error but with creating file as usual.
When checking again today I found out that mysqldump wants to dump tablespaces (as error message writes) so it's possible to let mysqldump skip this task by running it with parameter

--no-tablespaces

 

[cPanelLauren]

Can you tell me which version of MySQL you're running exactly?

mysql -v

 

[SJR]

Lauren,
As far as I can tell it is 5.7.31

When I type mysql -v in the command line I get:
root [/]# mysql -v
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root [/]# _

 

[billbordallo]

Hi,
I am getting the same error here. I run a cron everyday and the error has started just 2 days ago.

The command inside the cron (which is a shell script) is:

mysqldump -u$DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/$DB_FILE

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

MySQL version is 5.7.31

I think this is related to the update of MySQL's version. Backups that run over 5.7.30 does not shows the error.

Does anyone knows why this is happening and how to fix it?

Thank you!

 

[SJR]

Update -
I have added the parameter --no-tablespaces as suggested above by Reiner030 to my script and the database dump works as it should with no error messages.
The mysqldump appears to be working exactly as it has in the past. The current mysqldump file is relatively the same size as before, and it contains all the same beginning and ending statements, so I assume it is complete.
This latest update of mysql has somehow changed the way the privileges are managed between the database and the tablespaces.
Being a novice, I have no understanding of tablespaces, when they are used, or their purpose.
My assumption is, I either don't use tablespaces in my database, or I don't need to include them in the dump.
More information on this from experts would be useful.

 

[cPanelLauren]

It looks like this issue is actually related to a case CPANEL-33473

It's also indeed a result of a recent MySQL update which is also present i MySQL 8:

Security Notes

Incompatible Change: Access to the INFORMATION_SCHEMA.FILES table now requires the PROCESS privilege.

This change affects users of the mysqldump command, which accesses tablespace information in the FILES table, and thus now requires the PROCESS privilege as well. Users who do not need to dump tablespace information can work around this requirement by invoking mysqldump with the --no-tablespaces option. (Bug #30350829)

It's also been found to cause the issue noted in this support article: https://support.cpanel.net/hc/en-us/articles/360051023554-Access-denied-to-wp-db-export-command

I don't have any further information on the case right now but I have added this thread to it. I"ll update here when I have more information.

 

[sa3dy]

Today, I faced the same issue even with root.

Any a workaround parameter without --no-tablespaces ?

 

[philryan74]

From MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.31 (2020-07-13, General Availability)

• Incompatible Change: Access to the INFORMATION_SCHEMA.FILES table now requires the PROCESS privilege.
  This change affects users of the mysqldump command, which accesses tablespace information in the FILES table, and thus now requires the PROCESS privilege as well. Users who do not need to dump tablespace information can work around this requirement by invoking mysqldump with the --no-tablespaces option.
• 
  
• (Bug #30350829)

And this annoys the hell out of me. I never asked for the 5.7.31 update, and particularly when it breaks all my CRON jobs.

 

[cPanelLauren]

I think it's frustrating for people in general as the general consensus is that minor revision updates should not impose huge changes and be safe to move to automatically.
Further to that, the case associated with this has been closed as it was a MySQL change and not something managed or controlled by cPanel.

The way to do this moving forward is to invoke mysqldump with the --no-tablespaces option