2023年11月29日发(作者：)

flink读取mysql表中的时间字段ateTi。。。

版本说明

flink 版本 1.13.5

mysql 版本 8.0.26

操作

使⽤mysql table api 读取mysql8中的⼀个表，这个表的时间字段类型是datetime,在创建数据源的时候，对应mysql 的时间类型，设置成

datatime，String 都不⾏，执⾏都报错。

这让我emo好久，找不出问题所在。这应该是mysql的⼀个bug。

具体错误信息

Exception in thread "main" eException: Failed to fetch next result

at sultFromFetcher(:109)

at t(:80)

at esultImpl$CloseableRowIteratorWrapper.hasNext(:370)

at sTableauForm(:152)

at (:160)

at (:69)

Caused by: ption: Failed to fetch job execution result

at umulatorResults(:177)

at (:120)

at sultFromFetcher(:106)

... 5 more

Caused by: ionException: cutionException: Job execution failed.

at Get(:357)

at (:1928)

at umulatorResults(:175)

... 7 more

Caused by: cutionException: Job execution failed.

at xecutionResult(:144)

at $getJobExecutionResult$3(:137)

at ly(:616)

at lyStage(:628)

at ply(:1996)

at ExecutionResult(:134)

at umulatorResults(:174)

... 7 more

Caused by: eption: Recovery is suppressed by NoRestartBackoffTimeStrategy

at Failure(:138)

at lureHandlingResult(:82)

at TaskFailure(:216)

at andleTaskFailure(:206)

at TaskExecutionStateInternal(:197)

at TaskExecutionState(:682)

at TaskExecutionState(:79)

at TaskExecutionState(:435)

at (Unknown Source)

at (:43)

at (:498)

at RpcInvocation(:305)

at RpcMessage(:212)

at RpcMessage(:77)

at Message(:158)

at (:26)

at (:21)

at rElse(:123)

at rElse$(:122)

at rElse(:21)

at lFunction$OrElse.applyOrElse(:171)

at lFunction$OrElse.applyOrElse(:172)

at lFunction$OrElse.applyOrElse(:172)

at lFunction$OrElse.applyOrElse(:172)

at Receive(:517)

at Receive$(:515)

at Receive(:225)

at eMessage(:592)

at (:561)

at sMailbox(:258)

at (:225)

at (:235)

at (:260)

at inPool$WorkQueue.runTask(:1339)

at ker(:1979)

at (:107)

Caused by: astException: ateTime cannot be cast to amp

at (:30)

at sitionBased(:163)

at (:142)

at (:72)

at Operator(:69)

at t(:46)

at t(:26)

at t(:50)

at t(:28)

at SourceContexts$ManualWatermarkContext.processAndCollect(:317)

at SourceContexts$WatermarkContext.collect(:411)

at (:92)

at (:110)

at (:66)

at StreamTask$LegacySourceFunctionThread.run(:269)

解决办法

⽬前定位问题是 mysql8的驱动问题，服务器mysql的版本是8.0.26，我试了驱动 8.0.26 8.0.25 好⼏个版本，最后发现低版本的可以解

决这个问题，我使⽤mysql 8.0.16 ，设置时间类型为 timestamp 就可以查询了。

复现测试

public class ReadsMysql {

public static void main(String[] args) throws Exception {

// 连接mysql 数据源

String sourceDDL = "CREATE TABLE c_ex_stock (n" +

" EndDate TIMESTAMP,n" +

" InnerCode INT,n" +

" StatisPeriod INT,n" +

" StatisType VARCHAR,n" +

" SecuCode VARCHAR,n" +

" SecuAbbr VARCHARn" +

" CalDate TIMESTAMPn" +

") WITH (n" +

"'' = 'jdbc'," +

"'' = 'jdbc:mysql://.120.201:3306/fl?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL

=true&serverTimezone=GMT&2B8'," +

"'' = ''," +

"'' = 'c_ex_stock'," +

" 'me' = 'xxx'," +

" 'rd' = 'xxxx'"+

")";

EnvironmentSettings settings = EnvironmentSettings

.newInstance()

.inStreamingMode()

.useBlinkPlanner()

.build();

StreamTableEnvironment tEnv = (env, settings);

// 注册source和sink

eSql(sourceDDL);

// 数据提取

Table sourceTable = ("c_ex_stock");

TableResult tableResult = eSql("select * from " + sourceTable);

();

// 执⾏作业

e("flink-read-mysql");

}

}