Mysqld command does not start the MySQL Server

[Wrichik Basu]

TL;DR Summary

mysqld command does not start the MySQL 8.0 server.

I just installed MySQL Server 8.0. The server is configured to run automatically at startup. However, when I wake up my laptop from hibernation or sleep, sometimes the server stops. I read on the net that I should execute mysqld from command line to start the server.

I have set the folder of binaries in MySQL Server install folder in the path variable. When I execute the above command in PowerShell, I get the following error:

PS C:\Users\Wrichik Basu> mysqld
2020-05-09T14:06:37.383019Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.20) starting as process 3964
2020-05-09T14:06:37.437373Z 0 [Warning] [MY-010091] [Server] Can't create test file C:\Program Files\MySQL\MySQL Server 8.0\data\mysqld_tmp_file_case_insensitive_test.lower-test
2020-05-09T14:06:37.437616Z 0 [Warning] [MY-010091] [Server] Can't create test file C:\Program Files\MySQL\MySQL Server 8.0\data\mysqld_tmp_file_case_insensitive_test.lower-test
2020-05-09T14:06:37.438002Z 0 [ERROR] [MY-013276] [Server] Failed to set datadir to 'C:\Program Files\MySQL\MySQL Server 8.0\data\' (OS errno: 2 - No such file or directory)
2020-05-09T14:06:37.476711Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-05-09T14:06:37.477010Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.

Taking a hint from line #5, I created the data directory under the path stated in the error message: C:\Program Files\MySQL\MySQL Server 8.0\. Now when I run mysqld, there are no error messages but the server simply doesn't start. So I have to manually go to Services and start the server.

I plan to work from Java using the MySQL J connector. Once the server is up, I can execute commands from Java properly.

Why is mysqld not working?

Note that opening command prompt/PowerShell with administrator privileges does not solve the problem.

 

[Tom.G]

Just a guess: Is the file path or name too long?

 

[Wrichik Basu]

Just a guess: Is the file path or name too long?

I didn't get it: which path are you talking about?

 

[Tom.G]

The one it's complaining about.

Path start with "C:\" and includes all before the last slash "\", the filename is all after that last "\", and the filetype is after the last ".".
C:\Program Files\MySQL\MySQL Server 8.0\data\mysqld_tmp_file_case_insensitive_test.lower-test

Another possibility is the period in "8.0". Some software doesn't accept a "." in a path name.

This is a carry-over from earlier operating systems where a "." is considered the beginning of the filetype. These days the operating systems consider the filetype to be after the last "." in the line.

Also watch out for spaces in file specifications. A Space character in a path/filename is a delimiter unless the string is enclosed in quotes, "...".

Cheers,
Tom

 

[Wrichik Basu]

Another possibility is the period in "8.0". Some software doesn't accept a "." in a path name.

This is a carry-over from earlier operating systems where a "." is considered the beginning of the filetype. These days the operating systems consider the filetype to be after the last "." in the line.

Also watch out for spaces in file specifications. A Space character in a path/filename is a delimiter unless the string is enclosed in quotes, "...".

Cheers,
Tom

The file directories were created by the installer; I did not change anything.

But now there seems to be more errors in the installation. The path to the data directory of the server is supposed to be C:\ProgramData\MySQL\MySQL Server 8.0\Data but the system is not accepting it. There was a configuration file, my.ini, lying in the above folder that was supposed to be present in C:\Program Files\MySQL\MySQL Server 8.0. I copied it there and the errors stated in the OP are no longer being displayed. I discovered this fix from here:

https://forums.mysql.com/read.php?169,673459,673459

But now the mysqld command never stops executing. No errors, no return, nothing. The execution just goes on for hours if I don't terminate it. Perhaps internally it is throwing errors without showing me. Interestingly, however, the server is working fine if I start it from the Services app.

 

[jim mcnamara]

Did you consider permissions required? If services starts the process it has or can get the correct permissions or correct user to invoke the program. Owner!=admin, for example.

1. Press the Windows key + R to open the Run box, type taskmgr and then hit Enter to launch Task Manager.
2. Go to the Details tab. ...
3. Scroll down until you see the Elevated option, check that box and click OK.
4. Now, the Details tab of Task Manager will be showing a new “Elevated” column.

