MySQL Bugs: #79027: resetting password using skip-grant-tables bug

Bug #79027	resetting password using skip-grant-tables bug
Submitted:	30 Oct 2015 1:36	Modified:	13 Nov 2015 14:00
Reporter:	Omar Bourja	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.9-log	OS:	CentOS
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	rest password mysql skip-grant-tables

Description:
Hello Everyone 

I would like to let you know that the documentation :
https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html
share the method with using skip-grant-tables to reset the password doesn't seems to be working 
the only workaround I much for me is : 
update mysql.user set authentication_string=password('MyNewPass') where user='root';

Would it be possible to make tests and try to resolve this issue also please try to have the documentation modified with more details about how to reset the password or change old password when it is lost .

Thanks in advance for your help.
I look forward to hearing from you,

How to repeat:

mysql> select @@version, @@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 5.7.9-log | MySQL Community Server (GPL) |
+-----------+------------------------------+
1 row in set (0.00 sec)

mysql> show grants;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

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

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
ERROR 1131 (42000): You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords

mysql> show grants;
ERROR 1141 (42000): There is no such grant defined for user '' on host ''

mysql> update mysql.user set authentication_string=password('MyNewPass') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

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

mysql> show grants;
ERROR 1141 (42000): There is no such grant defined for user '' on host ''

mysql> ^D Bye

# mysql -uroot -pMyNewPass -e'show grants'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

also using 

update mysql.user set authentication_string=password('MyNewPass') where user='root';

then trying to change the password with documented ALTER USER method we have this error : 

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

maybe this describe  as child bug for this bug

Hi,

Thanks for the report. The bug is verified as described.

all best
Bogdan Kecman

Hello

I also experienced  similar situation when I tried to reset mysql root password.According to mysql documention(https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html)
I tried to reset mysql root password but it took few hours to find solution,because It gave me   an error

1)First I  add skip-grant-tables in my.cnf and restart mysql server,then I able to loginto system without password.

2)Then I enterd Flush Prvilieges 

3)Afterwhich I eneterd  ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass',howver below error reflected.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
ERROR 1131 (42000): You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords.

Anyway thanks to  Omar Bourja solution I able to reset mysql root password.

Mysql Version 5.7.9-log

Thanks

The server, when started in --skip-grant-tables, is not in a functional state. The in-memory ACL cache is not initialized at all. This makes most of the commands (except the very basic ones) unusable. 

Fortunately there's a workaround for this. 

You can (after the server initializes and you open up a session to it) issue FLUSH PRIVILEGES through that session.
It will fill in the ACL cache and all will work well again. Note that this will do nothing to your current session and it will be a session with SUPER privileges, so you can use that to set passwords etc.

Here's how it works: 
c:> mysqld --skip-grant-tables ...
c:> mysql.exe -u root ...
mysql> select user(), current_user();
+--------+-----------------------------------+
| user() | current_user()                    |
+--------+-----------------------------------+
| root@  | skip-grants user@skip-grants host |
+--------+-----------------------------------+
1 row in set (0.00 sec)

mysql> set password for root@localhost = 'haha';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

mysql> set password for root@localhost = 'haha';
Query OK, 0 rows affected (0.03 sec)

The sequence of commands shown by Georgi works as of MySQL 5.7.10. ALTER USER will work as well. That is, for a server started with --skip-grant-tables, connecting to the server and executing FLUSH PRIVILEGES will cause both ALTER USER ... IDENTIFIED BY ... and SET PASSWORD FOR ... to work.

The issue reported in this bug report occurs due to a bug present in 5.7.3 through 5.7.9. The version used when reporting the bug is 5.7.9, which accounts for the failure.

In the meantime (until 5.7.10 is available), the workaround reported earlier in the bug report can be used:

update mysql.user set authentication_string=password('MyNewPass') where user='root';