0

I have been struggling with MySQL timezone issues for a while I think I finally know why. In the past 2 days my timestamps were saved 2 hours behind my actual machine time (2+ UTC Jerusalem).

I tried running these commands:

SELECT @@global.time_zone;
SELECT @@session.time_zone;

And they both return SYSTEM, which means MySQL configures it's timezone based on a .cnf/.ini file. Problem is that I have no such file. I have went through all the "default locations" MySQL is supposed to read it's configuration from and there was not one file. My machine right now is 9:15 AM (UTC +2) but for some reason MySQL timestamps save at 7:15 AM.

In a fluke I caught this piece of condemning info:

enter image description here

Apparantly the server reads it's configuration from .ini inside a 5.5 version of MySQL. But it didn't exist! Only after I used the "Options file" inside workbench for the first time it created it. Here is where:

enter image description here

Mind you I never used 5.5. I am too young for that (though I have and old installation of WAMP from the days I screwed around with wordpress). I tried to diagnose the situation by trying to find my installed version of MySQL but I got this error:

mysql: [ERROR] Found option without preceding group in config file C:\Program Files\MySQL\MySQL Server 8.0\my.cnf at line 1.
mysql: [ERROR] Fatal error in defaults handling. Program aborted!

Any reason why 8 MySQL server wishes to read .ini files from a 5.5 folder that didn't even exist?

0

After alot of digging I found the answer.

Inside %AppData%..\MySQL I found "server_instances.xml". For some reason it listed MySQL server version as 5.5 even though workbench AND command line both showed it is 8.0, and 5.5 was never installed in my machine.

I searched for all mentions of 5.5 and swapped them for 8.0. Saved, rebooted machine (just to be sure) and opened workbench. Created a new config file via the Options File under the Administration in workbench. Everything works :)

2
  • You say in your answer: and 5.5 was never installed in my machine., but in the question you mention that: though I have and old installation of WAMP from the days I screwed around with wordpress. Your server_instances.xml file didn't get 5.5 in there for some random reason - there must have been some remnant of a 5.5 install somewhere. Please don't forget to mark your own answer as correct as it may help others in the future - no points though! :-) Don't hesitate to come back to us here on dba.se if you have other database issues!
    – Vérace
    Jan 4 '20 at 10:47
  • @Vérace yeah I kind of figured that out somewhere along the rabbit hole :) Oh and thanks for being so refreshingly friendly!
    – Bar Akiva
    Jan 4 '20 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.