[FLINK-21240] incompatible datetime with MySql JDBC Driver 8.0.23

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.12.1
• Fix Version/s: 1.13.0
• Component/s: Connectors / JDBC, Table SQL / Runtime
• Labels: pull-request-available
• Assignee: Leonard Xu
• Reporter: macdoor615
• Created: 02/Feb/21 12:34
• Updated: 28/May/21 09:00
• Resolved: 08/Apr/21 06:48

Description

1. upgrade mysql JDBC Driver from mysql-connector-java-8.0.22.jar to mysql-connector-java-8.0.23.jar 

2. create table on mysql

CREATE TABLE `p_port_packet_loss_5m` (
  `id` binary(16) NOT NULL,
  `coltime` datetime NOT NULL,
...

3. create table in Flink SQL client 
create table if not exists p_port_packet_loss_5m
(
  id bytes,
  coltime timestamp,
...)
WITH (
  'connector' = 'jdbc',
  'url' = 'jdbc:mysql://ip:port/mydatabase?rewritebatchedstatements=true',

...)

4. query this table in flink sql client
select * from p_port_packet_loss_5m;

5. prompt error

java.lang.ClassCastException: java.time.LocalDateTime cannot be cast to java.sql.Timestamp

    at org.apache.flink.connector.jdbc.internal.converter.AbstractJdbcRowConverter.lambda$createInternalConverter$ff586f13$9(AbstractJdbcRowConverter.java:170)

    at org.apache.flink.connector.jdbc.internal.converter.AbstractJdbcRowConverter.lambda$wrapIntoNullableInternalConverter$83a7e581$1(AbstractJdbcRowConverter.java:127)

    at org.apache.flink.connector.jdbc.internal.converter.AbstractJdbcRowConverter.toInternal(AbstractJdbcRowConverter.java:78)

    at org.apache.flink.connector.jdbc.table.JdbcRowDataInputFormat.nextRecord(JdbcRowDataInputFormat.java:265)

    at org.apache.flink.connector.jdbc.table.JdbcRowDataInputFormat.nextRecord(JdbcRowDataInputFormat.java:55)

    at org.apache.flink.streaming.api.functions.source.InputFormatSourceFunction.run(InputFormatSourceFunction.java:90)

    at org.apache.flink.streaming.api.operators.StreamSource.run(StreamSource.java:110)

    at org.apache.flink.streaming.api.operators.StreamSource.run(StreamSource.java:66)

    at org.apache.flink.streaming.runtime.tasks.SourceStreamTask$LegacySourceFunctionThread.run(SourceStreamTask.java:241)

 

6. downgrade to mysql-connector-java-8.0.22.jar , same procedure, no error

Issue Links

• links to: GitHub Pull Request #15506