Can't create test file lower test start server mysql

Question

I'm following this tutorial here on creating a JDBC programming. I've followed the steps up to the console command to start the server:

// For Windows
cd {path-to-mysql-bin}   // Check your MySQL installed directory
mysqld --console

I get the following error message:

mysqld: Can't change dir to 'C:\Program Files\MySQL\MySQL Server 5.7\data\' (Errcode: 2 - No such file or directory)
2017-01-06T10:54:36.968210Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-01-06T10:54:36.968210Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-01-06T10:54:36.968210Z 0 [Note] mysqld (mysqld 5.7.17) starting as process 11248 ...
2017-01-06T10:54:36.974226Z 0 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.7\data\DESKTOP-DEQ2IC2.lower-test
2017-01-06T10:54:36.975229Z 0 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.7\data\DESKTOP-DEQ2IC2.lower-test
2017-01-06T10:54:36.975229Z 0 [ERROR] failed to set datadir to C:\Program Files\MySQL\MySQL Server 5.7\data\
2017-01-06T10:54:36.976231Z 0 [ERROR] Aborting
2017-01-06T10:54:36.977234Z 0 [Note] Binlog end
2017-01-06T10:54:36.978237Z 0 [Note] mysqld: Shutdown complete

I'm quite new to using MySql in this way but I can see that there is no data folder in the server directory and no my.ini file which I assumes holds the config information on where the data directory is. I've also insntalled MySql using the windows installer which I've read could be why there is no my.ini file and just a template my-default.ini file.

Does anyone know why this is happening?

Solution 1

Create a data folder in C:\Program Files\MySQL\MySQL Server 8.0.

Create a new file with

(ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';) 

command for password reset (if required)

Make sure that your file is in C: drive. root folder.

Then execute the below command:

C:\> cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
C:\> mysqld --init-file=C:\\mysql-init.txt

Solution 2

As mentioned above, create a "data" folder, (for me after that it didn't show errors, but it wasn't stills starting).
After that, go to services.msc in windows and start it from there. Also set it to Automatic so it starts automatically whenever you start the windows. It worked for me.

Solution 3

I ran the command bin\mysqld --initialize --console and everything was solved. In some cases the data folder should be created before. There are different parameters for windows and the rest.

See the following links:
https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html
https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html#data-directory-initialization-password-assignment
https://dev.mysql.com/doc/refman/8.0/en/option-files.html

Solution 4

This error happens to me. The solution was edit D:\Program Files\xampp\mysql\data\my.ini and change the directory to your D: path

[mysqld]
datadir=D:\program files\xampp\mysql\data
[client]

Solution 5

In my case, I installed MySQL with Windows installer on the D:/ drive and was set NOT to run as Windows service. I managed to resolve the issue by moving my.ini from C:\ProgramData\MySQL\MySQL Server 8.0 to MySQL installation folder.

View more solutions

Comments

[Mark Rotteveel]

You might want to do this from an administrator command prompt, see also stackoverflow.com/questions/26833642/… .

[Fane Fonseka]

I tried running command promt as adminstrator but this gave the same results. However, i followed your link and the command: "start> CMD> c:\Program Files\MySQL Server 5.7\bin> mysql -u root -p" did the trick!

[pacoverflow]

All I needed to do was create the C:\Program Files\MySQL\MySQL Server 8.0\data folder.

[Dupls]

pacoverflow it seems that the data folder was required. However to finally change the root password for mysql workbench I simply opened up the program and under Management > Users and Privileges. I selected root and changed the password. I stopped services, closed workbench and started services again and opened workbench again. Entered the new password and 'Stored to Vault'. New password works and all is well in mysql land.

[Ali]

@Asqan, It happened to me also, then I went to services.msc in windows, from there I clicked MySQL 8 and started it( it was on manual) , also I set it to automatic so it starts every time windows start, now it's working.

[JackJohnson]

at the MySQL installation folder or the MySQL server installation folder?

[Ivan Chau]

Official Ref: Resetting the Root Password: Windows Systems

[Tim]

C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" was what I needed to do from an Administrative cmd prompt.

[tolypash]

Had the same issue. Starting it from services.msc worked for me!

[Hamad]

mysql service isnt showing up in services.msc