Resolved “Unknown column ‘password’ in ‘field list'”
I was trying to recover Mysql root password through mysql safe mode . When i tried to update the Mysql “user” table with new password, using following
|
1
2
3
4
5
6
7
8
9
|
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with –A
Database changed
mysql> update user set password=PASSWORD(“7heiuefuife778shjscjh”);
ERROR 1054 (42S22):Unknown column ‘password’ in ‘field list’
|
As you can see , it was throwing following error
|
1
2
3
|
ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’
|
This means , there is no column named “password” inside the user table. After a quick google search, I found that In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is ‘authentication_string’.
In mysql 5.7+, you can use following command to reset the mysql password for a user
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> update user set authentication_string=PASSWORD(“7heiuefuife778shjscjh”);
Query OK, 2 rows affected, 1 warning (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
|
Compared to MySQL 5.6, the changes are quite extensive in 5.7 . Please see the complete list of changes here