Resolved View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

[samson]

Plesk Obsidian
Version 18.0.40

Plesk was unable to update mariadb from 10.3 to 10.4, so I manually upgraded. The mariadb upgrade deleted some of plesks extensions.
Reinstalled plesk sucessfully, however, since mariadb 10.4, they have changed the table mysql.user to mysql.global_priv and now user is only a view.
Plesk will not install any apps with the following error.

ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them​

Tried adding root@localhost as a user and added global privileges
done plesk repair all

 

[IgorG]

Looks like database is corrupted. Try the following workaround:

1. Insert missing privileges:

INSERT INTO `tables_priv` (`Host`, `Db`, `User`, `Table_name`, `Grantor`, `Timestamp`, `Table_priv`, `Column_priv`) VALUES ('localhost','mysql','mariadb.sys','global_priv','root@localhost','0000-00-00 00:00:00','Select,Delete','');

2. Restart MariaDB to apply security changes:

# servier mariadb restart

3. Verify that select works now:

# plesk db

use mysql;

select * from user;

 

[samson]

here's the error after applying insert query

ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column `mysql`.`tables_priv`.`Timestamp` at row 1

 

[Bitpalast]

It think you might have a strict mode setting in /etc/my.cnf that shouldn't be there. Try to edit /etc/my.cnf and replace whatever the "sql_mode" entry is with an empty value like

sql_mode= ''

then restart MariaDB.
# service mariadb restart

Further, after upgrading MariaDB did you run
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin
to upgrade the MySQL scheme? If not, run it. There is no problem to run it again (if you might have done so earlier), it won't cause any damage.

For Plesk also make sure to run
# plesk sbin packagemng -sdf
to tell Plesk that you have a new version of something.

And finally, if the database itself starts up without problems, run
# plesk repair db -y
because after some upgrades of the database, Plesk needs to update some "datetime" settings, because between older and newer MariaDB versions some aspects of that has changed.

 

[samson]

That's fixed the issue.
Many thanks to Igor and Peter
Cheers
Simon

 

[waseem]

I am getting following
Dump all databases
Trying to Dump databases... mysqldump: Got error: 1356: "View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" when using LOCK TABLES
mysqldump: Got error: 1356: "View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" when using LOCK TABLES

the difference is i am getting View 'sys.host_summary instead of View 'mysql.user.
I checked all databses no error detected by check n repair tool, Also run above mentioned command, nothing works

 

[waseem]

"Drop database sys" worked for me