[Solved] Error 1054 (42s22) unknown column ‘password’ in ‘field list’ when updating the password of mysql5.7 or above

Error 1054 (42s22) unknown column ‘password’ in ‘field list’ is reported after execution

The reason for the error is: There is no password field in the MySQL database in version 5.7, and the password field is changed to authentication_string

>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

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

mysql> use mysql;
Database changed
mysql> select User from user;  #This 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 error
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update mysql.user set authentication_string=password('*******') where user='*******';  #Change password error
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

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

Similar Posts: