flink读取mysql表中的时间字段error java.time.LocalDateTime cannot be cast to java.sql.Timestamp

已于 2023-04-05 09:04:16 修改
阅读量3.8k 收藏 1
点赞数 1
分类专栏： flink 文章标签： mysql flink mysql时间和flink映射
于 2022-02-21 16:08:04 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43975771/article/details/123049377
版权

版本说明
flink 版本 1.13.5
mysql 版本 8.0.26

操作

使用mysql table api 读取mysql8中的一个表，这个表的时间字段类型是datetime,在创建数据源的时候，对应mysql 的时间类型，设置成 datatime，String 都不行，执行都报错。
这让我emo好久，找不出问题所在。这应该是mysql的一个bug。

具体错误信息

Exception in thread "main" java.lang.RuntimeException: Failed to fetch next result
	at org.apache.flink.streaming.api.operators.collect.CollectResultIterator.nextResultFromFetcher(CollectResultIterator.java:109)
	at org.apache.flink.streaming.api.operators.collect.CollectResultIterator.hasNext(CollectResultIterator.java:80)
	at org.apache.flink.table.api.internal.TableResultImpl$CloseableRowIteratorWrapper.hasNext(TableResultImpl.java:370)
	at org.apache.flink.table.utils.PrintUtils.printAsTableauForm(PrintUtils.java:152)
	at org.apache.flink.table.api.internal.TableResultImpl.print(TableResultImpl.java:160)
	at com.quant.flowcalculation.flinkapi.msql.ReadsMysql.main(ReadsMysql.java:69)
Caused by: java.io.IOException: Failed to fetch job execution result
	at org.apache.flink.streaming.api.operators.collect.CollectResultFetcher.getAccumulatorResults(CollectResultFetcher.java:177)
	at org.apache.flink.streaming.api.operators.collect.CollectResultFetcher.next(CollectResultFetcher.java:120)
	at org.apache.flink.streaming.api.operators.collect.CollectResultIterator.nextResultFromFetcher(CollectResultIterator.java:106)
	... 5 more
Caused by: java.util.concurrent.ExecutionException: org.apache.flink.runtime.client.JobExecutionException: Job execution failed.
	at java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:357)
	at java.util.concurrent.CompletableFuture.get(CompletableFuture.java:1928)
	at org.apache.flink.streaming.api.operators.collect.CollectResultFetcher.getAccumulatorResults(CollectResultFetcher.java:175)
	... 7 more
Caused by: org.apache.flink.runtime.client.JobExecutionException: Job execution failed.
	at org.apache.flink.runtime.jobmaster.JobResult.toJobExecutionResult(JobResult.java:144)
	at org.apache.flink.runtime.minicluster.MiniClusterJobClient.lambda$getJobExecutionResult$3(MiniClusterJobClient.java:137)
	at java.util.concurrent.CompletableFuture.uniApply(CompletableFuture.java:616)
	at java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:628)
	at java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:1996)
	at org.apache.flink.runtime.minicluster.MiniClusterJobClient.getJobExecutionResult(MiniClusterJobClient.java:134)
	at org.apache.flink.streaming.api.operators.collect.CollectResultFetcher.getAccumulatorResults(CollectResultFetcher.java:174)
	... 7 more
