Wednesday, August 06, 2014

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement



MySQL 5.6 added comprehensive password security mechanism how password and internally handled and encrypted. One major feature is auto encrypted password generation when mysql first time installed on your system. you can find the encrypted password in /root/.mysql_secret. Once you are into mysql using encrypted password you will get this error until unless root password is not set :

SET PASSWORD = PASSWORD('new_password');

Here is how the error looks like:

-bash-4.1$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.19-enterprise-commercial-advanced
Copyright (c) 2000, 2014, 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 SET PASSWORD before executing this statement


Just reset the password and you should be able login on next attempt:

-bash-4.1$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.19-enterprise-commercial-advanced

Copyright (c) 2000, 2014, 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 SET PASSWORD before executing this statement
mysql> SET PASSWORD = PASSWORD('newpassword');
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
-bash-4.1$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.19-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, 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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)