mysqltimemariadbaws-dms

How to handle MySQL/MariaDB TIME columns in Amazon DMS

We're experiencing issues when using Amazon DMS for ongoing replication of a MySQL/MariaDB database.

The issue specifically happens with TIME columns, whose values get garbled like this:

The largest values we have seen so far are:

But oddly enough, -1023:32:00 comes from 00:20:48. There seems to be no pattern at all... 😞

It happens consistently with all TIME columns, so it isn't due to some other problem. My guess is that it has to do with DMS being unable to properly read the format of the TIME columns from the binlog.

Is there any way to convert those buggy values back to what they should be? We have full control over the values and can run code to perform the conversion (Python or JS would be best, but we'll take anything). But given how the times above are so close and result in such vastly different representations, my hopes are quite low...

This issue seems related, but there is still no resolution or workaround. In their case, it seems like the values get truncated to 00:00:00 when negative, and 23:59:59 when they are too large.

Specs:

Solution

  • EDIT/TL;DR: the columns have to be altered in order to use the new internal temporal storage format. Details are below.

    A colleague of mine managed to find a solution/workaround!

    It's the strangest thing:

    UPDATE: For older tables, we also had to update the internal temporal storage format as described here:

    In MariaDB 10.1.2 a new temporal format was introduced from MySQL 5.6 that alters how the TIME, DATETIME and TIMESTAMP columns operate at lower levels.

    Tables that include TIMESTAMP values that were created on an older version of MariaDB or that were created while the mysql56_temporal_format system variable was disabled continue to store data using the older data type format.

    In order to update table columns from the older format to the newer format, execute an ALTER TABLE... MODIFY COLUMN statement that changes the column to the same data type.

    When MariaDB executes the ALTER TABLE statement, it converts the data from the older temporal format to the newer one.

    We didn't have to modify the columns at all, but simply set them to their current definition. For example:

    ALTER TABLE my_table MODIFY COLUMN `time_col` TIME DEFAULT '00:00:00' NOT NULL;

    UPDATE 2: Actually, it looks like it has absolutely nothing to do with the default value of the column (which is reassuring). It's entirely related to the internal temporal storage format.

    The issue that caused confusion is that somehow, even though mysql56_temporal_format is ON, new tables are still being created with the old format (there's no way to tell the difference on our version of MariaDB, unfortunately) and still require an ALTER TABLE... MODIFY COLUMN command to switch to the new storage format.

    So we have to create the table, and right away alter it. 🤷‍♂️