Caused by: org.apache.flink.runtime.JobException: Recovery is suppressed by NoRestartBackoffTimeStrategy
	at org.apache.flink.runtime.executiongraph.failover.flip1.ExecutionFailureHandler.handleFailure(ExecutionFailureHandler.java:138)
	at org.apache.flink.runtime.executiongraph.failover.flip1.ExecutionFailureHandler.getFailureHandlingResult(ExecutionFailureHandler.java:82)
	at org.apache.flink.runtime.scheduler.DefaultScheduler.handleTaskFailure(DefaultScheduler.java:216)
	at org.apache.flink.runtime.scheduler.DefaultScheduler.maybeHandleTaskFailure(DefaultScheduler.java:206)
	at org.apache.flink.runtime.scheduler.DefaultScheduler.updateTaskExecutionStateInternal(DefaultScheduler.java:197)
	at org.apache.flink.runtime.scheduler.SchedulerBase.updateTaskExecutionState(SchedulerBase.java:682)
	at org.apache.flink.runtime.scheduler.SchedulerNG.updateTaskExecutionState(SchedulerNG.java:79)
	at org.apache.flink.runtime.jobmaster.JobMaster.updateTaskExecutionState(JobMaster.java:435)
	at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.flink.runtime.rpc.akka.AkkaRpcActor.handleRpcInvocation(AkkaRpcActor.java:305)
	at org.apache.flink.runtime.rpc.akka.AkkaRpcActor.handleRpcMessage(AkkaRpcActor.java:212)
	at org.apache.flink.runtime.rpc.akka.FencedAkkaRpcActor.handleRpcMessage(FencedAkkaRpcActor.java:77)
	at org.apache.flink.runtime.rpc.akka.AkkaRpcActor.handleMessage(AkkaRpcActor.java:158)
	at akka.japi.pf.UnitCaseStatement.apply(CaseStatements.scala:26)
	at akka.japi.pf.UnitCaseStatement.apply(CaseStatements.scala:21)
	at scala.PartialFunction.applyOrElse(PartialFunction.scala:123)
	at scala.PartialFunction.applyOrElse$(PartialFunction.scala:122)
	at akka.japi.pf.UnitCaseStatement.applyOrElse(CaseStatements.scala:21)
	at scala.PartialFunction$OrElse.applyOrElse(PartialFunction.scala:171)
	at scala.PartialFunction$OrElse.applyOrElse(PartialFunction.scala:172)
	at scala.PartialFunction$OrElse.applyOrElse(PartialFunction.scala:172)
	at akka.actor.Actor.aroundReceive(Actor.scala:517)
	at akka.actor.Actor.aroundReceive$(Actor.scala:515)
	at akka.actor.AbstractActor.aroundReceive(AbstractActor.scala:225)
	at akka.actor.ActorCell.receiveMessage(ActorCell.scala:592)
	at akka.actor.ActorCell.invoke(ActorCell.scala:561)
	at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:258)
	at akka.dispatch.Mailbox.run(Mailbox.scala:225)
	at akka.dispatch.Mailbox.exec(Mailbox.scala:235)
	at akka.dispatch.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
	at akka.dispatch.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
	at akka.dispatch.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
	at akka.dispatch.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
