[FLINK-30771] different performance of conversion between datetime type in mysql and timestamp in flink

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.16.0
• Component/s: Connectors / JDBC

Environment

System timezone is UTC and mysql version is 5.7.37;
my laptap is window and i build my flink in wsl 2(Windows Subsystem for Linux) ;
my mysql client is dbeaver;

• Flags: Important

Language

sql

• Assignee: Unassigned
• Reporter: jingjing dong
• Created: 23/Jan/23 15:08
• Updated: 29/Jan/23 09:30

Description

in the attachment below named 'my_issue.sql', you can repeat my problem in eight steps。in simple terms，i think the conversion between datetime type in mysql and timestamp in flink need redefine carefully . after the research of source code ，i find something as below:

1. the timestime type in flink represent localdatetime in java
2. in  class 'com.mysql.cj.jdbc.result.ResultSetImpl' , there are two overloaded function :function one is  'Object getObject(int columnIndex)' , function two is '<T> T getObject(int columnIndex, Class<T> type)' . the chosen is function one by flink ,however the most popular ORM project like JPA/Mybatis choose function two.
3. since version  8.0.23 of mysql connector ，the function one has been refactored. and this refactor affect the conversion from datetime type in mysql to localdatetime in java. this conversion means fetch data from source mysql .
4. when the version of mysql connector is less than or equal with 8.0.22, the conversion is affected by the JDBC connection property "serverTimezone" which is defined in the WITH clause in the CREATE TABLE statment.
5. But when the version is greater than or equal with 8.0.23 , the result is different
6. in class 'org.apache.flink.connector.jdbc.internal.converter.AbstractJdbcRowConverter' , i am not sure if the localdatime type is cast into timestamp type, However i am pretty sure that  no matter the version of mysql connector ,the conversion from  localdatetime in java to datetime type in mysql is always affected by the JDBC connection property "serverTimezone" which is defined in the WITH clause in the CREATE TABLE statment.  this conversion means sink data to mysql .

       in my option ,i suggest we should refer to the ORM project , neither the fetch from mysql or sink to mysql proccess should affect by the property "serverTimezone" .