1

I'm having a weird issue on the timezone used by a MySQL server running on a Windows Server 2012

Things I have done:

  1. set the default timezone in my.ini file, in this case default-time-zone=America/Chicago
  2. ensure I'm using that actual ini file
  3. check the timezone variable actually used by MySQL which say SYSTEM
  4. loaded some timezone tables into MySQL data folder, cause I read in windows those tables aren't there by default
  5. check server actual time which is correctly set to UTC-06:00, same time as Chicago
  6. run a select now() statement

The last item in the list is returning me a different time, five hours ahead, one thing is that when I restart the server for some time the timezone is correctly set to Chicago time, but after a few days, it got back to the incorrect one, always 5 hours ahead.

I have no idea why this is happening.

TylerH
  • 20,799
  • 66
  • 75
  • 101
goseta
  • 770
  • 1
  • 7
  • 26

1 Answers1

0

after establishing mysql connection please run this query.

SET SESSION time_zone = 'America/Chicago'
Ammadu
  • 1,675
  • 15
  • 17
  • thanks for the reply, but this will be needed each time I start a connection to the server?, this can work for now, but I will like to know why is changing back and how I can set the timezone permanently – goseta Jul 22 '15 at 16:59
  • you can change mysql server timezone from the mysql configuration mysql.conf, please refer this answer http://stackoverflow.com/a/6019540/1716437, there it changes mysql server timezone to system settings, so if your system is on the right timezone all should be fine – Ammadu Jul 22 '15 at 17:04
  • Yes exactly, thats what I did in step 1 of my list, but is still not working, I was reading something about the daylight time affecting timezone, and also the five hours ahead it means MySQL is changing back to UTC time, any ideas why?? – goseta Jul 22 '15 at 17:11
  • 1
    on a user with super privilege run this query SET GLOBAL time_zone = 'America/Chicago'; – Ammadu Jul 22 '15 at 17:20