I just discovered the silly new issue of MariaDB's latest version having mysql.user as a view. All my imported Wordpress databases suddenly cannot connect from the blogs. When I try to even list mysql.user it shows me this:
> select * from mysql.user;
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
What can we do to solve this?
Edit: Found this question, but it does not have a solution, only a suggestion. The ALTER USER command -- where to use and with what settings? Do I have to somehow alter the rights for every Blog database?
Further investigation revealed that the issue described in this Question, and my initial response to it (below) may be related to an Incorrect definition of table mysql.event problem. In my case, I had 1) loaded a full dump (including the mysql database) from MySQL 5.7.33 to a fresh installation of MariaDB10.5.9; 2) discovered that this was not a good idea; 3) edited my dump file to exclude the mysql database, and 4) repeated the load without deleting any databases or configurations.
This caused the database to function properly, but (in addition to the issue described in this Question) a) /usr/sbin/mariadbd --verbose --help would try to run the database server rather than print help, b) on startup the following error always occurred:
Apr 05 08:52:46 xxx mariadbd[22668]: 2021-04-05 8:52:46 0 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_B
Apr 05 08:52:46 xxx mariadbd[22668]: 2021-04-05 8:52:46 0 [ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler
Today, I was able to correct these problems (under Amazon Linux 2) by:
At this point, I not only have clean database startup and proper operation of /usr/sbin/mariadbd --verbose --help, I also find that select * from mysql.user works properly!
So the problem of not being able to select from mysql.user appears not to have resulted from the change of mysql.user from table to view as I had originally thought, but from some other issue related to my "improper" database migration.
(included as a reference only)
After considerable research I have found at least part of the answer to this question:
tl;dr: select * from mysql.global_priv
then for each User,
show grants for 'XXX'@'localhost';
Longer version, from Authentication in MariaDB 10.4 — Understanding the Changes:
The password storage has changed. All user accounts, passwords, and global privileges are now stored in a mysql.global_priv table. What happened to the mysql.user table? It still exists and has exactly the same set of columns as before, but it’s now a view over mysql.global_priv...."
The aforementioned article provides not only what what but also the why. I do not agree with all of it. In particular the claim is made that Old mysql.user table still exists, you can select from it as before, but you cannot (hence this question). Nonetheless I am relieved to discover a relatively coherent explanation from MariaDB.
Finally, here is an example:
MariaDB [(none)]> select * from mysql.global_priv\G
*************************** 1. row ***************************
Host: localhost
User: mariadb.sys
Priv: {"access":0,"plugin":"mysql_native_password","authentication_string":"","account_locked":true,"password_last_changed":0}
*************************** 2. row ***************************
Host: localhost
User: root
Priv: {"access": 1844674407370915, "plugin": "mysql_native_password", "authentication_string": "*9A87226E872127C756290C504DB5D9076E", "auth_or": [{}, {"plugin": "unix_socket"}], "password_last_changed": 1617303275}
*************************** 3. row ***************************
Host: localhost
User: mysql
Priv: {"access":1844674407371615,"plugin":"mysql_native_password","authentication_string":"invalid","auth_or":[{},{"plugin":"unix_socket"}]}
*************************** 4. row ***************************
MariaDB [(none)]> show grants for 'root'@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING '*9A87226E872127C756290C5BF177504DB5D9076E' OR unix_socket WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION