dolphinscheduler icon indicating copy to clipboard operation
dolphinscheduler copied to clipboard

Published 20 hours ago • verified publisher icon apache

[Bug] [Task] SQL task prepareStatementAndBind error

Open 777yu opened this issue 2 years ago • 17 comments

Search before asking

  • [X] I had searched in the issues and found no similar issues.

What happened

hive sql 执行添加了参数，任务本身没有问题，在日志中发现报这个错误。重试次数调高之后，在多次重试之后有时可以执行成功。 error

What you expected to happen

多次重试，任务可以执行完成

How to reproduce

有时候会报错，有时候不会

Anything else

经常会出现

Version

2.0.3

Are you willing to submit PR?

  • [ ] Yes I am willing to submit a PR!

Code of Conduct

777yu avatar Jun 22 '22 02:06 777yu

Search before asking

  • [X] I had searched in the issues and found no similar issues.

What happened

The hive sql execution has added parameters, the task itself has no problem, and this error is found in the log. After the number of retries is increased, the execution can sometimes be successful after multiple retries. error

What you expected to happen

After several retries, the task can be executed and completed

How to reproduce

Sometimes it gives an error, sometimes it doesn't

Anything else

occurs often

Version

2.0.3

Are you willing to submit PR?

  • [ ] Yes I am willing to submit a PR!

Code of Conduct

github-actions[bot] avatar Jun 22 '22 02:06 github-actions[bot]

Thank you for your feedback, we have received your issue, Please wait patiently for a reply.

  • In order for us to understand your request as soon as possible, please provide detailed information、version or pictures.
  • If you haven't received a reply for a long time, you can join our slack and send your question to channel #troubleshooting

github-actions[bot] avatar Jun 22 '22 02:06 github-actions[bot]

Hi @777yu , could you try if this problem exists in 2.0.5-release?

SbloodyS avatar Jun 22 '22 03:06 SbloodyS

你好@777yu，如果2.0.5-release中存在这个问题，你能试试吗？

2.0.5 存在这个问题，我昨天出现了，今天又出现了。昨天重跑就执行成功了。 还有就是我的工作流实例外面看一直在运行，实际上里面是失败了，我点停止后，它一直在停止，卡死了，这是bug吗？

Flutemr avatar Jun 23 '22 00:06 Flutemr

Is there any error logs?

BTW, Please describe it in English so that others can understand it.

SbloodyS avatar Jun 23 '22 01:06 SbloodyS

有错误日志吗？

BTW，请用英文描述，以便其他人理解。 2.0.5-release error log

