A story about struggling with timestamp discrepancies in the Timestamp column during a database migration using DMS.
This is Ohara from the Service Reliability Group (SRG) of the Media Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article explains the timestamp-type time discrepancy issue that occurred during database migration using DMS and how to address it.
Time difference - originRemove the server time zone setting.Time discrepancy - Full load editionRegarding the behavior of DMS in relation to the presence or absence of serverTimeZone settings.Final configurationIn conclusion
Time difference - origin
When migrating an on-premises MySQL database to AWS Aurora using DMS, it was discovered that the timestamps in tables containing the Timestamp type were incorrect.
The configuration is as follows: a new replica server has been created on-premises to meet the DMS requirements and is being used as the DMS source server.
All servers use row-based replication, and the timezone is uniformly set to Asia/Tokyo (UTC+9).
The time discrepancy was caused by the fact that the data being replicated to Aurora was 9 hours earlier than the time recorded on the source server.
| server | Time data |
| on-prem Source | 10:00:00 |
| on-prem New Replica | 10:00:00 |
| Aurora | 19:00:00 |
serverTimezoneI also used "Asia/Tokyo" consistently without giving it much thought.
Remove the server time zone setting.
serverTimeZone=Asia/TokyoFrom a quick glance, it appeared that the time discrepancy had been eliminated.
However, the problems continue.
Time discrepancy - Full load edition
I've changed the DMS settings, so I'll start over with a full load.
After the synchronization is complete, you suddenly realize something.
The time is wrong again!!!!
This time, data was found on the Aurora side that was 9 hours behind the source server.
Upon investigating the situation, it appeared that the records targeted for full load were misaligned. However, the replication data updated during synchronization was not misaligned.
| server | Fully loaded time data (with some discrepancies) | Replicated time data (no discrepancy) |
| on-prem Source | 10:00:00 | 10:05:00 |
| on-prem New Replica | 10:00:00 | 10:05:00 |
| Aurora | 01:00:00 | 10:05:00 |
It doesn't seem like this is a problem that can be solved by changing the value of serverTimeZone.
Regarding the behavior of DMS in relation to the presence or absence of serverTimeZone settings.
Based on the results so far, I will summarize the relationship between the presence or absence of the serverTimeZone setting and the behavior of DMS.
| with serverTimeZone | serverTimeZone is not available. | |
| Full load | No misalignment | There is a discrepancy. |
| Replication | There is a discrepancy. | No misalignment |
The behavior of time discrepancies occurring during full load and CDC replication is reversed depending on whether serverTimezone is present or not.
In other words, when performing a full load + continuous replication as a DMS task, it is clear that a timestamp-type time difference will inevitably occur during one of the synchronization processes.
I suspect this difference arises because, during full load, SQL Reads are used, while during replication, Binlog Reads are used to reference the actual ROW-based updated values.
When reading SQL, the session timezone depends on the DMS settings, and since it defaults to UTC, the value will be correct when calculating the difference if serverTimeZone is set.
During replication, the timezone-inclusive value recorded in the Binlog on the replica server is referenced, and by default, UTC+9 data is referenced and reflected. However, if the serverTimeZone setting is present, an additional 9 hours are added.
I've thought of a few ways to resolve this situation.
- Change the Aurora timezone server variable to UTC (currently UTC+9).
- The task is split into two modes: full load (with serverTimeZone) and continuous replication (without serverTimeZone).
- Changed the type from Timestamp to Datetime.
We considered and verified each method and adopted a method to convert them to Datetime.
| merit | Disadvantages | |
| Change Aurora's timezone variable to UTC. | There is no impact on existing databases. | Ultimately, we want to revert to UTC+9, but the timing for doing so is limited to the transition period (which is difficult to implement as the transition is planned to occur while the service is running). |
| Split the task | There is no impact on existing databases. | The replication task seems to require a definition for each table, which is not practical. |
| Convert to Datetime | DMS synchronization of date and time literals does not cause time discrepancies, regardless of whether it is full load or replication. This can solve the 2038 problem of Timestamp. | The execution time of 0 is unpredictable. |
The reasons for adopting this approach include the fact that using the Datetime type eliminates replication-related discrepancies, and that converting only the on-premises replica server, which is the DMS source, to Datetime eliminates any impact on running services.
Also, although the execution time of the ALTER operation for converting to Datetime is unpredictable, I estimated that it wouldn't take several days because the scale of the database data is not that large.
One concern is that the Timestamp type is internally stored in UTC and converted to the session's timezone when retrieved for display. It was necessary to confirm how the value behaves when converted to Datetime.
Verification revealed that if the session's Timezone is UTC+9 when the Alter command is executed, existing records can be correctly converted from UTC+9 to UTC+9 Datetime, and the time data replicated from the source server during the conversion is also reflected in UTC+9.
Final configuration
By converting to the Datetime type, I was able to successfully perform full load and replication without any inconsistencies.
In conclusion
Explaining replication inconsistencies to a third party is quite complicated...
When migrating a database using DMS, please check if there are any tables containing the Timestamp data type.
If there are any other easy ways to solve this, please let me know.
SRG is looking for new team members.
If you are interested, please contact us here.