Mysql: Needs Restart When Time Changes To Dst?

[larsen]

Hi,

this has not directly to do with SQLyog, but I hope you don´t mind me asking.

I have a customer where an application of mine is running that is using MySQL 5.1.33. They noticed some problems with the saved timestamps and I found out that the option to automatically switch between normal and daylight saving time was not set.

So, I checked “switch automatically to DST” and the system time was fine. Then I noticed that NOW() still gives the “old” time that is 1 hour behind. It was only after restarting the MySQL server that NOW() would give the same time as the system´s time.

I read this http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html and checked the MySQL variable “time_zone” is set to “system”.

Finally to my questions =)

Does MySQL always have to be restarted to have NOW() get the same time as the system after switching to/from DST?

That would mean that I´d have to adapt my application to have it change the connection´s timezone?

How can I configure MySQL to always let NOW() return the system time?

Lars

[peterlaursen]

You are welcome to ask – but this is not something anybody here knows particular much about I think. 

“How can I configure MySQL to always let NOW() return the system time?”, you ask.  Well, maybe a better option was NOT to have the MySQL timezone setting set to SYSTEM?

One option is to run MySQL servers in GMT timezone (what does not have DST). You will then have to “SET [SESSION] time_zone = timezone;” when connecting from applications.  I have seen this is highly recommended by very experienced DBAs (and it also works around some issues with replication where the same time repeats itself one hour (typically like 1:19 .. 2:00 .. 2:59 .. 2:00 .. 2:59 .. 3:00). These two hours occurring every year is basically a hopeless situation for a computer.

Btw:  Timestamps are always stored in GMT on the server (as opposite to DATETIMES that are stored as specified by client) but when returned to a client the value is transformed to client timezone.

From your link: “Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:SET time_zone = timezone;” And to get 'human time' you will need to “SELECT now() INTERVAL …”. 

Also this statement from same link is unambigious: “When the server starts, it attempts to determine the time zone of the host machine and uses it to set the system_time_zone system variable. The value does not change thereafter.” (emphasis by me)

But there is an option to “SET GLOBAL time_zone” according to http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_time_zone. So one option could be to schedule execution of such statement when DST changes. 

The client timezone and the INTERVAL relative to GMT can easily be determined with any scripting/programming language no matter what solution you prefer.

[peterlaursen]

I should use the term 'UTC' and not 'GMT'

http://geography.about.com/od/timeandtimezones/a/gmtutc.htm

[larsen]

So, I would need a thread (it´s a Java program) that continuously checks wether DST has started/ended and according to that change the connection´s timezone???

Any idea why MySQL doesn´t simply use the server´s time? I guess there must be some reason.

(and all this affirms me that I don´t like DST – time shouldn´t jump 😉

[peterlaursen]

I can only repeat my quote that if TZ is set to SYSTEM then “When the server starts.. determine the time zone of the host machine and uses it to set the system_time_zone system variable. The value does not change thereafter.” (thereafter = after server start”).  If you want to know WHY, better ask MySQL! 

One option is to use an EVENT (on 5.1+) or schedule a small client script with the OS scheduler (CRON, Windows Scheduler) executing SET GLOBAL time_zone …

If you want MySQL to use the system's time with automatic DST changes on Windows (at least) I think you need to 

Code:

SET GLOBAL time_zone = '+02:00';

when DST changes in March and 

Code:

SET GLOBAL time_zone = '+01:00'

in October. That is how I calculate German times in my head right now (but I am in India at the moment  so I may be confused in my head! Besides time is something you worry about when you get old 🙂 ).

I have not tried to verify that MySQL gets one hour 'out of sync' with global TZ = SYSTEM when DST changes (that is how I understand you).  Maybe also if you load the TZ tables and set timezone to 'Berlin' (or whatever it is named) it may handle automatically.  Actually I think it will. Refer http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html at the bottom and http://dev.mysql.com/downloads/timezones.html

[peterlaursen]

So my conclusion is: do not run (production) MySQL servers with TZ set to SYSTEM, if the system is using DST. Instead if system is running TZ 'blahblihbluh' then also let MySQL run TZ = 'blahblihbluh'.  This requires the timezone tables in the `mysql` database to be populated (what they are not after an install) so that TZ = 'blahblihbluh' can be resolved by the server.

There may be bugs (and have been with exotic timezones) of course with MySQL TZ descriptions in the timezone tables.  But as you are using CET I would find it very unlikely that you will ever encounter such bug.

[larsen]

Thanks a lot for that information!

[Author]

Posts