When updating or changing your MySQL user password, you might encounter an error saying the
password column is unknown.
Consider the following example
UPDATE mysql.user SET Password = PASSWORD('root') WHERE User = 'root';
The above statement might produce the following error:
ERROR 1054 (42S22): Unknown column 'Password' in 'field list'
This is because MySQL version 5.7 changed the
Password column into
If your MySQL server version is between 5.7.0 to 5.7.5, then you can change the
Password column into
authentication_string column as shown below:
UPDATE mysql.user SET authentication_string = PASSWORD('root') WHERE User = 'root'; -- Query OK, 0 rows affected (0.01 sec)
The above statement should work, but rather than using an
UPDATE statement, there’s a different statement that’s recommended to change MySQL user passwords.
PASSWORD() function has been deprecated since MySQL version > 5.7.5, so you might fail to execute the
UPDATE statement for MySQL version 5.7.6 and above.
To change your MySQL user’s password, it’s recommended to use the
ALTER USER statement.
The syntax is as shown below:
ALTER USER user IDENTIFIED BY 'password_here';
For example, to change the
root user password with
abcd, use the following statement:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'abcd'; -- Query OK, 0 rows affected (0.01 sec)
You need to add the user
Host part as shown below (using
You can find the
Host information of your user by using the following
SELECT User, Host FROM mysql.user;
The returned result set would be similar as shown below:
+------------------+-----------+ | User | Host | +------------------+-----------+ | nathan | % | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+
Once you find the
Host name of your user, just add it to the
ALTER USER statement as shown above.
To conclude, the
ALTER USER statement is the recommended statement to modify MySQL user account according to MySQL documentation.
UPDATE statement and the
PASSWORD() function is an old way of changing MySQL user passwords that no longer work in the latest stable MySQL version.