Question Tips for resolving failure to dump SQL for Plesk Backup ("Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = ..." )

[MHC_1]

Server operating system version

AlmaLinux 9

Plesk version and microupdate number

18.0.73

OS: Alma Linux 9.7
MariaDB: 10.11.15
Plesk: 18.0.73 update 5

Plesk backup runs but creates an error for every DB:

Warning: Database "dbase_name"
Unable to make database dump. Error: Failed to exec mariadb-dump: Exit code: 2: mariadb-dump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'dbase_name'': Cannot load from mysql.proc. The table is probably corrupted (1728)

But the database and the tables all work ok and no other indicator anything is corrupted.

This Q&A here: Resolved - Plesk backup can not Dump databases MariaDB 10.6.7 references checking the "bind_address" in "/etc/my.cnf"

[mysqld]
bind-address=127.0.0.1

This above should not need to be changed for local backups.

There is no "[mysqldump]" elements in the "my.cnf" file. There is another post here: Issue - Database backup doesn't work - Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 2 - MariaDB 10.6 that has similar but non-identical issue with suggested resolution of :

the issue is caused by name resolution in mariadb as [email protected] and admin@localhost are two different users

However,
I suspect the user who runs the Backup doesn't have the SHOW FUNCTION privilege. How can we add the SHOW FUNCTION privilege to the (assumed) "Admin" user?

This issue has only occurred since updating MariaDB to 10.11.15 (It was 10.5 I think before)

NOTES:

-- Error occurs only on backup processing and across all databases.
-- My.cnf has no "[mysqldump]" elements (including child .cnf files)
-- Currently "bind-address=127.0.0.1"
-- All Databases appear to be working normally.


Questions

1) Is Backup run by the "Admin" MariaDb user. If not, which user carries out the SQL backup process?

2) How can we check/set the user in Q1 has the "SHOW FUNCTION" privilege?

3) Do we need to distinguish [email protected] and admin@localhost, eg set bind address to something different or otherwise tweak a setting?

4) What else can be explored to make DB backups work?

5) Would "bind-address = 127.0.0.1,::1,localhost" work to permit admin@ both / all local sources access?

 

[MHC_1]

Ignore that last Q5 that's wrong.

 

[Bitpalast]

Admin privileges are not required for "show function". I suggest to open phpMyAdmin and run a test:
SHOW FUNCTION STATUS WHERE Db = 'dbase_name';
in the SQL dialog box. Maybe the system is right that mysql.proc has a problem?
Was MariaDB updated lately? Was mysql_upgrade --force run afterwards to make sure that the system tables have a format that perfectly matches the version of the server?

 

[MHC_1]

@Bitpalast oooh, I don't think, mysql_upgrade --force was run. I will check the PhpMyAdmin routine and then give this (--force) a try. Thank you.

 

[MHC_1]

@Bitpalast update: We can not run mysql_upgrade because there is no root user. There is a mariadb.sys user and an admin user but we do not know these user's passwords.

 

[MHC_1]

@Bitpalast

Ok Update again: Root user has been generated; and mysql_upgrade --force has been run. All tables show "OK" . Re running the SHOW FUNCTION STATUS WHERE Db = 'dbase_name'; in PhpMyAdmin returns a non-error result (blank but not the error it had before.

This should fix the issue. Thank you.

 

[MHC_1]

Can this be marked as "Resolved" with BitPalasters answer. Thanks.