[ 问题咨询 ]遇到一个很奇怪的事情,mysql开启脱敏query_check导致mysql所有查询都报数据脱敏异常 list index out of range

[jdj199222]

问题描述

使用nginx代理方式部署出现如下问题:
脱敏功能开启, query check功能关闭情况下正常查询,脱敏正常,脱敏异常会直接显示
,query check开启情况下查询报错如下

后测试使用debug方式启动,query check开启,脱敏开启,查询不会报错,脱敏正常

版本信息

• 应用版本/分支：Release v1.7.11
• 部署方式：手工部署

[LeoQuote]

最好有个traceback，不然没法看到底哪里的问题

…

Sent from my iPhone

On Jul 10, 2020, at 18:08, jdj199222 ***@***.***> wrote:  问题描述 脱敏功能开启, query check功能关闭情况下正常查询,脱敏正常,脱敏异常会直接显示 ,query check开启情况下查询报错如下 后测试使用debug方式启动,query check开启,脱敏开启,查询不会报错,脱敏正常 版本信息 应用版本/分支：Release v1.7.11 部署方式：手工部署 — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.

[jdj199222]

最好有个traceback，不然没法看到底哪里的问题
…
Sent from my iPhone
On Jul 10, 2020, at 18:08, jdj199222 @.***> wrote:  问题描述 脱敏功能开启, query check功能关闭情况下正常查询,脱敏正常,脱敏异常会直接显示 ,query check开启情况下查询报错如下 后测试使用debug方式启动,query check开启,脱敏开启,查询不会报错,脱敏正常 版本信息 应用版本/分支：Release v1.7.11 部署方式：手工部署 — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.

[2020-07-13 11:36:27,475][Thread-2:47188736825088][task_id:default][mysql.py:164][WARNING]- MySQL语句执行报错，语句：show slave status，错误信息Traceback (most recent call last):
File "/tmp/archery/sql/engines/mysql.py", line 153, in query
effect_row = cursor.execute(sql)
File "/home/zb/data/archery/venv4archery/lib/python3.6/site-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/home/zb/data/archery/venv4archery/lib/python3.6/site-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/home/zb/data/archery/venv4archery/lib/python3.6/site-packages/MySQLdb/connections.py", line 239, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1227, 'Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation')

[2020-07-13 11:36:27,493][Thread-2:47188736825088][task_id:django.db.backends][utils.py:110][DEBUG]- (0.001) SELECT django_q_schedule.id, django_q_schedule.name, django_q_schedule.func, django_q_schedule.hook, django_q_schedule.args, django_q_schedule.kwargs, django_q_schedule.schedule_type, django_q_schedule.minutes, django_q_schedule.repeats, django_q_schedule.next_run, django_q_schedule.task FROM django_q_schedule WHERE django_q_schedule.name = 'query-1594611387.4537508'; args=('query-1594611387.4537508',)
[2020-07-13 11:36:27,496][Thread-2:47188736825088][task_id:django.db.backends][utils.py:110][DEBUG]- (0.001) DELETE FROM django_q_schedule WHERE django_q_schedule.id IN (530); args=(530,)
[2020-07-13 11:36:30,513][Thread-2:47188736825088][task_id:default][data_masking.py:37][WARNING]- 数据脱敏异常，错误信息：Traceback (most recent call last):
File "/tmp/archery/sql/utils/data_masking.py", line 32, in data_masking
query_tree = inception_engine.query_print(instance=instance, db_name=db_name, sql=sql)
File "/tmp/archery/sql/engines/inception.py", line 166, in query_print
print_info = self.query(db_name=db_name, sql=sql).to_dict()[0]
IndexError: list index out of range

[jdj199222]

我测试了目前只发现有一台mysql服务器上的两个实例上的所有数据库都有这种情况，其他的服务器配置的实例查询又是正常的

[LeoQuote]

#129 参考下这个

[jdj199222]

#129 参考下这个

[2020-07-13 11:45:46,771][Thread-1:47687124612864][task_id:default][data_masking.py:37][WARNING]- 数据脱敏异常，错误信息：Traceback (most recent call last):
File "/tmp/archery/sql/utils/data_masking.py", line 32, in data_masking
query_tree = inception_engine.query_print(instance=instance, db_name=db_name, sql=sql)
File "/tmp/archery/sql/engines/inception.py", line 167, in query_print
print_info = self.query(db_name=db_name, sql=sql).to_dict()[0]
IndexError: list index out of range

是这里报错，而不是权限问题，权限那个不会影响查询的，其他正常实例也没有授权super，这个有问题的实例授予super replication client权限了，并没有解决 list index out of range

[jdj199222]

#129 参考下这个

[2020-07-13 11:45:46,771][Thread-1:47687124612864][task_id:default][data_masking.py:37][WARNING]- 数据脱敏异常，错误信息：Traceback (most recent call last):
File "/tmp/archery/sql/utils/data_masking.py", line 32, in data_masking
query_tree = inception_engine.query_print(instance=instance, db_name=db_name, sql=sql)
File "/tmp/archery/sql/engines/inception.py", line 167, in query_print
print_info = self.query(db_name=db_name, sql=sql).to_dict()[0]
IndexError: list index out of range

是这里报错，而不是权限问题，权限那个不会影响查询的，其他正常实例也没有授权super，这个有问题的实例授予super replication client权限了，并没有解决 list index out of range

def query_print(self, instance, db_name=None, sql=''):
"""
将sql交给inception打印语法树。
"""
sql = f"""/--user={instance.user};--password={instance.password};--host={instance.host};
--port={instance.port};--enable-query-print;/
inception_magic_start;
use {db_name};
{sql}
inception_magic_commit;"""
#这里不知道为什么返回空呢，inception那边的日志正常打印的，没报错
print(str(self.query(db_name=db_name, sql=sql).to_dict()))
print_info = self.query(db_name=db_name, sql=sql).to_dict()[0]
# 兼容语法错误时errlevel=0的场景
if print_info['errlevel'] == 0 and print_info['errmsg'] == 'None':
return json.loads(_repair_json_str(print_info['query_tree']))
elif print_info['errlevel'] == 0 and print_info['errmsg'] == 'Global environment':
raise SyntaxError(f"Inception Error: {print_info['query_tree']}")
else:
raise RuntimeError(f"Inception Error: {print_info['errmsg']}")

[LeoQuote]

看起来还是 inception 或者 goinception 的问题, 再看看吧

[jdj199222]

看起来还是 inception 或者 goinception 的问题, 再看看吧
已确定原因，inception访问网络端口问题，谢谢耐心回复