Caused by: java.lang.ClassCastException: java.time.LocalDateTime cannot be cast to java.sql.Timestamp
	at org.apache.flink.api.common.typeutils.base.SqlTimestampSerializer.copy(SqlTimestampSerializer.java:30)
	at org.apache.flink.api.java.typeutils.runtime.RowSerializer.copyPositionBased(RowSerializer.java:163)
	at org.apache.flink.api.java.typeutils.runtime.RowSerializer.copy(RowSerializer.java:142)
	at org.apache.flink.api.java.typeutils.runtime.RowSerializer.copy(RowSerializer.java:72)
	at org.apache.flink.streaming.runtime.tasks.CopyingChainingOutput.pushToOperator(CopyingChainingOutput.java:69)
	at org.apache.flink.streaming.runtime.tasks.CopyingChainingOutput.collect(CopyingChainingOutput.java:46)
	at org.apache.flink.streaming.runtime.tasks.CopyingChainingOutput.collect(CopyingChainingOutput.java:26)
	at org.apache.flink.streaming.api.operators.CountingOutput.collect(CountingOutput.java:50)
	at org.apache.flink.streaming.api.operators.CountingOutput.collect(CountingOutput.java:28)
	at org.apache.flink.streaming.api.operators.StreamSourceContexts$ManualWatermarkContext.processAndCollect(StreamSourceContexts.java:317)
	at org.apache.flink.streaming.api.operators.StreamSourceContexts$WatermarkContext.collect(StreamSourceContexts.java:411)
	at org.apache.flink.streaming.api.functions.source.InputFormatSourceFunction.run(InputFormatSourceFunction.java:92)
	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: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 VARCHAR\n" +
                "  CalDate TIMESTAMP\n" +
                ") WITH (\n" +
                "'connector.type' = 'jdbc'," +
                "'connector.url' = 'jdbc:mysql://xx.xx.120.201:3306/fl?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT&2B8'," +
                "'connector.driver' = 'com.mysql.cj.jdbc.Driver'," +
                "'connector.table' = 'c_ex_stock'," +
                " 'connector.username' = 'xxx'," +
                " 'connector.password' = 'xxxx'"+
                ")";

        EnvironmentSettings settings = EnvironmentSettings
                .newInstance()
                .inStreamingMode()
                .useBlinkPlanner()
                .build();

        StreamTableEnvironment tEnv = StreamTableEnvironment.create(env, settings);

        // 注册source和sink
        tEnv.executeSql(sourceDDL);
 
        // 数据提取
        Table sourceTable = tEnv.from("c_ex_stock");

        TableResult tableResult = tEnv.executeSql("select * from " + sourceTable);
        tableResult.print();

        // 执行作业
        env.execute("flink-read-mysql");
    }
}
笔墨新城
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
专栏目录
flink-sql-connector-mysql-cdc-2.3.0.jar
07-05
flink同步mysql数据库所用包
flink-sql-connector-mysql-cdc-2.5-SNAPSHOT.jar
08-15
flink-sql-connector-mysql-cdc 2.5-SNAPSHOT
【源码改造】flink JDBC connector 源码改造之 类型转换 java.time.LocalDateTime cannot be cast to java.sql.Timestamp
hiliang521的博客
06-12 3905
版本：flink 1.12.5 mysql 8.0.28 由于mysql 驱动版本更新，导致flink在消费mysql数据时不能正确将datetime类型数据转换为flink的Timestamp。
springboot返回对象 java.time.LocalDateTime cannot be cast to java.sql.Timestamp
qq690452074的专栏
08-25 1428
java.time.LocalDateTime cannot be cast to java.sql.Timestamp
已解决java.lang.RuntimeException: 运行时异常的正确解决方法，亲测有效！！！
最新发布
小明的Java问道之路
04-26 3727
已解决java.lang.RuntimeException: 运行时异常的正确解决方法，亲测有效！！！
SpringBoot报错java.time.LocalDateTime cannot be cast to java.sql.Timestamp] with root cause
qq_40898909的博客
09-15 5819
解决SpringBoot升级导致的代码报错java.time.LocalDateTime cannot be cast to java.sql.Timestamp] with root cause。
shardingsphere+mybatis LocalDateTime转换报错java.time.LocalDateTime cannot be cast to java.sql.Timestamp
子休的博客
03-20 748
重写LocalDateTimeTypeHandler，解决shardingsphere + mybatis LocalDateTime转换报错java.time.LocalDateTime cannot be cast to java.sql.Timestamp的问题；java 实体类中的字段类型为LocalDateTime，mysql 表中字段类型为datetime。未使用shardingsphere之前一切正常，使用shardingsphere之后才出现的这个错误。
java.sql.Timestamp cannot be cast to java.sql.Date
热门推荐
Calf's Blog
05-10 1万+
 java.lang.ClassCastException: java.sql.Timestamp cannot be cast to java.sql.Date我在往数据库插入数据时用的是string型,SQL应该会转换为java.sql.Date型,但是取出值的时候不也应该是java.sql.Date型么?但是这么会报这样的错误?还有一个问题,用你说的这种方式生成的java.sql
