Question 390125 in Mysql Question posted in
The official MySQL documentation can be found here.

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:

  • 13:22:31 becomes 112:16:02
  • 13:23:59 becomes -911:43:62 (which is actually below the documented minimum of -838:59:59.999999)

The largest values we have seen so far are:

  • -1023:63:63 (from 13:11:36)
  • 1023:60:01 (from 13:07:51)

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:

  • MariaDB version 10.1.46
  • InnoDB version 5.6.49-89.0
  • mysql56_temporal_format is ON (see documentation)
  • We’re using AWS DMS Serverless, so we have no control over the version
  • DMS destination is Kinesis Data Streams (in JSON format)
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    EDIT: A colleague of mine managed to find a solution/workaround! See this answer instead.

    Well, I just reached a dead end... 😞

    The only possible solution is for Amazon to fix this bug in DMS.

    I tried creating a lookup table by replicating all 86,400 possible values (60 seconds * 60 minutes * 24 hours), and at first it seemed to work... but unfortunately, there is a very significant amount of duplicates in the mapping (due to the lack of precision of the data received), which means that even a programmatic solution would reach the exact same result (because same input = same output).

    Here is a sample of the duplicates: enter image description here

    So for example, when we receive -841:19:63, it is impossible to tell if the correct value is 09:29:39 or 09:30:03. But at least the values are somewhat close together...

    EDIT: Interestingly, all of the duplicate mappings are exactly 24 seconds apart (except the first one):

    enter image description here

    Here is the code I used to generate the table in MariaDB:

    CREATE TABLE my_table AS
SELECT
  CONCAT(hours.val, ':', minutes.val, ':', seconds.val) AS t1,
  CAST(CONCAT(hours.val, ':', minutes.val, ':', seconds.val) AS TIME) AS t2
FROM (
  SELECT LPAD(seq, 2, '0') AS val
  FROM seq_0_to_59
) AS seconds,
(
  SELECT LPAD(seq, 2, '0') AS val
  FROM seq_0_to_59
) AS minutes,
(
  SELECT LPAD(seq, 2, '0') AS val
  FROM seq_0_to_23
) AS hours;

  2. 0

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

    It’s the strangest thing:

    • When the default value of the column is set to NULL, the values are badly converted.
    • But when the default value of the column is set to 00:00:00, then it works!
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search