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