[Bug]: MySQL TIMESTAMP values fail to apply due to ISO offset formatting in Datastream-to-SQL template

[mrkrizic]

Related Template(s)

datastream-to-sql

Template Version

2025-10-21-00_rc00

What happened?

Summary

When using the Cloud Datastream → Dataflow (Datastream-to-SQL flex template) → Cloud SQL (MySQL) pipeline, CDC rows that include MySQL TIMESTAMP columns fail during the Dataflow sink step. The template formats timestamps with DateTimeFormatter.ISO_OFFSET_DATE_TIME, producing strings like 2025-08-01T09:15:00Z. Cloud SQL’s MySQL engine rejects that representation with a data truncation error.

Steps to Reproduce

Source MySQL table with a column of type TIMESTAMP and a valid value e.g. 2025-08-01 09:15:00.
Set up Datastream to capture binlog changes and land them in GCS.
Launch the Datastream-to-SQL Flex template with databaseType set to mysql, pointing to a Cloud SQL MySQL 8.0 instance.
Allow Datastream to publish an insert into that table so the Avro payload contains:

{"name":"creation_date","type":["null",{"type":"long","logicalType":"timestamp-micros"}],"default":null}
(The encoded value decodes to 2025-08-01T09:15:00Z.)

Observe the Dataflow job logs as the row is processed.
Expected Behavior
The Dataflow template should convert the Avro logical timestamp to a MySQL-compatible literal (2025-08-01 09:15:00) and insert it successfully.

Actual Behavior

Dataflow retries the mutation multiple times and ultimately fails with:

SQLException Occurred: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation:Data truncation: Incorrect datetime value: '2025-08-01T09:15:00Z' for column 'creation_date'

This happens even when the Cloud SQL instance’s sql_mode excludes strict defaults (ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION), and manual inserts via Cloud SQL Studio succeed.

Diagnosis

The template uses DEFAULT_TIMESTAMP_WITH_TZ_FORMATTER, which in turn relies on DateTimeFormatter.ISO_OFFSET_DATE_TIME. MySQL TIMESTAMP columns expect the pattern yyyy-MM-dd HH:mm:ss (per MySQL docs, range 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07). The offset/Z-suffix format is therefore rejected by the MySQL JDBC driver.

Proposed Fix

When databaseType = mysql, format Avro logical timestamps with DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss") (or the equivalent MySQL-safe formatter) before issuing the write. All other database types can retain the current formatter.

Environment

Source: External MySQL (binlog captured by Datastream)
Destination: Cloud SQL for MySQL 8.0
Dataflow Template: gs://dataflow-templates-/latest/flex/Cloud_Datastream_to_SQL
SQL mode overrides tested: ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION

Additional Information

I cannot give the full error log, since it contains sensitive data. The attached exception log should suffice to verify a successful reproduction of the error.

Relevant log output

SQLException Occurred: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2025-08-01T09:15:00Z' for column 'creation_date' at row 1 while executing statement: INSERT INTO ...