Why is My MySQL 5.7 Root Password Update Failing with \'Unknown column \'password\' in \'field list\'\'?

MySQL User DB Missing Password Columns

Problem:

While attempting to update the MySQL root password, the error message "Unknown column 'password' in 'field list'" appears, indicating that the password column does not exist in the user table.

Explanation:

In MySQL 5.7, the password column in the mysql.user table has been removed, and the authentication details are now stored in the 'authentication_string' column.

Solution:

1. Connect to the MySQL database as the root user:

mysql -u root

2. Select the MySQL database:

USE mysql;

3. Verify that the user table exists:

SHOW TABLES;

4. Examine the user table structure:

DESCRIBE user;

5. You will notice that the 'password' column is missing, replaced by the 'authentication_string' column.
6. Update the root user's password using the 'authentication_string' column:

UPDATE user SET authentication_string = PASSWORD('1111') WHERE user = 'root';

Additional Notes:

• MySQL 5.7 introduces significant changes compared to previous versions.
• For more information on MySQL 5.7 updates, refer to the official documentation: [What's New in MySQL 5.7](https://dev.mysql.com/doc/refman/5.7/en/whats-new-in-5-7.html)

The above is the detailed content of Why is My MySQL 5.7 Root Password Update Failing with \'Unknown column \'password\' in \'field list\'\'?. For more information, please follow other related articles on the PHP Chinese website!