Odd value for Debezium Server __source_ts_ms for __op=r

[Liudas Sodonis]

Hello,

I have a process where debezium server is streaming data to GCP Pub/Sub.

The config has the unwrap section that adds extra fields, like this: 

debezium.source.transforms=unwrap

debezium.source.transforms.unwrap.type=io.debezium.transforms.ExtractNewRecordState

debezium.source.transforms.unwrap.delete.handling.mode=rewrite

debezium.source.transforms.unwrap.add.fields=op,table,source.ts_ms

debezium.source.transforms.unwrap.add.headers=db

So I get extra cols for __op, __table and __source_ts_ms.

It is odd that __source_ts_ms for initial load (the "r" event) value is not right (?) and set in the future. But the update (the "u") has the correct time value. 

I wonder if this a bug or my misconfiguration? 

For the docs https://debezium.io/documentation/reference/2.2/connectors/sqlserver.html I understand that value suppose to be current time (not future one) 

ts_ms - 

Optional field that displays the time at which the connector processed the event. The time is based on the system clock in the JVM running the Kafka Connect task.

In the source object, ts_ms indicates the time that the change was made in the database. By comparing the value for payload.source.ts_ms with the value for payload.ts_ms, you can determine the lag between the source database update and Debezium.

My system:
- Mac OS 13.2.1.

- The debezium version - debezium-server-dist-2.1.3.Final-runner.

- Java: 

   openjdk version "19.0.2" 2023-01-17 
   OpenJDK Runtime Environment Homebrew (build 19.0.2)
   OpenJDK 64-Bit Server VM Homebrew (build 19.0.2, mixed mode, sharing)

But I noticed same behavior on other systems (debian server) as well. 

Any help will be highly appreciated,

Thank you,

Liudas

[Chris Cranford]

Hi Liudas,

The difference appears to be exactly 7 hours, is it possible your timezone is UTC+7?

Chris

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/2fd39a9c-5b38-452e-ac7e-7005ea8da7d1n%40googlegroups.com.

[Liudas Sodonis]

I'm in the UTC+8. However this don't explain why read event timestamp is set to future. 

[Chris Cranford]

Hi Liudas,

I suspect its a result of a timezone problem because you are UTC+8.  Could you please raise a Jira [1] on this, providing all the details including your configuration, the table DDL, etc.

Thanks,
Chris

[1]: https://issues.redhat.com/projects/DBZ/issues

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/cabdeaf2-9e7f-4701-bb95-797a0fcd8671n%40googlegroups.com.

[Liudas Sodonis]

Hello Chris,

Created https://issues.redhat.com/browse/DBZ-6388 

I'm surprised nobody else has this issue. 

[Liudas Sodonis]

Found a workaround! 

Chris you gave me ideas, really appreciate that!

Since you mentioned timezone I looked how to set tz for jar.

Basically I need to update the run.sh and add the -Duser.timezone=GMT bit, like this:

exec "$JAVA_BINARY" -Duser.timezone=GMT $DEBEZIUM_OPTS $JAVA_OPTS -cp "$RUNNER"$PATH_SEP"conf"$PATH_SEP$LIB_PATH io.debezium.server.Main

The confusing part that it seems only to be working with GMT time, not the UTC or America/Los_Angeles and so on.

The tz is not that important, important part is that is working predictable way.