java.lang.NoClassDefFoundError错误解决办法
08-30
"java.lang.NoClassDefFoundError错误解决办法" java.lang.NoClassDefFoundError错误是一种常见的Java错误，它发生在Java虚拟机在编译时能找到合适的类，而在运行时不能找到合适的类导致的错误。下面是该错误的解决...
Flink 中文文档资料.zip
01-17
flink Flink 中文文档资料.zipFlink 中文文档资料.zipFlink 中文文档资料.zipFlink 中文文档资料.zipFlink 中文文档资料.zipFlink 中文文档资料.zipFlink 中文文档资料.zipFlink 中文文档资料.zipFlink 中文文档资料...
java.net.ConnectException: Connection refused问题解决办法
09-01
主要介绍了java.net.ConnectException: Connection refused问题解决办法的相关资料,需要的朋友可以参考下
flowable报错java.time.LocalDateTime cannot be cast to java.lang.String
茴香豆的博客
11-11 3788
floable启动时报错解决方案
java.sql.Timestamp cannot be cast to java.sql.Date求助
yzhjisji的专栏
06-30 1712
java.lang.ClassCastException: java.sql.Timestamp cannot be cast to java.sql.Date百度上搜到以下内容:这两天一直被这个问题困扰着，总是出现这个异常，找网上又好像搜索不到解决的方案，接着研究了N久之后，发现原来是java.sql.Date和java.util.Date的原因……总算解决了我在百度上搜到以
Caused by: java.lang.ClassCastException: java.time.LocalDateTime cannot be cast to java.util.Date
The_Boy_le的博客
10-23 1653
已经解决了我的问题，希望对你们也有用。
java中报错java.sql.Timestamp cannot be cast to java.sql.Date
weixin_34349320的博客
05-21 386
解决办法：进行类型转换java.sql.Date--->java.sql.Timestampnew java.sql.Timestamp(yourDate.getTime());java.sql.Timestamp-->java.sql.Datenew java.sql.Date(yourTimestamp.getTime());原帖地址：http://blog....
java异常：oracle.sql.TIMESTAMP cannot be cast to java.sql.Timestamp
qq_30434271的博客
06-15 2739
原因：会出现此异常，是由于内存中的对象类型与你想转换出来的时间戳类型不一致造成的， 解决办法：1.网上有种解决办法是通过设置系统属性oracle.jdbc.J2EE13Compliant为true，在博主的开发环境下是不行的，依然会出现异常 2.采用类的反射： private Timestamp translateTimestamp(Object val) { try { Class c = value.getClass...
达梦数据库java.sql.Timestamp cannot be cast to java.sql.Date问题排查
LEARN_BLOG_CSDN的博客
07-05 2174
java.sql.Timestamp cannot be cast to java.sql.Date问题排查
flink报错java.lang.ClassCastException: java.time.LocalDateTime cannot be cast to java.sql.Timestamp
05-28
这个错误通常是由于在Flink中将LocalDateTime类型直接转换为Timestamp类型时引起的。虽然它们都表示日期时间，但它们是不同的类，不能直接相互转换。 要将LocalDateTime类型转换为Timestamp类型，可以使用Flink SQL中的`TO_TIMESTAMP`函数，例如： ```sql SELECT TO_TIMESTAMP('2021-07-22 13:30:00', 'yyyy-MM-dd HH:mm:ss') as ts FROM mytable; ``` 这里我们使用`TO_TIMESTAMP`函数将字符串`'2021-07-22 13:30:00'`转换为Timestamp类型，并将其命名为`ts`。 如果你从数据源中读取了一个Timestamp类型的值，并且想要将其转换为LocalDateTime类型，可以使用Flink SQL中的`FROM_TIMESTAMP`函数，例如： ```sql SELECT FROM_TIMESTAMP(timestamp_column) as dt FROM mytable; ``` 这里我们使用`FROM_TIMESTAMP`函数将`timestamp_column`列中的值转换为LocalDateTime类型，并将其命名为`dt`。 另外，如果你使用Flink Table API或DataStream API进行流处理，也可以使用Flink提供的TimestampAssigner或TimestampExtractor来将数据流中的时间戳转换为Timestamp类型。具体实现方式可以参考Flink官方文档。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 5
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值