【 使用环境 】生产环境
【 OB or 其他组件 】OB
【 使用版本 】5.7.25-OceanBase_CE-v4.3.5.2
【问题描述】执行sql报错：
4020 No join order generated
sql内容如下：
SELECT
T201.diag_module AS F185,
T24.diagnostician_id AS F0,
T25.diagnostician_type_id AS F2,
T25.diagnostician_type_name AS F3,
T454.grade_id AS F423,
T454.grade_name AS F424,
T454.grade_show_seq AS F425,
T454.grade_type_id AS F426,
T454.grade_type_name AS F427,
T454.grade_type_show_seq AS F428,
T56310.school_area_id AS F56169,
T56310.school_area_name AS F56170,
T56310.school_area_show_seq AS F56171
FROM
dw_actual_answer_naire_question T203
INNER JOIN dw_question T190 ON T203.q_id = T190.q_id
INNER JOIN dw_diag_module T201 ON T190.diag_module = T201.diag_module
INNER JOIN dw_diag_obj T191 ON T203.diag_obj_id = T191.diag_obj_id
AND T203.school_code = T191.school_code
AND T203.school_term_id = T191.school_term_id
INNER JOIN dw_school_term T27 ON T191.school_term_id = T27.school_term_id
INNER JOIN dw_school T28 ON T28.school_code = T191.school_code
INNER JOIN dw_diag_obj_rpt_tag T198 ON T191.diag_obj_id = T198.diag_obj_id
INNER JOIN dw_diag_obj_type T199 ON T191.diag_obj_type_id = T199.diag_obj_type_id
INNER JOIN dw_diagnostician T24 ON T203.diagnostician_id = T24.diagnostician_id
AND T203.school_code = T24.school_code
AND T203.school_term_id = T24.school_term_id
INNER JOIN dw_tician_type T29 ON T29.diagnostician_id = T24.diagnostician_id
INNER JOIN dw_diagnostician_type T25 ON T29.diagnostician_type_id = T25.diagnostician_type_id
INNER JOIN dw_tician_grade T454 ON T24.diagnostician_id = T454.diagnostician_id
INNER JOIN dw_tician_school_area T56310 ON T24.diagnostician_id = T56310.diagnostician_id
WHERE
T28.school_code IN ('bj8z', 'ceshi2')
AND T27.school_term_id IN ('2521')
AND T201.diag_module IN (
'组织与领导-专项诊断'
)
AND T203.school_code IN ('bj8z', 'ceshi2')
AND T199.diag_obj_type_id IN ('JXBYB')
AND T198.rpt_tag IN ('全部')
AND T25.diagnostician_type_id IN ('JZG')
SHOW VARIABLES like ‘version_comment’;
alter system set enable_rich_error_msg=true;
obclient [test]> select count(*) from t2;
ERROR 1146 (42S02): Table ‘test.t2’ doesn’t exist
[xx.xx.xx.1:2882] [2024-04-13 20:10:20.292087] [YB420BA1CC68-000615A0A8EA5E38-0-0]
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” rootservice.log
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” observer.log
alter system set enable_rich_error_msg=false;
a. 登录系统租户，打开enable_rich_error_msg 参数
b. 登录业务租户，执行报错 SQL 语句，会直接返回执行节点 IP 和 trace_id 信息。
c. 去 xx.xx.xx.1 节点过滤日志，如果最新日志无法过滤到，可以正则匹配多个日志进行过滤
d. 获取完日志信息后，关闭enable_rich_error_msg参数
