无法使用executequery发出数据操作语句-data studio/my sql connector错误

envsm3lx  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(413)

我有一个在mysql workbench中运行得很好的查询,但是当我尝试在datastudio中的mysql connector中运行它时,却遇到了一个与datastudio相关的错误。我试着在谷歌上四处搜索,并在so和datastudio的留言板上询问一些想法,但没有得到多少想法。我发现的大多数回答都与java有关。
我的问题:

CREATE TEMPORARY TABLE cte_most_recent_record
    SELECT
    leeds_new.leenk_ladder_history.member_id as member_id,
    max(leeds_new.leenk_ladder_history.date_trigger_event) AS date_trigger_event_max
    FROM leeds_new.leenk_ladder_history
    WHERE leeds_new.leenk_ladder_history.ladder_change = 1 and leeds_new.leenk_ladder_history.ladder_advocacy is not NULL
    group by leeds_new.leenk_ladder_history.member_id;

CREATE TEMPORARY TABLE cte_most_recent_record_date_ladder
    select 
    lh.member_id,
    lh.ladder_advocacy,
    lh.date_trigger_event

    from leeds_new.leenk_ladder_history as lh 
        inner join
            cte_most_recent_record as cte_rr on lh.member_id = cte_rr.member_id
            and lh.date_trigger_event = cte_rr.date_trigger_event_max
            limit 100;

CREATE TEMPORARY TABLE cte_ladder_counts_before
    select 
        ladder_advocacy,
        count(ladder_advocacy) as ladder_counts_before

    from cte_most_recent_record_date_ladder
        where date_trigger_event < date('2018-01-01')
        group by ladder_advocacy;

CREATE TEMPORARY TABLE cte_ladder_counts_after
    select 
        ladder_advocacy,
        count(ladder_advocacy) as ladder_counts_after

    from cte_most_recent_record_date_ladder
        where date_trigger_event > date('2018-01-01')
        group by ladder_advocacy;

select
    cte_ladder_counts_before.ladder_advocacy,
    cte_ladder_counts_before.ladder_counts_before,
    ladder_counts_after,
    ladder_counts_before - ladder_counts_after

from cte_ladder_counts_before
    inner join cte_ladder_counts_after on cte_ladder_counts_before.ladder_advocacy = cte_ladder_counts_after.ladder_advocacy; 

drop table cte_most_recent_record;
drop table cte_most_recent_record_date_ladder;
drop table cte_ladder_counts_before;
drop table cte_ladder_counts_after;

错误:

Sorry, we encountered an error and were unable to complete your request.

Failed to execute connection with error: Can not issue data manipulation statements with executeQuery().
Error ID: 24721465

我相信这与我在同一个查询中有多个表有关-因为我可以用一个select语句运行查询?但我不确定是否还有其他我应该注意的datastudio事情。
思想?

djp7away

djp7away1#

我知道现在回答有点晚,但以防万一将来有人需要这个。我今天碰到了同样的问题。答案是相当令人沮丧的,因为它很容易解决。
你用的是 CREATE TEMPORARY TABLE 句子(因为我也在做)。然后我意识到datastudio不能处理这个句子(显然,它也不能进行多重查询)。
所以我的解决方法是在数据库中实际创建表,而不是作为临时表(有效但一点也不干净)。。。并删除 CREATE TEMPORARY TABLE 我的问题中的句子。工作起来很有魅力,但在黑暗的一面,现在我有一个无用的表在我的数据库只是为了处理查询。
希望有帮助,亲切的问候。

相关问题