v2.0.0-rt.4 creating mysql cdc source tables failed during wide table test

[cyliu0]

Describe the bug

https://buildkite.com/risingwave-test/wide-table/builds/107#0191e434-7017-47a6-863a-098f2ece810a

[WARN] Using S3 as the storage backend! MinIO deployment is forcefully disabled!
--
  | [INFO] [workspace/k8s] [namespace: wide-table-op9kn] Happy benchmarking!
  | Generating realtime_waybill_detail data
  | Generating realtime_waybill_cost_fee data
  | Creating realtime_waybill_detail table
  | mysql: [Warning] Using a password on the command line interface can be insecure.
  | Creating realtime_waybill_cost_fee table
  | mysql: [Warning] Using a password on the command line interface can be insecure.
  | Creating RW source and tables
  | CREATE_SOURCE
  | ERROR:  Failed to run the query
  |  
  | Caused by these errors (recent errors listed first):
  | 1: Expr error
  | 2: Unsupported function: cast(timestamp with time zone) -> timestamp without time zone
  |  
  | ERROR:  Failed to run the query
  |  
  | Caused by these errors (recent errors listed first):
  | 1: Expr error
  | 2: Unsupported function: cast(timestamp with time zone) -> timestamp without time zone

Error message/log

No response

To Reproduce

No response

Expected behavior

No response

How did you deploy RisingWave?

No response

The version of RisingWave

v2.0.0-rt.4

Additional context

No response

[lmatz]

it worked before? sounds like a breaking change

[StrikeW]

cc @xiangjinwu to take a look. btw @cyliu0 can provide the table schema to you which is a customer scenario.

[xiangjinwu]

Note the following naming differences between MySQL and PostgreSQL (RisingWave):

• MySQL datetime is PostgreSQL timestamp (without time zone).
• MySQL timestamp is PostgreSQL timestamptz, which is number of s/ms/us passed since 1970-01-01 00:00:00 in UTC.

Unsupported function: cast(timestamp with time zone) -> timestamp without time zone

This looks like we are trying to insert a timestamptz (MySQL timestamp) into RisingWave timestamp, but there is no context on which time zone to use for this conversion. Is the conversion expected or accidental?

[cyliu0]

@xiangjinwu But we have run this case for the past months. It never failed because of this. You can check the build history on the buildkite.

And we never use MySQL timestamp in the upstream tables according to the upstream table schemas https://github.com/risingwavelabs/kube-bench/blob/main/manifests/wide-table/wide-table-sql.template.yaml#L7-L1246.

And there is no timestamptz in source table schemas as well. You can check the source tables in the same file above.

[xiangjinwu]

Thanks for sharing the schema, which is mapping from MySQL datetime to RisingWave timestamp (without time zone) correctly. There should be no timestamp with time zone at all during this test. @StrikeW Is there any related change in MySQL CDC logic in the past 2 months?

[lmatz]

becaue 2.0.0-rc.1 passed the test, I suppose the buggy commit is in the following range:
v2.0.0-rc.1...release-2.0

[lmatz]

Since @xiangjinwu reproduced the bug on v2.0.0-rc.1 locally, please ignore the thread above.

[lmatz]

In the following range between v2.0.0-rt.3 and v2.0.0-rc.1
dc8faba...v2.0.0-rc.1

[cyliu0]

After #18322, we can't define column in CDC tables like this update_time timestamp DEFAULT CURRENT_TIMESTAMP,. The correct definition should be update_time timestamp DEFAULT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Singapore', -- or UTC. We may support the former one in the future like MySQL and PG. But for now, we fixed our test cases to adapt to this in https://github.com/risingwavelabs/kube-bench/pull/457

[xiangjinwu]

#18513