nzw921rx opened a new issue, #13024:
URL: https://github.com/apache/seatunnel/issues/13024
### Search before asking
Searched existing issues and pull requests. Related reports such as #12884
concern MySQL CDC TIMESTAMP values written to Doris; this report concerns MySQL
JDBC DATETIME values written to a MySQL JDBC sink.
### What happened
MySQL `DATETIME` maps to SeaTunnel `TIMESTAMP` / `LocalDateTime`. Its date
and time fields can shift silently when written through JDBC if the execution
JVM default time zone differs from the JDBC connection time zone.
The reported version comparison is:
| JVM time zone | JDBC serverTimezone | 2.3.13 | 3.1.0 |
|---|---|---|---|
| Asia/Shanghai | UTC | Exact | -8 hours, 5,000/5,000 rows |
| UTC | Asia/Shanghai | Exact | +8 hours, 5,000/5,000 rows |
| Asia/Shanghai | Asia/Shanghai | Exact | Exact |
| UTC | UTC | Exact | Exact |
This table is the reported job-level comparison. Independent connector-level
verification of the affected dev code is described below; the 2.3.13 job was
not independently rerun during that verification.
### SeaTunnel version and verification environment
- Reported comparison: 2.3.13 versus 3.1.0.
- Independently reproduced with production converter code from dev commit
`8f859a49234b4eccf4600e3bf385444e24d668db` (3.1.0-SNAPSHOT).
- MySQL Server: 8.0.43.
- MySQL Connector/J: 8.0.27, the version declared by the JDBC module.
- Java: OpenJDK 17.0.20.1.
- Both client-side and server-side prepared statements were exercised.
### Relevant configuration and reproduction
The full original engine job configuration is unavailable. The independent
reproduction calls the production `MysqlJdbcRowConverter.toInternal` and
`toExternal` methods against a real MySQL instance.
For the -8 hour case, use:
```text
Execution JVM: -Duser.timezone=Asia/Shanghai
Source JDBC:
jdbc:mysql://HOST:3306/ntz_test?serverTimezone=UTC&preserveInstants=true
Sink JDBC:
jdbc:mysql://HOST:3306/ntz_test?serverTimezone=UTC&preserveInstants=true
```
Prepare tables and an independently inserted local value:
```sql
CREATE TABLE ntz_src (id INT PRIMARY KEY, dt DATETIME(6));
CREATE TABLE ntz_dst (id INT PRIMARY KEY, dt DATETIME(6));
INSERT INTO ntz_src VALUES (1, '2026-01-01 12:34:56.123456');
```
Read `SELECT dt FROM ntz_src WHERE id = 1`, convert that field as SeaTunnel
`LocalTimeType.LOCAL_DATE_TIME_TYPE`, and bind it through the JDBC converter to
`INSERT INTO ntz_dst (id, dt) VALUES (1, ?)`:
```java
TableSchema schema =
TableSchema.builder()
.column(
PhysicalColumn.builder()
.name("dt")
.dataType(LocalTimeType.LOCAL_DATE_TIME_TYPE)
.build())
.build();
MysqlJdbcRowConverter converter = new MysqlJdbcRowConverter();
// sourceResultSet is positioned on the source row;
// insertStatement is the prepared INSERT above.
SeaTunnelRow row = converter.toInternal(sourceResultSet, schema);
converter.toExternal(schema, row, insertStatement);
insertStatement.executeUpdate();
```
Check the database value independently of Java timestamp conversion:
```sql
SELECT DATE_FORMAT(dt, '%Y-%m-%d %H:%i:%s.%f') FROM ntz_dst WHERE id = 1;
```
Expected: `2026-01-01 12:34:56.123456`.
Affected JVM Asia/Shanghai / JDBC UTC path: `2026-01-01 04:34:56.123456`.
Reverse the JVM and JDBC zones to reproduce the opposite direction.
### Independent verification
The production-converter reproduction used five JVM/connection combinations:
UTC/UTC, UTC/Asia-Shanghai, Asia-Shanghai/UTC, Asia-Shanghai/Asia-Shanghai, and
America-New-York/UTC, each with both prepared-statement modes.
```text
Cases: 10
Rows: 60
Source read mismatches: 0
Sink value mismatches: 30
```
Each case contained five non-null DATETIME values and one null.
Matching-zone cases passed; all five non-null values shifted in each
mismatching-zone case. The null remained null. These are connector-level
database checks, not a full SeaTunnel engine E2E run.
### Relevant code / suspected cause
Related change: #10724, merged as `872077f6480ade460efca5501a3847fa2703e681`.
The shared JDBC TIMESTAMP read branch changed from
`getTimestamp(...).toLocalDateTime()` to
`JdbcFieldTypeUtils.getLocalDateTime(...)`, whose preferred path is the typed
`LocalDateTime` getter. The ordinary TIMESTAMP writer retained:
```java
statement.setTimestamp(statementIndex, Timestamp.valueOf(localDateTime));
```
`Timestamp.valueOf` uses the JVM default time zone; the driver subsequently
serializes the timestamp using its effective connection time zone.
This is a suspected regression mechanism identified by code inspection. A
before/after-commit bisect has not been performed.
### Workaround
For affected DATETIME jobs, align the effective source and sink JDBC
connection time zones with the JVM default time zone on every execution node,
for example all UTC or all Asia/Shanghai. Both matching combinations passed the
reported comparison and the independent converter checks.
### Error exception
No exception is required: rows can be written successfully with shifted
values.
### Scope
This report is limited to the verified MySQL DATETIME / SeaTunnel TIMESTAMP
path. It does not establish the same regression for MySQL TIMESTAMP / SeaTunnel
TIMESTAMP_TZ, CDC sources, OceanBase, or other JDBC dialects.
Other drivers may support different temporal binding APIs and need separate
verification. The report does not propose changing the shared JDBC default for
every dialect or treating untested databases as confirmed affected.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
