ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or def

I needed to change the hostname on mysql database logins because the application server name was changed. I ran the following command:

MariaDB [mysql]>  update user set host='app1.example.com' where Host='app2.example.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

Starting with the MariaDB-10.4+ the mysql.user is a view rather than a table.

Always check the official documentation and use SET PASSWORD or ALTER USER to manage user authentication.

If you need to execute massive command rename user, for example because you have change hostname to application server, this script can help you :

An example output :

select user,host from user where host='app1.example.com';
+-----------+-----------------+
| User      | Host            |
+-----------+-----------------+
| USER1     | app1.example.com|
| USER2     | app1.example.com|
| USER3     | app1.example.com|
| USER4     | app1.example.com|
| USER5     | app1.example.com|
+-----------+-----------------+

We need to change Host from app1.example.com to app10.example.com and theoretically you have to run 5 rename user commands, but thanks to this script the command will be generated without writing “by hand”.

SELECT CONCAT('RENAME USER \'', user, '\'@\'',host, '\' TO \'', user, '\'@\'app2.example.com\';'  ) from mysql.`user` u where host='app1.example.com';
|+-----------+-----------------+-----------------+-----------------+--------------+
|RENAME USER 'USER1'@'app1.example.com' TO 'USER1'@'app2.example.com';            |
|RENAME USER 'USER2'@'app1.example.com' TO 'USER2'@'app2.example.com';            |
|RENAME USER 'USER3'@'app1.example.com' TO 'USER3'@'app2.example.com';            |
|RENAME USER 'USER4'@'app1.example.com' TO 'USER4'@'app2.example.com';            |
|RENAME USER 'USER5'@'app1.example.com' TO 'USER5'@'app2.example.com';            |
+-----------+-----------------+-----------------+-----------------+---------------+

Copy the output and execute.