MYSQL8 在线查询功能 报错

[qn2060]

重现步骤

1、一个非dba角色用户（主要是不能有查询所有库权限）申请了相关库的权限
2、在相关库执行类似SQL
select term,assist_institution_code,pr_m_amonunt,pr_m_amonunt_total
FROM
(

select term,
assist_institution_code 
   ,pr_m_amonunt,ROW_NUMBER() over(partition by term order by pr_m_amonunt  desc) as rn,
   sum(pr_m_amonunt) over (partition by term) pr_m_amonunt_total
FROM  
(

select 
        term,replace(assist_institution_code,'_YKB','')assist_institution_code,SUM(loan_amount)pr_m_amonunt
  from t_order_day
  where 1=1 
  and stat_date >= DATE_FORMAT('2022-10-21','%Y-%m-01')  and stat_date <= '2022-10-21' 
    and DATE_FORMAT('2022-10-21','%Y-%m') = DATE_FORMAT('2022-10-21','%Y-%m')
        group by  term,replace(assist_institution_code,'_YKB','')
)t_amount

)t_rn
where rn = 1

预期外的结果

Error

无法校验查询语句权限，请联系管理员，错误信息：line 7 column 40 near "(partition by term order by pr_m_amonunt desc) as rn, sum(pr_m_amonunt) over (partition by term) pr_m_amonunt_total FROM ( select term,replace(assist_institution_code,'_YKB','')assist_institution_code,SUM(loan_amount)pr_m_amonunt from t_order_day where 1=1 and stat_date >= DATE_FORMAT('2022-10-21','%Y-%m-01') and stat_date <= '2022-10-21' and DATE_FORMAT('2022-10-21','%Y-%m') = DATE_FORMAT('2022-10-21','%Y-%m') group by term,replace(assist_institution_code,'_YKB','') )t_amount )t_rn where rn = 1"

日志文本

[2022-10-21 15:40:24,202][MainThread:140379151083328][task_id:default][query_privileges.py:85][ERROR]- 无法校验查询语句权限，PROD-微神马生产MYSQL8-RDS，select term,assist_institution_code,pr_m_amonunt,pr_m_amonunt_total
  FROM
  (

    select term,
    assist_institution_code
       ,pr_m_amonunt,ROW_NUMBER() over(partition by term order by pr_m_amonunt  desc) as rn,
       sum(pr_m_amonunt) over (partition by term) pr_m_amonunt_total
    FROM
    (

    select
            term,replace(assist_institution_code,'_YKB','')assist_institution_code,SUM(loan_amount)pr_m_amonunt
      from t_order_day
      where 1=1
      and stat_date >= DATE_FORMAT('2022-10-21','%Y-%m-01')  and stat_date <= '2022-10-21'
        and DATE_FORMAT('2022-10-21','%Y-%m') = DATE_FORMAT('2022-10-21','%Y-%m')
            group by  term,replace(assist_institution_code,'_YKB','')
    )t_amount
  )t_rn
  where rn = 1，Traceback (most recent call last):
  File "/opt/archery/sql/query_privileges.py", line 69, in query_priv_check
    table_ref = _table_ref(sql_content, instance, db_name)
  File "/opt/archery/sql/query_privileges.py", line 409, in _table_ref
    query_tree = engine.query_print(instance=instance, db_name=db_name, sql=sql_content).get('query_tree')
  File "/opt/archery/sql/engines/goinception.py", line 165, in query_print
    raise RuntimeError(print_info.get('errmsg'))
RuntimeError: line 7 column 40 near "(partition by term order by pr_m_amonunt  desc) as rn,
       sum(pr_m_amonunt) over (partition by term) pr_m_amonunt_total
    FROM
    (

    select
            term,replace(assist_institution_code,'_YKB','')assist_institution_code,SUM(loan_amount)pr_m_amonunt
      from t_order_day
      where 1=1
      and stat_date >= DATE_FORMAT('2022-10-21','%Y-%m-01')  and stat_date <= '2022-10-21'
        and DATE_FORMAT('2022-10-21','%Y-%m') = DATE_FORMAT('2022-10-21','%Y-%m')
            group by  term,replace(assist_institution_code,'_YKB','')
    )t_amount
  )t_rn
  where rn = 1"

版本

1.8.5

部署方式

Docker

是否还有其他可以辅助定位问题的信息？比如数据库版本等

mysql8

[LeoQuote]

先升最新版再试试

[viper-sina-info]

select * from user_wallet_log partition(p10);

我安装了1.8.3，然后升级到最新的版本1.10.0， 也是一样在 partition 关键字的地方报错，admin用户和DBA用户是可以查询，但是普通用户就会报错。

普通用户如果授予
sql | permission | 可查询所在资源组内的所有实例
或者
sql | permission | 可查询所有实例
权限， 就可以查询。

但是普通用户的查询是需要审核才可以查询。不能给这两个权限

[LeoQuote]

从你的描述来看, 是校验权限的时候出了问题, 校验权限时是使用的 goinception, 请查看 hanchuanchuan/goInception#281 .

检查的时候注意两点:

1. 校验权限是否使用的是 goinception
2. 是否使用的是支持partition 的 goinception
3. 是否已经打开 partition 支持.

欢迎 pr 或更新 wiki.

[viper-sina-info]

1.看日志已经调用到了goinception
2.admin和DBA用户可以查询，应该就是支持的
3.goinception已经升级到最新版本v1.3.0

还是报错
[2023-10-09 18:12:50,471][Thread-93:140324078388992][task_id:default][query_privileges.py:94][ERROR]- 无法校验查询语句权限，me，select order_no from user_wallet_log partition(p10);，Traceback (most recent call last):
File "/opt/app/sql/query_privileges.py", line 73, in query_priv_check
table_ref = _table_ref(sql_content, instance, db_name)
File "/opt/app/sql/query_privileges.py", line 485, in _table_ref
query_tree = engine.query_print(
File "/opt/app/sql/engines/goinception.py", line 184, in query_print
raise RuntimeError(print_info.get("errmsg"))
RuntimeError: line 1 column 75 near "partition(p10)"

[hhyo]

admin和dba不做权限检验，也走不到语法解析