Mysql5.7.18.1 modify user password error ERROR 1054 (42S22): Unknown Column ‘Password’ In ‘Field List’ solution

The 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 after execution ERROR 1054(42S22) Unknown column ‘password’ in ‘field list’

The reason for the error is that there is no password field in the mysql database under version 5.7, and the password field has been 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 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 error
ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’
mysql> update mysql.user set authentication_string=password(‘*******’) where user=’*******’; #password has been updated
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 #Log in successfully as this user.
Enter password: ********

Was this article helpful?

Related Articles

Leave A Comment?

You must be logged in to post a comment.