Mysql5.7.18.1 Error 1054 (42S22): Unknown Column’password’ In’field List’ When Changing User Password

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

mysql> update user set password=password(“new password”) where user=”username”;

ERROR 1054(42S22) Unknown column’password’ in’field list’ is reported after execution

The reason for the error is that there is no password field in the mysql database under version 5.7, and the password field is changed to authentication_string

So please use the following 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 = " ******* " ; #Modify password error
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update mysql.user set authentication_string=password('*******') where user='*******';  #修改密码成功
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 #Log in as this user successfully.
Enter password: ********
…………………………
mysql>

Read More:

Leave a Reply

Your email address will not be published. Required fields are marked *