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

[patrickkasie]

Dear forum,

I'm aware that this is probably in the wrong section and should be among bugs, but I've got a problem and no good question to ask, so I'll explain the problem in short. I believe this could be a bug. Either with DA or with AlmaLinux, I hope someone with a deeper knowledge of either can tell me here.

When suspending, unsuspending a user or attempting to create/edit databases within the user, we're getting the following error:

Unable to set password for db useramas_db : View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Unable to set password for db useramas : View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Full situation below.

We've got 2 VPS'es right now. An old one that has CentOS 8, and a new one that has AlmaLinux 8.5.
After completely installing AlmaLinux, I've moved a user from the old VPS to the new VPS.
When trying to unsuspend the user, we got the aforementioned error.
When Googling for the error, the following suggestions showed up for me:

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

I have tried query but there is an error. Does anybody solved the error? MariaDB [mysql]> UPDATE user SET Host='%' WHERE User='root'; ERROR 1356 (HY000): View 'mysql.user' references invalid t...

stackoverflow.com

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 - Blog for Database and System Administrators

Starting with the MariaDB-10.4+ the mysql.user is a view rather than a table. Read the post for to resolve the Error 1356.

dbsysupgrade.com

I do not have enough knowledge to take out the neccessary information from the above sources, but what I do understand is that I have to set the MariaDB version to 10.3 in order to get it to work again.
However, the old VPS is using MariaDB 10.4, and the new one is using 10.6. So this should not be a problem. So I had decided to set the MDB versions to the same version: 10.4
After setting the new VPS' MDB to 10.4, it spewed out the following information:

Now I am not completely unable to make changes to a user, but the MDB service is down. It's now on the same version as the old server is. I need help information because I do not have enough knowledge on what is happening here, so I've reverted the version from 10.4 back to 10.6. This went without any problems whatsoever.

With no change at all, I could update the user (suspend, unsuspend, create and edit a db user). I did have to delete the existing user and restore it before the whole thing started to work again. I should have probably made a screenshot of this error too, as I could not access the domain userdomain.vps11.dhost.nl (DNS zone A record from [userdomain.vps11.dhost.nl -> 93.119.13.214] (vps14.dhost.nl) to make this userdomain visible while using the old link, but this is completely unrelated to the problem at hand)

I'm aware that everything I've just explained looks like a mess, but these are all the steps that I have executed in order to make the best out of the whole thing. Hit me up with any questions you may have.

 

[ikkeben]

The main problem after you did "downgrade" ? Is it downgrade ? , is that downgrade is not supported!

Doesn't help you but F.Y.I.

You can try new version and then look in changelogs and error log files which default settings changed or depricated failed, then in the mysql config files put those settings ,, as for example ( "strict" is default on in newer versions)

Only one example i can't help with MYSQL mariadb only pointing out possible direction to look for.

EDIT:
Also a ticket at DA supporrt while could be BUG:

When suspending, unsuspending a user or attempting to create/edit databases within the user, we're getting the following error:

EDIT: Also post for support versions of DA, Custombuild, and if used Custom Build GUI plugin.

 

[patrickkasie]

The problem is that the user I tried to edit did not accept any database edits whatsoever. This is why I've tried downgrading (which didn't work). So I've effectively done a re-install of 10.6, which is when db changes started working.

 

[ikkeben]

The problem is that the user I tried to edit did not accept any database edits whatsoever. This is why I've tried downgrading (which didn't work). So I've effectively done a re-install of 10.6, which is when db changes started working.

See my edited above for yes no ticket or bug..?.

Downgrade you can forget at all, is not supported and only BIG problems.

Only that one user?
Then support need i think some history for that user to , so in which version suspended // and ....

 

[patrickkasie]

Yeah, I was replying to your unedited message. Either way, a complete re-install of the 10.6 version is what solved the problem. But thank you for pointing out the downgrade issue, that's useful in the future for me! And yes, besides this, it feels like this suffices as a bug, but idk why this has only occured to me and not others afaik

 

[ikkeben]

And yes, besides this, it feels like this suffices as a bug, but idk why this has only occured to me and not others afaik

First check ( if only that one user has this problem) it could be not only you, only you are the first that see / have this problem in this combination.

I think could be handy to notice DA support for this matter.

 

[smtalk]

Are you sure directadmin.conf has mysql_detect_correct_methods=1 set?

 

[patrickkasie]

Are you sure directadmin.conf has mysql_detect_correct_methods=1 set?

It seems that it is set to true. However, the file seems to have been edited at 11:40 AM my time, which is 5 minutes AFTER BEFORE I had done the re-install of the MDB 10.6, so I cannot confirm or disprove if this flag was set to 1 prior to now

Edit: I understand times are not meaningful, just the order in which the steps were executed in.

 

[patrickkasie]

Additional info: it seems that the update of DirectAdmin from 1.63.2 to 1.63.3 at that time has edited the file. I did the update because it was not working. I updated BEFORE the re-install of 10.6, it was not working then too