How to Fix MySQL Error 1356: Invalid Table References in UPDATE Queries?

MySQL Error 1356: Invalid Table References in Update Query

Attempting to update user permissions using the UPDATE command can result in the 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

Understanding the Error

In MariaDB 10.4 and higher, mysql.user is a view, not a table. As a result, performing direct updates on this view is no longer supported.

Solution

To address this error, use the following recommended practices:

• Use SET PASSWORD or ALTER USER for Authentication Changes:
  Manage user authentication by using the SET PASSWORD or ALTER USER commands.
• Avoid Manipulating Existing Users:
  Modifying user/host components of existing usernames can lead to inconsistencies. Instead, drop and recreate users as needed.

Example

To grant remote access to the root user in MariaDB 10.4 , use the following command:

ALTER USER 'root'@'%' IDENTIFIED BY 'new_password';

Cautions

• Always refer to the official documentation for the most up-to-date information on MySQL and MariaDB authentication mechanisms.
• Copying commands from outdated sources may lead to errors and unexpected behaviors.

The above is the detailed content of How to Fix MySQL Error 1356: Invalid Table References in UPDATE Queries?. For more information, please follow other related articles on the PHP Chinese website!