oceanbase执行语句报错java.sql.SQLException: Internal error

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 使用版本 】版本 5.7.25-OceanBase_CE-v4.3.3.0,集群部署
【问题描述】之前项目使用的mysql，spring中也使用的mysql的驱动driver-class-name: com.mysql.cj.jdbc.Driver
生产环境上部署运行一段时间后，逐渐有业务报错，排查日志

The error may exist in aaa.xml

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: SELECT asset_uuid,usedepart_uuid FROM table1 WHERE asset_uuid IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )

Cause: java.sql.SQLException: Internal error

========================Exception End=============================
========================Exception Start=============================

Error querying database. Cause: java.sql.SQLException: Internal error

The error may exist in bbb.xml

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: SELECT COUNT(*) FROM (SELECT t3.uuid, t2.uuid itcompUuid, t3.uuid accountUuid, t3.source_uuid sourceUuid, t3.account_name accountName, t2.itcompName itcompName, t2.controlAdderss itcompIp, t2.citypeUuid, t4.citype_name citypeName, d.itcompDeparts, t5.account_type_name accountRoleName, g.accountGroupName, g.accountGroupUuids, k.relatedTask, IF(t3.check_pwd_time > 0, FROM_UNIXTIME(t3.check_pwd_time), ‘’) lastCheckpwdTime, t3.check_pwd_result lastCheckpwdResult FROM t_monitor_port_info t1 INNER JOIN t_monitor_itcomp t2 ON t1.assets_uuid = t2.uuid INNER JOIN t_monitor_account_info t3 ON t2.uuid = t3.itcomp_uuid LEFT JOIN t_cmdb_citype t4 ON t2.citypeUuid = t4.uuid LEFT JOIN t_monitor_account_type_init t5 ON t3.account_role_uuid = t5.uuid INNER JOIN ( SELECT d1.asset_uuid, GROUP_CONCAT(d2.depart_name) itcompDeparts FROM t_asset_usedepart_rel d1 INNER JOIN t_department_info d2 ON d1.usedepart_uuid = d2.uuid WHERE 1=1 AND d1.usedepart_uuid IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) GROUP BY d1.asset_uuid ) d ON d.asset_uuid = t2.uuid LEFT JOIN ( SELECT g1.account_uuid, GROUP_CONCAT(DISTINCT g2.name) accountGroupName, GROUP_CONCAT(DISTINCT g2.uuid) accountGroupUuids FROM asset_account_group_rel g1 LEFT JOIN asset_account_group_info g2 ON g1.group_uuid = g2.uuid GROUP BY g1.account_uuid ) g ON t3.uuid = g.account_uuid LEFT JOIN ( SELECT account_uuid, GROUP_CONCAT(DISTINCT task_name) relatedTask FROM ( SELECT k1.account_uuid, k2.task_name FROM t_checkpwd_task_detail k1 LEFT JOIN t_checkpwd_task k2 ON k1.task_uuid = k2.uuid where k2.task_type=? UNION ALL SELECT k5.account_uuid, k4.task_name FROM t_checkpwd_account_group k3 LEFT JOIN t_checkpwd_task k4 ON k3.task_uuid = k4.uuid LEFT JOIN asset_account_group_rel k5 ON k3.account_group_uuid = k5.group_uuid where k4.task_type=? ) k6 GROUP BY account_uuid ) k ON t3.uuid = k.account_uuid WHERE t1.status != 4 AND LOCATE(‘3’, t3.module_codes) > 0 AND t2.status != 4 AND t3.status = 1 AND t3.password_type = 0 AND (t3.snmpCWordOr = ‘’ OR t3.snmpCWordOr IS NULL) AND (t3.snmpCWordRw = ‘’ OR t3.snmpCWordRw IS NULL) AND t3.attribute = 1 AND !(t2.device_type_uuid = ‘tdevicetypeinit00000000000000002’ AND t5.uuid = ‘bf45d38aecdf4b9eafb7288adc74da8d’ AND t3.account_name = ‘root_su’) AND t3.account_role_uuid IN ( ? ) AND t2.citypeUuid IN ( ? , ? , ? , ? , ? ) AND ( (t2.device_type_uuid = ‘tdevicetypeinit00000000000000001’ AND t1.protocol_uuid IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ) OR ((t2.device_type_uuid IS NULL OR t2.device_type_uuid != ‘tdevicetypeinit00000000000000001’) AND t1.protocol_uuid IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ) ) AND t3.uuid NOT IN ( SELECT account_uuid FROM t_checkpwd_task_detail WHERE task_uuid = ? ) GROUP BY t3.uuid ORDER BY CONVERT(t3.account_name USING GBK) COLLATE gbk_bin desc) TOTAL

Cause: java.sql.SQLException: Internal error

========================Exception End=============================
已经排查，原始数据无问题

#3

麻烦使用obdiag获取一下相关sql信息

SQL 执行出错， 此处env中的trace_id对应gv$ob_sql_audit的trace_id

obdiag gather scene run --scene=observer.sql_err --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”

#4

不是本地环境，无法提供
目前发现所有复杂的sql，包含in,exists,group by ,order by ,group_concat都报错

#5

）设置trace信息
SET ob_enable_show_trace=‘ON’;

2）执行sql。

3）获取上个命令的trace
select last_trace_id();

4）获取trace对应的节点
select query_sql,svr_ip from gv$ob_sql_audit where trace_id=‘第三步获取的trace信息’；

5）取对应的svr_ip节点 过滤日志
grep “第三步获取的trace信息” observer.log*
grep “第三步获取的trace信息” rootservice.log*

6）提供日志信息即可。