E.3. Understanding Replication of Date/Time Values

  • Replicator processes default to UTC internally by setting the Java VM default time zone to UTC. This default can be changed by setting the replicator.time_zone property in the replicator services.propertiesx file but is not recommended other than for problem diagnosis or specialized testing.

  • Replicas store a time zone on statements and row changes extracted from MySQL.

  • Replicators use UTC as the session time zone when applying to MySQL replicas.

  • Replicators similarly default to UTC when applying transactions to data warehouses like Hadoop, Vertica, or Amazon Redshift.

  • The thl utility prints time-related data using the default GMT time zone. This can be altered using the -timezone option.

Best Practices

We recommend the following steps to ensure successful replication of time-related data.

  • Standardize all DBMS server and host time zones to UTC. This minimizes time zone inconsistencies between applications and data stores. The recommendation is particularly important when replicating between different DBMS types, such as MySQL to Hadoop.

  • Use the default time zone settings for Tungsten replicator. Do not change the time zones unless specifically recommended by VMware support.

  • If you cannot standardize on UTC at least ensure that time zones are set consistently on all hosts and applications.

Arbitrary time zone settings create a number of corner cases for database management beyond replication. Standardizing on UTC helps minimize them, hence is strongly recommended.