ERROR 1054(42S22) Unknown column ‘password’ in ‘field list’ (Fixed)

This intention to modify a user’s password, the command found on the Internet is as follows

1 mysql> Update user set password=password(" new password ") where user= "username";

ERROR 1054(42S22) Unknown column ‘password’ in ‘field List’
The reason for the error is that the password field is no longer in mysql database in version 5.7, so the password field is changed to AUTHENTICation_string
So use the command:

>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed
mysql> select User from user;  # Here is the query user command
+-----------+
| User      |
+-----------+
| *******  |
| mysql.sys |
| root      |
+-----------+
3 rows in set (0.00 sec)

mysql> update user set password=password("*******") where user="*******";  # Change password report error
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update mysql.user set authentication_string=password('*******') where user='*******';  # Change password successfully
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;  # Effective immediately
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

n>mysql -u ******* -p # Login successfully with this user.
Enter password: ********
…………………………
mysql>

 

Read More: