0

I realized that some of my droplets in Digital Ocean have different timezone between OS and MySQL. By default, I've configured all vms to Brazilian timezone (America/Sao_Paulo) through tzselect, and, assuming that MySQL comes using default timezone as of system.

So, when I type for current timestamp, I get this from CentOS:

date

Thu Jun  9 09:43:03 BRT 2016

and from MySQL:

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2016-06-09 12:45:57 |
+---------------------+
1 row in set (0.00 sec)

mysql>

Reading docs of MySQL 5.5, I have option to use timezone as string, as 'America/Sao_Paulo' for instance, however, CentOS 6.5 comes with MySQL 5.1, and it doesn't has the same option, only using hours, as +3:00 ...I'm sure that I guess I should not worry about date/time of MySQL since I've configured it on system.

CentOS configs for timezone that I did:

I've used tzselect command to set Brazilian timezone, thinking that MySQL will assume this configuration. After reboot, I realize that system erase my configs on tzselect, to prevent this, I put this line at /etc/rc.local:

ln -sf /usr/share/zoneinfo/America/Sao_Paulo /etc/localtime

Even if this configs, I realize some vms are as UTC e some others with BRT...and at this moment, I really don't know how I'll configure all my servers, at least 40 machines, with same way.

Does someone can help me explaining how can I correctly set timezone for system a also for MySQL?

Additional Infos

System: CentOS 6.5 x64 MySQL: 5.1 (default)

Below, command asked by @lese:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2016-06-09 14:31:19 |
+---------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

$ date
Thu Jun  9 11:31:22 BRT 2016

And machine time at /etc/sysconfig/clock file:

[root@xxxx]# cat /etc/sysconfig/clock
ZONE=Etc/UTC

Best regards.

  • anything related to timezone config into the file /etc/my.cnf? – lese Jun 9 '16 at 14:36
  • So, I never modify default mysql config, it's original yet. – Carlos Spohr Jun 9 '16 at 14:39
  • I was thinking that I changed hour of and +3 timezone to get equals of my timezone, so, at this point mysql really loses himself. However it's my mistake. – Carlos Spohr Jun 9 '16 at 14:40
  • uhmmm did you restarted mysql after tzselect fix? – lese Jun 9 '16 at 14:41
  • yap, sometimes we reboot, but happens sometimes DigitalOcean did it. – Carlos Spohr Jun 9 '16 at 14:48
2

I found the problem,

I've used tzselect to select my timezone, however, /etc/sysconfig/clock was with ZONE=America/New_York, causing a little mistake on system and apps that consuming system date/time info. To fix it, and keep bios clock synchonized with system's clock, did these steps:

sudo vim /etc/sysconfig/clock

ZONE="America/Sao_Paulo"
UTC=true
ARC=false

Then reboot system.

sudo reboot

Remove unneeded timezone parameters, such as default_time_zone, from /etc/my.cnf (this one for CentOS) or /etc/mysql/my.cnf (for Ubuntu based OS).

Then reboot MySQL server instance:

sudo /etc/init.d/mysqld restart

And finally, to check if all configurations are OK, use these commands:

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2016-06-09 15:18:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[carlos@xxxx-setup ~]$ date
Thu Jun  9 15:18:34 BRT 2016
[carlos@xxx-setup ~]$

Thanks @lese by your attention.

  • good job well done, welcome for my attention : ) – lese Jun 10 '16 at 6:51
1

I hate to necro old posts, but this seemed like the only appropriate place to post this info. In CentOS 7, the default MariaDB 10 setup (using CentOS standard rpms) has the wrong location of the timezone files. We were having issues with applications that depend on MySQL for timezone info. No matter what settings we used within the applications, they always returned UTC time zone time.

This:

mysql_tzinfo_to_sql /usr/share/zoneinfo/right |mysql -u root mysql -p

Immediately corrected the issue. No reboot necessary! I cleared caches etc for good measure...

  • 1
    If it's relevant to the question, then a new answer is (at least) as suitable as one of the older ones. This isn't a forum, so new/old/dead doesn't really have as strong a meaning here. – roaima Apr 30 at 13:35

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.