When updating or changing your MySQL user password, you might encounter an error saying the password column is unknown.
Consider the following example UPDATE statement:
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 authentication_string.
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.
Furthermore, the 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 @'localhost' or @'%')
You can find the Host information of your user by using the following SELECT query:
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.
Using the 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.
