jingjing dong created FLINK-30771:
-------------------------------------
Summary: different performance of conversion between datetime type
in mysql and timestamp in flink
Key: FLINK-30771
URL: https://issues.apache.org/jira/browse/FLINK-30771
Project: Flink
Issue Type: Bug
Components: Connectors / JDBC
Affects Versions: 1.16.0
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;
Reporter: jingjing dong
Fix For: 1.16.1
Attachments: my_issue.sql
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:
# the timestime type in flink represent localdatetime in java
# 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.
# 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 .
# 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.
# But when the version is greater than or equal with 8.0.23 , the result is
different
# 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" .
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