[INFO] 2022-06-23 06:00:31.251 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask:[118] - Full sql parameters: SqlParameters{type='HIVE', datasource=3, sql='INSERT overwrite table yyt_dwd.dwd_gy_ykt_xsyktzpxx partition(dt='${bizdate1}') select distinct ecode ,UPPER(REGEXP_REPLACE(outid,'[^0-9a-zA-Z]+','')) as outid ,photo
,updateflag ,updatedt ,downdt ,ver from yyt_ods.ods_card_m_base_ecardphotos where dt='${bizdate1}' and outid <>''', sqlType=1, sendEmail=false, displayRows=10, limit=0, udfs='', showType='null', connParams='', groupId='0', title='', preStatements=[CREATE TABLE if not exists yyt_dwd.dwd_gy_ykt_xsyktzpxx( ecode string COMMENT '企业IDECARD-T4.0等多客户版本必填，其它版本不用填', outid string COMMENT '学工号', photo binary COMMENT '照片', updateflag string COMMENT '更新状态1：新增；2：修改；99：已同步', updatedt string COMMENT '更新时间', downdt string COMMENT '读取时间', ver string COMMENT '记录版本，用于增量同步') COMMENT '一卡通照片信息' PARTITIONED BY ( dt string COMMENT 'ods层分区字段,如20220321') stored as orc], postStatements=[]} [INFO] 2022-06-23 06:00:31.251 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask:[119] - sql type : HIVE, datasource : 3, sql : INSERT overwrite table yyt_dwd.dwd_gy_ykt_xsyktzpxx partition(dt='${bizdate1}') select distinct ecode ,UPPER(REGEXP_REPLACE(outid,'[^0-9a-zA-Z]+','')) as outid ,photo
,updateflag ,updatedt ,downdt ,ver from yyt_ods.ods_card_m_base_ecardphotos where dt='${bizdate1}' and outid <>'' , localParams : [Property{prop='bizdate1', direct=IN, type=VARCHAR, value='${system.biz.date}'}],udfs : ,showType : null,connParams : ,varPool : [] ,query max result limit 0 [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask:[94] - setSqlParamsMap: Property with paramName: bizdate1 put in sqlParamsMap of content INSERT overwrite table yyt_dwd.dwd_gy_ykt_xsyktzpxx partition(dt='${bizdate1}') select distinct ecode ,UPPER(REGEXP_REPLACE(outid,'[^0-9a-zA-Z]+','')) as outid ,photo
,updateflag ,updatedt ,downdt ,ver from yyt_ods.ods_card_m_base_ecardphotos where dt='${bizdate1}' and outid <>'' successfully. [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask:[94] - setSqlParamsMap: Property with paramName: bizdate1 put in sqlParamsMap of content INSERT overwrite table yyt_dwd.dwd_gy_ykt_xsyktzpxx partition(dt='${bizdate1}') select distinct ecode ,UPPER(REGEXP_REPLACE(outid,'[^0-9a-zA-Z]+','')) as outid ,photo
,updateflag ,updatedt ,downdt ,ver from yyt_ods.ods_card_m_base_ecardphotos where dt='${bizdate1}' and outid <>'' successfully. [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask:[418] - after replace sql , preparing : INSERT overwrite table yyt_dwd.dwd_gy_ykt_xsyktzpxx partition(dt=?) select distinct ecode ,UPPER(REGEXP_REPLACE(outid,'[^0-9a-zA-Z]+','')) as outid ,photo
,updateflag ,updatedt ,downdt ,ver from yyt_ods.ods_card_m_base_ecardphotos where dt=? and outid <>'' [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask:[427] - Sql Params are replaced sql , parameters:20220622(VARCHAR)20220622(VARCHAR) [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask:[418] - after replace sql , preparing : CREATE TABLE if not exists yyt_dwd.dwd_gy_ykt_xsyktzpxx( ecode string COMMENT '企业IDECARD-T4.0等多客户版本必填，其它版本不用填', outid string COMMENT '学工号', photo binary COMMENT '照片', updateflag string COMMENT '更新状态1：新增；2：修改；99：已同步', updatedt string COMMENT '更新时间', downdt string COMMENT '读取时间', ver string COMMENT '记录版本，用于增量同步') COMMENT '一卡通照片信息' PARTITIONED BY ( dt string COMMENT 'ods层分区字段,如20220321') stored as orc [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask:[427] - Sql Params are replaced sql , parameters: [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask:[496] - can't find udf function resource [ERROR] 2022-06-23 06:01:08.356 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask:[209] - execute sql error: SQL task prepareStatementAndBind error [ERROR] 2022-06-23 06:01:08.356 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask:[160] - sql task error: org.apache.dolphinscheduler.plugin.task.api.TaskException: SQL task prepareStatementAndBind error

Flutemr avatar Jun 23 '22 02:06 Flutemr

有错误的日志吗？ BTW，请用英文描述，以便其他人理解。 2.0.5-release 错误日志

[LOG-PATH]：/opt/dolphinscheduler/logs/5858213194912_5/1315/2510.log，[HOST]：10.60.0.49 [INFO] 2022-06-23 06:00:31.251 TaskLogLogger-class org.apache.dolphinscheduler。 plugin.task.sql.SqlTask​​:[118] - 完整的 sql 参数：SqlParameters{type='HIVE', datasource=3, sql='INSERT overwrite table yyt_dwd.dwd_gy_ykt_xsyktzpxx partition(dt='${bizdate1}') select distinct ecode ,UPPER(REGEXP_REPLACE(outid,'[^0-9a-zA-Z]+','')) as outid ,photo ,updateflag , updatedt ,downdt ,ver from yyt_ods.ods_card_m_base_ecardphotos 其中 dt='${bizdate1}' 和 outid <>''', sqlType=1, sendEmail=false, displayRows=10, limit=0, udfs='', showType='null', connParams='', groupId ='0', title='', preStatements=[CREATE TABLE if not exists yyt_dwd.dwd_gy_ykt_xsyktzpxx( ecodestring COMMENT '企业IDECARD-T4.0等多客户版本必填，其他版本不用填', outidstring COMMENT '学工号', photobinary COMMENT '照片'， updateflag字符串 COMMENT '更新状态修改 1：新增；2：；99：已同步'， updatedt字符串 COMMENT '更新时间'， downdt字符串 COMMENT '读取时间'， ver字符串 COMMENT '记录版本，用于增量同步') COMMENT '一卡通层照片信息' PARTITION BY ( dtstring COMMENT 'ods分区字段,如20220321') stored as orc], postStatements=[]} [INFO] 2022-06-23 06:00:31.251 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[119] - sql 类型：HIVE，数据源：3，sql：INSERT 覆盖表 yyt_dwd。 dwd_gy_ykt_xsyktzpxx partition(dt='${bizdate1}') 选择不同的编码 ,UPPER( REGEXP_REPLACE (outid,'[^0-9a-zA-Z]+','')) 作为 outid ,photo ,updateflag , updatedt ,downdt ,ver 来自 yyt_ods.ods_card_m_base_ecardphotos 其中 dt=无法呈现表达式。 '${bizdate1}' 和 outid <>'' , localParams : [Property{prop='bizdate1', direct=IN, type=VARCHAR, value='${system.biz.date}'}],udfs : ,showType : null,connParams : ,varPool : [] ,查询最大结果限制 0 [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache .dolphinscheduler.plugin.task.sql.SqlTask​​:[94] - setSqlParamsMap: 具有参数名称的属性：bizdate1 放入内容的 sqlParamsMap 中 INSERT 覆盖表 yyt_dwd.dwd_gy_ykt_xsyktzpxx 分区（dt='${bizdate1}'） 选择 不同的编码 ，UPPER（ REGEXP_REPLACE(outid,'[^0-9a-zA-Z]+','')) as outid ,photo ,updateflag , updatedt ,downdt ,ver from yyt_ods.ods_card_m_base_ecardphotos where dt='${bizdate1}' and outid < >'' 成功。 [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[94] - setSqlParamsMap: 具有参数名称的属性：bizdate1 放入内容的 sqlParamsMap 中，INSERT 覆盖表 yyt_dwd .dwd_gy_ykt_xsyktzpxx partition(dt='${bizdate1}') 选择不同的编码 ,UPPER( REGEXP_REPLACE (outid,'[^0-9a-zA-Z]+','')) 作为 outid ,photo ,updateflag , updatedt , downdt ,ver 从 yyt_ods.ods_card_m_base_ecardphotos where dt='${bizdate1}' and outid <>'' 成功。 [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[418] - 替换 sql 后，准备：INSERT 覆盖表 yyt_dwd。 选择不同的 ecode ,UPPER( REGEXP_REPLACE (outid,'[^0-9a-zA-Z]+','')) 作为 outid ,photo ,updateflag , updatedt ,downdt ,ver from yyt_ods.ods_card_m_base_ecardphotos where dt=? 和 outid <>'' [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[427] - Sql 参数被替换 sql ，参数：20220622( VARCHAR)20220622(VARCHAR) [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[418] - 替换 sql 后，准备：如果没有，则创建表存在yyt_dwd.dwd_gy_ykt_xsyktzpxx( ecodestring COMMENT '企业IDECARD-T4.0等多客户版本必填，其他版本不用填', outidstring COMMENT '学工号', photo updateflagstring COMMENT '更新状态1：新增；2：修改；99：已同步', updatedtstring COMMENT '更新时间', downdtstring COMMENT '读取时间', verstring COMMENT '记录版本，用于增量同步') COMMENT '一卡通图片信息' PARTITIONED BY ( dtstring'ods 分区字段，如2022220321') 存储为orc [IN] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.plugin.task.sql。 SqlTask​​:[427] - Sql 参数被替换为 sql ，参数： [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[496] - can' t find udf function resource [ERROR] 2022-06-23 06:01:08.356 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[209] - 执行 sql 错误：SQL 任务 prepareStatementAndBind 错误 [错误] 2022-06-23 06:01:08.356 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[160] - sql 任务错误：org.apache.dolphinscheduler.plugin.task.api。 TaskException：SQL 任务 prepareStatementAndBind 错误

你的解决了吗？

777yu avatar Jun 25 '22 03:06 777yu

today ，I have this mistake on many tasks。 1657507161(1) 1657506958(1) 1657507114(1)

Flutemr avatar Jul 11 '22 02:07 Flutemr

有错误的日志吗？ BTW，请用英文描述，以便其他人理解。 2.0.5-release 错误日志

[LOG-PATH]：/opt/dolphinscheduler/logs/5858213194912_5/1315/2510.log，[HOST]：10.60.0.49 [INFO] 2022-06-23 06:00:31.251 TaskLogLogger-class org.apache.dolphinscheduler。 plugin.task.sql.SqlTask​​:[118] - 完整的 sql 参数：SqlParameters{type='HIVE', datasource=3, sql='INSERT overwrite table yyt_dwd.dwd_gy_ykt_xsyktzpxx partition(dt=Double subscripts: use braces to clarify

    '${bizdate1}') select distinct ecode ,UPPER(REGEXP_REPLACE(outid,'[^0-9a-zA-Z]+','')) as outid ,photo ,updateflag , updatedt ,downdt ,ver from yyt_ods.ods_card_m_base_ecardphotos 其中 dt='${bizdate1}' 和 outid <>''', sqlType=1, sendEmail=false, displayRows=10, limit=0, udfs='', showType='null', connParams='', groupId ='0', title='', preStatements=[CREATE TABLE if not exists `yyt_dwd.dwd_gy_ykt_xsyktzpxx`( `ecode`string COMMENT '企业IDECARD-T4.0等多客户版本必填，其他版本不用填', `outid`string COMMENT '学工号', `photo`binary COMMENT '照片'， `updateflag`字符串 COMMENT '更新状态修改 1：新增；2：；99：已同步'， `updatedt`字符串 COMMENT '更新时间'， `downdt`字符串 COMMENT '读取时间'， `ver`字符串 COMMENT '记录版本，用于增量同步') COMMENT '一卡通层照片信息' PARTITION BY ( `dt`string COMMENT 'ods分区字段,如20220321') stored as orc], postStatements=[]} [INFO] 2022-06-23 06:00:31.251 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[119] - sql 类型：HIVE，数据源：3，sql：INSERT 覆盖表 yyt_dwd。 dwd_gy_ykt_xsyktzpxx partition(dt=Double subscripts: use braces to clarify
    
    '${bizdate1}') 选择不同的编码 ,UPPER( REGEXP_REPLACE (outid,'[^0-9a-zA-Z]+','')) 作为 outid ,photo ,updateflag , updatedt ,downdt ,ver 来自 yyt_ods.ods_card_m_base_ecardphotos 其中 dt=无法呈现表达式。 '${bizdate1}' 和 outid <>'' , localParams : [Property{prop='bizdate1', direct=IN, type=VARCHAR, value=Extra close brace or missing open brace
    
    '${system.biz.date}'}],udfs : ,showType : null,connParams : ,varPool : [] ,查询最大结果限制 0 [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache .dolphinscheduler.plugin.task.sql.SqlTask​​:[94] - setSqlParamsMap: 具有参数名称的属性：bizdate1 放入内容的 sqlParamsMap 中 INSERT 覆盖表 yyt_dwd.dwd_gy_ykt_xsyktzpxx 分区（dt='${bizdate1}'） 选择 不同的编码 ，UPPER（ REGEXP_REPLACE(outid,'[^0-9a-zA-Z]+','')) as outid ,photo ,updateflag , updatedt ,downdt ,ver from yyt_ods.ods_card_m_base_ecardphotos where dt=成功。具有参数名称的属性：放入内容的中，覆盖表bizdate1′andoutid<>″成功。[INFO]2022−06−2306:00:31.252TaskLogLogger−classorg.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[94]−setSqlParamsMap:具有参数名称的属性：bizdate1放入内容的sqlParamsMap中，INSERT覆盖表yytdwd.dwdgyyktxsyktzpxxpartition(dt=′{bizdate1}') 选择不同的编码 ,UPPER( REGEXP_REPLACE (outid,'[^0-9a-zA-Z]+','')) 作为 outid ,photo ,updateflag , updatedt , downdt ,ver 从 yyt_ods.ods_card_m_base_ecardphotos where dt='${bizdate1}' and outid <>'' 成功。 [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[418] - 替换 sql 后，准备：INSERT 覆盖表 yyt_dwd。 选择不同的 ecode ,UPPER( REGEXP_REPLACE (outid,'[^0-9a-zA-Z]+','')) 作为 outid ,photo ,updateflag , updatedt ,downdt ,ver from yyt_ods.ods_card_m_base_ecardphotos where dt=? 和 outid <>'' [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[427] - Sql 参数被替换 sql ，参数：20220622( VARCHAR)20220622(VARCHAR) [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[418] - 替换 sql 后，准备：如果没有，则创建表存在`yyt_dwd.dwd_gy_ykt_xsyktzpxx`( `ecode`string COMMENT '企业IDECARD-T4.0等多客户版本必填，其他版本不用填', `outid`string COMMENT '学工号', `photo` `updateflag`string COMMENT '更新状态1：新增；2：修改；99：已同步', `updatedt`string COMMENT '更新时间', `downdt`string COMMENT '读取时间', `ver`string COMMENT '记录版本，用于增量同步') COMMENT '一卡通图片信息' PARTITIONED BY ( `dt`string'ods 分区字段，如2022220321') 存储为orc [IN] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.plugin.task.sql。 SqlTask​​:[427] - Sql 参数被替换为 sql ，参数： [INFO] 2022-06-23 06:00:31.252 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[496] - can' t find udf function resource [ERROR] 2022-06-23 06:01:08.356 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[209] - 执行 sql 错误：SQL 任务 prepareStatementAndBind 错误 [错误] 2022-06-23 06:01:08.356 TaskLogLogger-class org.apache.dolphinscheduler.plugin.task.sql.SqlTask​​:[160] - sql 任务错误：org.apache.dolphinscheduler.plugin.task.api。 TaskException：SQL 任务 prepareStatementAndBind 错误

你的解决了吗？ 没有，今天好多个任务又出现了。

Flutemr avatar Jul 11 '22 02:07 Flutemr

i think this is not dolphin issue, can you check the yarn logs of this task in yarn application ui?

fhygh avatar Jul 14 '22 07:07 fhygh

i think this is not dolphin issue, can you check the yarn logs of this task in yarn application ui?

new logs at #https://github.com/apache/dolphinscheduler/issues/10889。you can see it。

Flutemr avatar Jul 14 '22 08:07 Flutemr

Have you found the cause of this problem? We are in the same situation

zhaoxiaoge avatar Jul 19 '22 10:07 zhaoxiaoge

Have you found the cause of this problem? We are in the same situation watch this #10889

Flutemr avatar Jul 20 '22 00:07 Flutemr

你找到这个问题的原因了吗？我们处于同样的情况，请看 这个 #10889

增加 hiveserver2 线程数 可以解决吗

777yu avatar Aug 05 '22 07:08 777yu

你找到这个问题的原因了吗？我们处于同样的境地

没，没定位到哪里的问题

777yu avatar Aug 05 '22 07:08 777yu

你找到这个问题的原因了吗？

你们是怎么解决的

777yu avatar Aug 05 '22 07:08 777yu

你找到这个问题的原因了吗？

你们是怎么解决的

去看后台日志，找到这个任务运行时的日志，我的是hive 客户端连接超时，后面优化了hive客户端，问题解决了

Flutemr avatar Aug 06 '22 08:08 Flutemr

你找到这个问题的原因了吗？

你们是怎么解决的

去看后台日志，找到这个任务运行时的日志，我的是hive 客户端连接超时，后面优化了hive客户端，问题解决了

how to optimize the hive client?

caffreyZ avatar Sep 08 '22 03:09 caffreyZ

you can search hive HA

Flutemr avatar Sep 08 '22 03:09 Flutemr

你找到这个问题的原因了吗？

你们是怎么解决的

去看后台日志，找到这个任务运行时的日志，我的是hive 客户端连接超时，后面优化了hive客户端，问题解决了

how to optimize the hive client?

@caffreyZ Actually, it is not a good practice to use hiveserver2 for scheduled hive tasks in production. Hiveserver2 is a better choice for ad-hoc query. For scheduling purpose, you may try hive CLI. You could do it with shell task if you are using an old version of DS. If you are planning to upgrade DS, there will be a new Hive CLI task in the future. see: https://github.com/apache/dolphinscheduler/pull/11651

EricGao888 avatar Sep 08 '22 03:09 EricGao888

According to the comments from #10889, the problem seems not to be on the DS side. Therefore, I'm going to close it at this moment. If you believe it is a DS bug, please feel free to ping me or reopen the issue. Thanks : )

EricGao888 avatar Sep 08 '22 03:09 EricGao888