I don’t remember the MySQL login password. I couldn’t log in. I wanted to modify the MySQL password, but an error was reported
ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’
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="123" where user="root";
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
Looking at the table in detail, it is found that there is no password field in the table, and the password field is changed to authentication_ String, as follows:
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
Solution:
mysql> update user set authentication_string="123" where user="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,authentication_string from user where user="root";
+------+-----------------------+
| user | authentication_string |
+------+-----------------------+
| root | 123 |
+------+-----------------------+
1 row in set (0.00 sec)
Read More:
- ERROR 1054(42S22) Unknown column ‘password’ in ‘field list’ (Fixed)
- Mysql5.7.18.1 Error 1054 (42S22): Unknown Column’password’ In’field List’ When Changing User Password
- [Solved]Error 1054 (42s22): unknown column ‘password’ in ‘field list’ how to modify the password
- [Solved] Daily further: database error: unknown column ‘model’ in ‘field list‘
- [Solved] pymysql.err.internalError: (1054, “Unknown column ‘LGD_xiaohong’ in ‘field list’”)
- [Solved] Sequelize DatabaseError: ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP
- Solve ERROR 1136 (21S01) in Mysql: Column count doesn’t match value count at row 1
- [MySQL] The principle of group by aggregation function and the reason for aggregation limitation SELECT list is not in GROUP BY clause and contains nonaggregated column
- Error 1406 (22001) in MySQL: data too long for column (Fixed)
- [Solved] There is a problem with mysql8.0 without password in Navicat connection
- postgresql-Database query problem-ERROR: column “t.domainid“ must appear in the GROUP BY clause or be used in an a
- MySQL Change password failure prompt: ERROR 1064(42000):You have an error in your SQL syntax: check the corresponds to your M
- [Solved] there are special symbols in the initial password for installing MySQL in Hadoop, and an error is reported
- [Solved] ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
- Docker mysql8 modify password
- MYSQL Error 1045 (28000): access denied for user ‘root’ @’localhost ‘(using password: Yes)
- [Solved] MySQL Error: “Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”
- ERROR 1265 (01000): Data truncated for column ‘ENABLED’ at row 7
- [Solved] MYSQL Error: unknown error 1130, unknown error 1045
- MYSQL Use cmd to change root password error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual tha