MariaDB Error 1356: How to Correctly Update User Settings?

Error 1356 in MariaDB: Understanding the Invalid View Reference

When attempting to update user settings, you may encounter the error message "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." This error arises because:

In MariaDB-10.4 and later versions, the "mysql.user" is no longer a table but a view. Thus, it cannot be directly modified using UPDATE queries.

Solution:

To update user authentication, it is recommended to use the "SET PASSWORD" or "ALTER USER" commands instead. These commands allow you to directly manage user authentication without accessing the "mysql.user" view.

For example:

MariaDB [mysql]> SET PASSWORD FOR 'root'@'%' = 'new_password';

Caution:

Manipulating user/host components of usernames can have unintended consequences, such as breaking triggers, events, and roles. Therefore, it is generally advised to drop and re-create users rather than modifying them.

The above is the detailed content of MariaDB Error 1356: How to Correctly Update User Settings?. For more information, please follow other related articles on the PHP Chinese website!