#WTF MySQL 5.7: Crashes on Windows 10 after startup

The first JDBC request to the MySQL blocked and terminated with a timeout message. I checked the MySQL service in the console: It had started and was running. So far, everything seemed to be o.k.

Logfile Check

I checked the Eventlog (“Ereignisanzeige”) of Windows and discovered some errors like:

InnoDB: File .\ibdata1: 'Windows aio' returned OS error 121.
Error 121:  ERROR_SEM_TIMEOUT -->  The semaphore timeout period has expired.

Instead of the file ibdata1 sometimes ibtmp1 was included in the error-log.

Next I checked the MySQL *.err file. As expected, another warning-message that seems to be related:

2019-01-07T07:45:47.593552Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 43025888ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

43025888ms == 12 hours seem to be a very long time!

Google Check

No luck. No solution found. Unbelievable!

First Guess

Could it be some kind of file-locking from another process? Perhaps the Anti-Virus software (== Windows Defender)? Or the Acronis TrueImage backup-background process? I tested this by deactivating those services. No luck, either.

The Solution

I have noticed that the MySQL database didn’t crash, whenever I did a restart (“Neustart”). The problem only occured after a shutdown (“Herunterfahren”) and subsequent start of Windows. So what’s the difference between a restart and a shutdown/start-process on Windows 10? It turned out that Windows 10 uses a hibernate function (“Ruhezustand”) for speeding up the start-process. In contrast when using the restart-function there is a real shutdown without hibernating any processes.

So it seems that MySQL did not survive it’s hibernation!

Luckily the “fast startup” (“Schnellstart”) of Windows 10 can be disabled in the Power Options (“Energieoptionen”). Check the following screenshot (of the German version). Disabling this option, hence forcing Windows to shutdown all processes, fixed the MySQL bug for me.

Conclusion

There is a critical bug in MySQL 5.7 in interaction with Windows 10 (Version 1803). Whenever the hibernate function is used, the MySQL database can crash on the next startup. The workaround is to avoid the use of hibernation. Therefore disable the fast-startup function (“Schnellstart”) and don’t use the Hibernate- (“Ruhezustand”) or Sleep-Action (“Energie sparen”) for shutting down Windows.

Sure, the best solution would be if this one will be fixed by the MySQL and/or Windows Teams…

About the #WTF posts on this blog:
These are short articles that show simple solutions to problems that lead to a headache in the first place. This serves two goals: 1.) Remembering the solution (and own stupidity) for the next time 2.) Helping other people with similar problems.