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

["Martijn Visser (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/FLINK-30771?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17679853#comment-17679853 ] 

Martijn Visser commented on FLINK-30771:
----------------------------------------

[~solofire] Thanks for opening the ticket. For me it's not clear what is the outcome of the 2nd function, is that the result of the first function prior to 8.023 or the result of the first function with 8.023 and higher? 

> 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
>            Priority: Major
>         Attachments: my_issue.sql
>
>   Original Estimate: 504h
>  Remaining Estimate: 504h
>
> 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)