MySQL Bugs: #80848: trying to reset root password--skip-grant-tables

Bug #80848	trying to reset root password--skip-grant-tables
Submitted:	24 Mar 2016 13:19	Modified:	24 Mar 2016 14:20
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.7.11	OS:	Windows (10 Pro 64 bit)
Assigned to:		CPU Architecture:	Any

Description:
Is this expected? It looks that security hardening in MySQL 5.7 causes issues with --skip-grant-tables (at least on Windows).

How to repeat:
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld --skip-grant-tables -uroot -p
mysqld: Can't change dir to 'C:\Program Files\MySQL\MySQL Server 5.7\data\' (Errcode: 2 - No such file or directory)
2016-03-24T12:55:51.095264Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-03-24T12:55:51.095264Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2016-03-24T12:55:51.095819Z 0 [Note] mysqld (mysqld 5.7.11) starting as process 9656 ...
2016-03-24T12:55:51.097821Z 0 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.7\data\SH-WIN10.lower-test
2016-03-24T12:55:51.097821Z 0 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.7\data\SH-WIN10.lower-test
2016-03-24T12:55:51.100065Z 0 [ERROR] failed to set datadir to C:\Program Files\MySQL\MySQL Server 5.7\data\
2016-03-24T12:55:51.112870Z 0 [ERROR] Aborting

2016-03-24T12:55:51.113870Z 0 [Note] Binlog end
2016-03-24T12:55:51.115117Z 0 [Note] mysqld: Shutdown complete

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld --skip-grant-tables --datadir="C:\ProgramData\MySQL\MySQL Server 5.7\Data" -uroot -p

C:\Program Files\MySQL\MySQL Server 5.7\bin>

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld --skip-grant-tables --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini"
mysqld: Can't change dir to 'C:\Program Files\MySQL\MySQL Server 5.7\data\' (Errcode: 2 - No such file or directory)
2016-03-24T13:07:41.799059Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-03-24T13:07:41.799059Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2016-03-24T13:07:41.799681Z 0 [Note] mysqld (mysqld 5.7.11) starting as process 3932 ...
2016-03-24T13:07:41.801683Z 0 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.7\data\SH-WIN10.lower-test
2016-03-24T13:07:41.801683Z 0 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.7\data\SH-WIN10.lower-test
2016-03-24T13:07:41.804440Z 0 [ERROR] failed to set datadir to C:\Program Files\MySQL\MySQL Server 5.7\data\
2016-03-24T13:07:41.819194Z 0 [ERROR] Aborting

2016-03-24T13:07:41.820195Z 0 [Note] Binlog end
2016-03-24T13:07:41.821196Z 0 [Note] mysqld: Shutdown complete

.. and server does not start in either case. It starts as a service from registry key specifying 

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57

The my.ini specifies 
datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data

Suggested fix:
I would be happy to be pointed to a mistake - but I am almost sure I did not have similar problems before 5.7.

Hi Peter,

Sure it's a mistake to run mysqld.exe with options -uroot -p  ?

HA!

closing!

Hello i'm struggling with this same issue...and was getting the exact same errors i.e. trying to reset the password on MySQL, same OS;

I tried --skip grant tables; on the command line. It works i.e does not give me an error but when I restart the MySQL server it still asks for a password and does not start,

any help or guidance would be appreciated!!!!