You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@dolphinscheduler.apache.org by GitBox <gi...@apache.org> on 2022/04/19 14:23:25 UTC
[GitHub] [dolphinscheduler] hanfengcan opened a new issue, #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
hanfengcan opened a new issue, #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/dolphinscheduler/issues?q=is%3Aissue) and found no similar issues.
### What happened
我在执行SQL任务的时候, 报错`execute sql error: Can not issue data manipulation statements with executeQuery()`
数据库使用了Doris
建表和select都可以正常执行
### What you expected to happen
能够执行insert语句
### How to reproduce
目标表
```
CREATE TABLE `wx_payment_details` (
`create_time` datetime NOT NULL COMMENT "创建时间",
`amount` decimal(12, 4) NULL COMMENT "支付金额",
`settlement_refund_fee` decimal(12, 4) NULL COMMENT "退款金额",
`goods_name` varchar(100) NULL COMMENT "商品名称",
`goods_type` varchar(50) NULL COMMENT "商品类型",
`transaction_id` varchar(100) NOT NULL DEFAULT "reserve" COMMENT "微信订单号"
) ENGINE=OLAP
DUPLICATE KEY(`create_time`)
COMMENT "微信订单记录"
DISTRIBUTED BY HASH(`create_time`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2"
);
```
源表
CREATE TABLE `wx_payment_details_load` (
`create_time` varchar(100) NOT NULL COMMENT "",
`appid` varchar(100) NOT NULL COMMENT "",
`mch_id` varchar(100) NOT NULL COMMENT "",
`mch_id_child` varchar(50) NOT NULL COMMENT "",
`device_info` varchar(60) NULL COMMENT "",
`transaction_id` varchar(100) NOT NULL COMMENT "",
`out_trade_no` varchar(100) NOT NULL COMMENT "",
`openid` varchar(100) NOT NULL COMMENT "",
`trade_type` varchar(100) NOT NULL COMMENT "",
`trade_state` varchar(100) NOT NULL COMMENT "",
`bank_type` varchar(100) NOT NULL COMMENT "",
`fee_type` varchar(100) NOT NULL COMMENT "",
`amount` varchar(50) NOT NULL COMMENT "",
`enterprise_discounts` varchar(100) NOT NULL COMMENT "",
`refund_id` varchar(100) NOT NULL COMMENT "",
`out_refund_no` varchar(100) NOT NULL COMMENT "",
`settlement_refund_fee` varchar(50) NOT NULL COMMENT "",
`enterprise_refund_discounts` varchar(100) NOT NULL COMMENT "",
`refund_type` varchar(100) NULL COMMENT "",
`refund_status` varchar(100) NULL COMMENT "",
`goods_name` varchar(100) NOT NULL COMMENT "",
`attach` varchar(100) NOT NULL COMMENT "",
`service_charge` varchar(100) NOT NULL COMMENT "",
`rate` varchar(100) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`create_time`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`create_time`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2"
);
将源表中的微信账单处理后写入目标表
```
INSERT into example_db.wx_payment_details SELECT
CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
0 + split_part(amount, '`', 2) as amount,
0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
split_part(goods_name, '`', 2) as goods_name,
CASE goods_name
when '`门诊' then '门诊缴费'
when '`诊间支付' then '门诊缴费'
when '`住院缴费' then '住院缴费'
when '`住院预交' then '住院缴费'
else goods_name end as goods_type,
split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load;
```
### Anything else
_No response_
### Version
2.0.2
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] github-actions[bot] commented on issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596#issuecomment-1102720476
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/dolphinscheduler/issues?q=is%3Aissue) and found no similar issues.
### What happened
When I execute a SQL task, I get an error `execute sql error: Can not issue data manipulation statements with executeQuery()`
The database uses Doris
Both table creation and select can be executed normally
### What you expected to happen
Ability to execute insert statements
### How to reproduce
target table
````
CREATE TABLE `wx_payment_details` (
`create_time` datetime NOT NULL COMMENT "create time",
`amount` decimal(12, 4) NULL COMMENT "Payment Amount",
`settlement_refund_fee` decimal(12, 4) NULL COMMENT "Refund Amount",
`goods_name` varchar(100) NULL COMMENT "goods name",
`goods_type` varchar(50) NULL COMMENT "goods type",
`transaction_id` varchar(100) NOT NULL DEFAULT "reserve" COMMENT "WeChat order number"
) ENGINE=OLAP
DUPLICATE KEY(`create_time`)
COMMENT "WeChat order record"
DISTRIBUTED BY HASH(`create_time`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2"
);
````
source table
CREATE TABLE `wx_payment_details_load` (
`create_time` varchar(100) NOT NULL COMMENT "",
`appid` varchar(100) NOT NULL COMMENT "",
`mch_id` varchar(100) NOT NULL COMMENT "",
`mch_id_child` varchar(50) NOT NULL COMMENT "",
`device_info` varchar(60) NULL COMMENT "",
`transaction_id` varchar(100) NOT NULL COMMENT "",
`out_trade_no` varchar(100) NOT NULL COMMENT "",
`openid` varchar(100) NOT NULL COMMENT "",
`trade_type` varchar(100) NOT NULL COMMENT "",
`trade_state` varchar(100) NOT NULL COMMENT "",
`bank_type` varchar(100) NOT NULL COMMENT "",
`fee_type` varchar(100) NOT NULL COMMENT "",
`amount` varchar(50) NOT NULL COMMENT "",
`enterprise_discounts` varchar(100) NOT NULL COMMENT "",
`refund_id` varchar(100) NOT NULL COMMENT "",
`out_refund_no` varchar(100) NOT NULL COMMENT "",
`settlement_refund_fee` varchar(50) NOT NULL COMMENT "",
`enterprise_refund_discounts` varchar(100) NOT NULL COMMENT "",
`refund_type` varchar(100) NULL COMMENT "",
`refund_status` varchar(100) NULL COMMENT "",
`goods_name` varchar(100) NOT NULL COMMENT "",
`attach` varchar(100) NOT NULL COMMENT "",
`service_charge` varchar(100) NOT NULL COMMENT "",
`rate` varchar(100) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`create_time`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`create_time`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2"
);
Write the WeChat bill in the source table into the target table after processing
````
INSERT into example_db.wx_payment_details SELECT
CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
0 + split_part(amount, '`', 2) as amount,
0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
split_part(goods_name, '`', 2) as goods_name,
CASE goods_name
when '`outpatient' then 'outpatient payment'
when '`payment between clinics' then 'payment for outpatient clinics'
when '`hospitalization payment' then 'hospitalization payment'
when '`hospitalization prepayment' then 'hospitalization payment'
else goods_name end as goods_type,
split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load;
````
### Anything else
_No response_
### Version
2.0.2
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] hanfengcan closed issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by GitBox <gi...@apache.org>.
hanfengcan closed issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
URL: https://github.com/apache/dolphinscheduler/issues/9596
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] hanfengcan commented on issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by GitBox <gi...@apache.org>.
hanfengcan commented on issue #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596#issuecomment-1103411121
@SbloodyS Removing the semicolon also raises an error
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] hanfengcan commented on issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by GitBox <gi...@apache.org>.
hanfengcan commented on issue #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596#issuecomment-1103585686
```
[LOG-PATH]: /opt/apache-dolphinscheduler-2.0.1-bin/logs/5241061414816_1/100/275.log, [HOST]: 192.168.160.3
[INFO] 2022-04-20 10:37:30.007 - [taskAppId=TASK-5241061414816_1-100-275]:[121] - Full sql parameters: SqlParameters{type='MYSQL', datasource=3, sql='INSERT into fy_payment_db.wx_payment_details SELECT
CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
0 + split_part(amount, '`', 2) as amount,
0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
split_part(goods_name, '`', 2) as goods_name,
CASE goods_name
when '`门诊缴费' then '门诊缴费'
when '`诊间支付-缴费订单' then '门诊缴费'
when '`住院缴费' then '住院缴费'
when '`住院缴费-住院预交' then '住院缴费'
else goods_name end as goods_type,
split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load', sqlType=0, sendEmail=false, displayRows=10, limit=0, udfs='', showType='null', connParams='', groupId='0', title='', preStatements=[], postStatements=[]}
[INFO] 2022-04-20 10:37:30.014 - [taskAppId=TASK-5241061414816_1-100-275]:[122] - sql type : MYSQL, datasource : 3, sql : INSERT into fy_payment_db.wx_payment_details SELECT
CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
0 + split_part(amount, '`', 2) as amount,
0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
split_part(goods_name, '`', 2) as goods_name,
CASE goods_name
when '`门诊缴费' then '门诊缴费'
when '`诊间支付-缴费订单' then '门诊缴费'
when '`住院缴费' then '住院缴费'
when '`住院缴费-住院预交' then '住院缴费'
else goods_name end as goods_type,
split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load , localParams : [],udfs : ,showType : null,connParams : ,varPool : [] ,query max result limit 0
[INFO] 2022-04-20 10:37:30.017 - [taskAppId=TASK-5241061414816_1-100-275]:[450] - after replace sql , preparing : INSERT into fy_payment_db.wx_payment_details SELECT
CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
0 + split_part(amount, '`', 2) as amount,
0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
split_part(goods_name, '`', 2) as goods_name,
CASE goods_name
when '`门诊缴费' then '门诊缴费'
when '`诊间支付-缴费订单' then '门诊缴费'
when '`住院缴费' then '住院缴费'
when '`住院缴费-住院预交' then '住院缴费'
else goods_name end as goods_type,
split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load
[INFO] 2022-04-20 10:37:30.018 - [taskAppId=TASK-5241061414816_1-100-275]:[459] - Sql Params are replaced sql , parameters:
[INFO] 2022-04-20 10:37:30.019 - [taskAppId=TASK-5241061414816_1-100-275]:[529] - can't find udf function resource
[INFO] 2022-04-20 10:37:30.029 - [taskAppId=TASK-5241061414816_1-100-275]:[403] - prepare statement replace sql : HikariProxyPreparedStatement@1988302493 wrapping com.mysql.cj.jdbc.ClientPreparedStatement: INSERT into fy_payment_db.wx_payment_details SELECT
CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
0 + split_part(amount, '`', 2) as amount,
0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
split_part(goods_name, '`', 2) as goods_name,
CASE goods_name
when '`门诊缴费' then '门诊缴费'
when '`住院缴费' then '住院缴费'
else goods_name end as goods_type,
split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load
[ERROR] 2022-04-20 10:37:30.030 - [taskAppId=TASK-5241061414816_1-100-275]:[212] - execute sql error: Can not issue data manipulation statements with executeQuery().
[ERROR] 2022-04-20 10:37:30.031 - [taskAppId=TASK-5241061414816_1-100-275]:[163] - sql task error: java.sql.SQLException: Can not issue data manipulation statements with executeQuery().
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] SbloodyS commented on issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by GitBox <gi...@apache.org>.
SbloodyS commented on issue #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596#issuecomment-1103430172
> @SbloodyS Removing the semicolon also raises an error
Can you paste your configuration and error log here?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] hanfengcan closed issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by GitBox <gi...@apache.org>.
hanfengcan closed issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
URL: https://github.com/apache/dolphinscheduler/issues/9596
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] SbloodyS commented on issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by GitBox <gi...@apache.org>.
SbloodyS commented on issue #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596#issuecomment-1102782240
Currently SQL node does not support semicolons. This feature will be implemented when the issue #9177 is closed. You can pay attention to it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] hanfengcan commented on issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by GitBox <gi...@apache.org>.
hanfengcan commented on issue #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596#issuecomment-1103645933
OK, it work, thanks
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] hanfengcan commented on issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by GitBox <gi...@apache.org>.
hanfengcan commented on issue #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596#issuecomment-1103360162
thanks
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] github-actions[bot] commented on issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596#issuecomment-1102720738
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](https://join.slack.com/t/asf-dolphinscheduler/shared_invite/zt-omtdhuio-_JISsxYhiVsltmC5h38yfw) and send your question to channel `#troubleshooting`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] SbloodyS commented on issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by GitBox <gi...@apache.org>.
SbloodyS commented on issue #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596#issuecomment-1103598638
Did you chose ```Non Query``` sql type? @hanfengcan
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] netcloudtec commented on issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by "netcloudtec (via GitHub)" <gi...@apache.org>.
netcloudtec commented on issue #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596#issuecomment-1681514939
> 大佬这个问题怎么解决的？
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] netcloudtec commented on issue #9596: [Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()
Posted by "netcloudtec (via GitHub)" <gi...@apache.org>.
netcloudtec commented on issue #9596:
URL: https://github.com/apache/dolphinscheduler/issues/9596#issuecomment-1681514623
大佬这个问题怎么解决的？
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org