How to Solve MySQL Error Message ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

By Posted on

Introduction

This is an article where the main content is focusing on solving an error message. The error message itself occur after executing a query in the MySQL command console. In detail, the error message is ‘ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.’ . Actually, before accessing MySQL command console, and also before executing the command triggering the error message, there are several sequences of process executed. Those processes are :

1. Installing MySQL Database Server manually. Configuring it from the tarbal source file.

2. Since it is a manual installation, the root password reset process is also manual. It is done by editing the file ‘my.cnf’. By adding the ‘skip-grant-tables’ in the mysqld section, it will allow access to MySQL command console using root account without password. Off course, the change to the file will need further restart before access.

3. Continue on the second step, access MySQL Command Console without password.

4. After successfully access the MySQL Command Console, change the root password in the command line of MySQL Command Console using the ‘update’ query. The following is the output of the execution step of third and fourth step : 

user@hostname:~$ mysql -uroot -P3307 -h127.0.0.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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> update mysql.user set authentication_string=password('password') where user='root';
Query OK, 1 row affected, 1 warning (0.20 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> quit
Bye

5. Finally, remove the ‘skip-grant-tables’ line in the mysqld section and restart the MySQL Database Server’s service. This step is to return back the MySQL Database Server’s behaviour to authenticate every user account access to MySQL Command Console.

At the end of the fifth step, after accessing back MySQL Command Console following with the execution of a certain query, it will generate the error message as follows : 

root@hostname:~# mysql -uroot -P3307 -h127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.28-log
Copyright (c) 2000, 2019, 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> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>

 

Solution

In order to solve the above error message, the following is the solution to handle it. Just execute the following command for initializing the root password : 

set password = password('your-password');

For an example, the following is the execution of the above query for changing the password of the user logging in to the MySQL Command Console : 

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password = password('password');
Query OK, 0 rows affected, 1 warning (0.11 sec)

Last but not least, try to log back or access the MySQL Command Console using the new password as follows : 

user@hostname:~$ mysql -uroot -P3307 -h127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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>

Leave a Reply