MySQL off-site replication and timezones

Recently we were doing some testing on a mysql slave server which was located at an off-site location from the master server. While the on-site slave was having no problems replicating, the off-site slave would once a day break replication on a duplicate entry error because of a unique key insert problem.

This was very weird, especially because the on-site slave was having no trouble at all. After some brainstorming aided by percona, we realized the cause. The off-site slave was in a different timezone than the other master and slave. This fact together with the fact that we had a unique key that contained curdate(), caused the following scenario:

• On January 3, 23:57, there was an insert on table t, unique key was 2009-01-03.
• On January 4, 00:01, there was an insert on table t, unique key was 2009-01-04

No problem, but — times are replicated as timestamps, so on the off-site slave, which was 1 hour ahead (EST instead of CST):

• First insert was replicated as January 4, 00:57, insert on table t, unique key 2009-01-04
• Second insert was replicated as January 4, 01:01, insert on table t, and error on unique key for 2009-01-04

The solution is either to set the whole machine’s timezone to the timezone of the master you’re replicating, or using the same time-zone and default-time-zone settings for the mysql server.