0

I have the master running Mysql 5.5.38 on Ubuntu 14.04 LTS. The timezone in the master is UTC+5:30. The slave is in AWS RDS which runs in UTC. The slave is primarily used for running for SQL queries by staff.

To maintain their sanity, I setup the timezone hack. This also applies to the replication user.

One of our innodb tables records scheduled appointments. The relevant parts of the table are below:

`startDateTime` timestamp NOT NULL DEFAULT '2000-01-01 12:00:00', `endDateTime` datetime DEFAULT NULL, `createdOn` timestamp NULL DEFAULT NULL,

I am seeing the following time on the master when querying the table:

  • startDateTime - IST
  • endDateTime - IST
  • createdOn - IST

However on the slave:

  • startDateTime - UTC
  • endDateTime - IST
  • createdOn - IST

Running SELECT NOW(); on either servers shows current time in UTC+5:30. The bin_log format on both master and slave is STATEMENT

How do I ensure that the replicated column startDateTime is also in IST?

Update:

  • RDS is a hosted mysql with no access to my.cnf. Futher time_zone variable is restricted and hard coded to UTC
1

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATETIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values.

http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html

Change your binlog format to ROW to eliminate the vagaries of statement-based replication and then change your DATETIME columns to TIMESTAMP (on the master, let the change replicate).

This solves the issues you're experiencing.

Although... it solves entirely the wrong problem, which is that it's typically better to store times properly in UTC and localize them as needed. Of course, that's what TIMESTAMP columns essentially do -- they store datetimes as UTC after converting then from the current session time zone, and then they convert back when you read them.

If you want locale-specific arithmetic for DATETIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

-1

I had similar issues a month or 2 ago. There is a solution for this. But this has been implemented in CentOS.

There are 2 steps for the resolution [run this on slave]:-

  • mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p mysql
    This command should copy all the time from unix timezone directory to mysql.time_zone_name table

  • After this you need to tell mysql which timezone to use, to do this
    Edit /etc/my.cnf to add a line default_time_zone=Asia/Kolkata , Asia/Kolkata could be changed to any timezones you want.

    This change will require mysql restart to take in effect.
    If you do not want to restart then simply use this in mysql command
    set global time_zone = 'Asia/Kolkata'; set session time_zone = 'Asia/Kolkata';



Hope this helps!!

  • Thanks for your answer. Unfortunately RDS is a hosted mysql with no access to my.cnf. Futher time_zone variable is restricted and hard coded to UTC. – Shoan Sep 12 '14 at 13:03

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.