So you can see what processes including mysqld have special privileges.

 

[Wrichik Basu]

Did you consider permissions required? If services starts the process it has or can get the correct permissions or correct user to invoke the program. Owner!=admin, for example.

So you can see what processes including mysqld have special privileges.

Problem persists even if I try to run mysqld from an elevated power shell/command prompt.

 

[jim mcnamara]

Run mysqld as the NETWORK user:

To run an app as a different user in Windows 10, do the following.

1. Open File Explorer and go to the folder which contains the required app.
2. Press and hold the Shift key and right-click on the file.
3. In the context menu, select Run as different user.
4. Enter the new credentials and click OK to run the app.

Be sure the process is not running already, since there probably is a semaphore object as a global atom/file enforcing that limit.

I have not done windows internals for 20 years. I assume atoms still exist in the OS. So take my comment with a grain of salt.

 

[pbuk]

Like a lot of server technology mysql is a lot easier to manage under Linux. I spend most of my time in Windows 10 and use hosted VPS servers for Apache/Nginx/mysql/node etc., but if I want a local copy on my laptop for testing I fire up a virtual machine (I use VirtualBox). If you have enough RAM and CPU to make this a viable option I would recommend it. If not then perhaps get a cheap second hand PC and make it a full time Linux box. Anything but struggle with the Windows registry.

Edit: if you must stick with Windows 10 consider using XAMPP to manage MySQL: the people who put XAMPP together have done all the hard work making technology designed for Linux work with a Windows GUI manager. I used to use XAMPP before my laptop was big enough to run VMs happily and never had a problem.

 

[Wrichik Basu]

Run mysqld as the NETWORK user:

Be sure the process is not running already, since there probably is a semaphore object as a global atom/file enforcing that limit.

I don't think any user can start some service as a network service. Windows asks for the credentials, which I don't have because I have defined no network user in my PC, it has been defined by Windows.

It could also be possible that the software itself has a bug that is appearing on not all but some devices with specific machine architecture. mysqld command is supposed to work from a normal command prompt (even elevation is not required). I have tried almost everything on the internet and it doesn't work. I tried to file a bug in the MySQL bug system, but it requires me to have an Oracle account, and I am not interested in creating one.

 

[Wrichik Basu]

Like a lot of server technology mysql is a lot easier to manage under Linux. I spend most of my time in Windows 10 and use hosted VPS servers for Apache/Nginx/mysql/node etc., but if I want a local copy on my laptop for testing I fire up a virtual machine (I use VirtualBox). If you have enough RAM and CPU to make this a viable option I would recommend it. If not then perhaps get a cheap second hand PC and make it a full time Linux box. Anything but struggle with the Windows registry.

It is not profitable for me to use MySQL on a Linux virtual machine (I have already set up one where I test my Java apps). The reason is, I am working with databases to add them to an android app that I will/may build later. Android Studio cannot be run on a VM. It would be difficult for me to modify the database somewhere and copy it elsewhere to be executed.

 

[pbuk]

When it comes to development environments a VM on your local machine doesn't cost you anything (except the degradation in performance due to shared resources). The VM doesn't have to be running Android Studio, it can expose MySQL to your DE on the host machine through a local network.

When it comes to production release I would very strongly recommend against deploying a back-end server for an Android app on Microsoft technology.

Final thought - you get a load of resources free for a year with a Google Apps account (and also AWS but this is probably less suited to your requirements), have you looked at that?

 

[banishthem]

i presume op got this working by now but for a wild stab at issues like this regarding services

https://dev.mysql.com/doc/refman/8.0/en/server-options.html

--install-manual [service_name]

Property	Value
Command-Line Format​	--install-manual [service_name]
Platform Specific​	Windows

(Windows only) Install the server as a Windows service that must be started manually. It does not start automatically during Windows startup. The default service name is MySQL if no service_name value is given. For more information, see Section 2.3.4.8, “Starting MySQL as a Windows Service”.

Note
If the server is started with the --defaults-file and --install-manual options, --install-manual must be first.