Annoying timezone problem

neilwatson

Currently in mysql "select now();" returns GMT time. I added the switch --timezone=Canada/Eastern to the safe_mysqld startup and tried --timezone=EDT but, in both cases "select now();" and timestamp columns are still returning GMT. What have I missed?

Platform: linux
Mysql V: 3.23.49

RobNewYork

Section 20.15 of the mysql manual for v3.23.22beta says...

"If you have a problem with SELECT NOW() returning values in GMT and not your local time, you have to set the TZ environment variable to your current timezone. This should be done for the environment in which the server runs, for example in safe_mysqld or mysql.server. See section A Environment variables."

I don't find any reference to the switch you're referring to. Where was that documented?

neilwatson

The --timzone reference is Here. It does change the variables as mysqladmin variables -p returns timezone: Canada/Eastern.

RobNewYork

Thanks for the reference. Seems that the --timezone switch is looking for a number. Did you try:

--timezone=-0400

(or whatever GMT offset is appropriate?)

neilwatson

It doesn't like numbers. The timezone variable becomes blank.

RobNewYork

Other possible fixes are here.

neilwatson

Most fixed point to to my system clock being wrong. That is not the case:

[root@valetta]$date -u
Wed Oct 23 19:03:38 UTC 2002
[root@valetta]$date
Wed Oct 23 15:03:40 EDT 2002

RobNewYork

I'm assuming you've already validated that you have a zoneinfo file for Canada/Eastern (/usr/lib/zoneinfo/Canada/Eastern).

I did find one other thing to try:

use --timezone=:Canada/Eastern (note the colon).

This is from the man page for tzset for linux.

neilwatson

I have a file /usr/share/zoneinfo/Canada/Eastern. I tried adding the colon but no change 🙁

neilwatson

Solution:

Edit the safe_mysqld script. Change the TZ variable at the top. In my case Canada/Eastern still did not work. I changed it to EST5EDT and it works now. The true test will be to see what the time says on Monday.