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:Microsoft Windows (10 Pro 64 bit)
Assigned to: CPU Architecture:Any

[24 Mar 2016 13:19] Peter Laursen
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.
[24 Mar 2016 14:17] Shane Bester
Hi Peter,

Sure it's a mistake to run mysqld.exe with options -uroot -p  ?
[24 Mar 2016 14:20] Peter Laursen
HA!

closing!
[8 Sep 2016 11:58] Talha Muhammad
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!!!!