search

dataease / dataease

🔥 人人可用的开源 BI 工具，Tableau、帆软的开源替代。
https://dataease.io/
GNU General Public License v3.0
17.43k stars 3.18k forks source link

[Question]v2新增数据集逾时，但v1却正常 #11534

Open tzengshinfu opened 1 month ago

tzengshinfu commented 1 month ago

请描述您的问题. 场景叙述：有一table原本作为存放系统稽核纪录用，因资料笔数过多影响系统本身效能，再次移出作为冷资料备存，并使用DataEase查询之。 数据来源：MySQL 8.0.28 资料笔数：约2亿1千2百万笔 测试版本：DataEase v2.9和v1.18 测试结果：v1.18能正常新增数据集和仪表板，但v2.9在新增数据集时点击“保存”后无回应也无报错讯息， 从浏览器控制台看到逾时错误 image_20240814_094750

从后端log也能看到以下逾时错误 2024-08-14 09:21:18.707 INFO --- [nio-8100-exec-7] i.d.dataset.manage.DatasetSQLManage : calcite origin sql: SELECT t_a_0.files_audit_log_keyASf_d02b91fd118c8474,t_a_0.computer_guidASf_43b5b3c12d896a93,t_a_0.ipASf_fb29cae42a44a80f,t_a_0.useridASf_c9f15447af5a30d3,t_a_0.computer_nameASf_8606eeccaba1c43c,t_a_0.loginidASf_5182571ece664b31,t_a_0.organization_nameASf_e7ab300732547472,t_a_0.org_keyASf_074b32dd70804f1f,t_a_0.rule_nameASf_f0fa3c1fc330318d,t_a_0.audit_typeASf_dde20d015da499aa,t_a_0.audit_actionASf_c6ca6a385d2cbcf6,t_a_0.audit_modeASf_4f14c4bd41823145,t_a_0.audit_valueASf_5952b3a12fd43a51,t_a_0.affect_fileASf_2b94c94df5657a7c,t_a_0.affect_dateASf_95625daba27243e5,t_a_0.affect_timeASf_9b97dd7598f84d94,t_a_0.affect_filepathASf_712b619703b55328,t_a_0.affectuserASf_e750da619fa0fbcb,t_a_0.extstornameASf_71bc90e4677c8881,t_a_0.extstorusbidASf_f620a1c6b3c92882,t_a_0.custodianASf_375551f458a00665,t_a_0.custodian_orgASf_ef31fb4bd96e65d6,t_a_0.uncpathASf_44ca5f57c70c3519,t_a_0.drivetypeASf_17fe9af2bf8692b1,t_a_0.processASf_11a2e2148a00179f,t_a_0.smartit_serialASf_932465d90ad5c22c,t_a_0.affect_file_unicodeASf_2875ef3c216cd7e7,t_a_0.affect_filepath_unicodeASf_f8b52d430694abb1,t_a_0.affect_filesizeASf_f76055d69391abe9,t_a_0.rule_typeASf_0982d182b70edf63,t_a_0.sourcepathASf_a8201f0385fb7411,t_a_0.modify_dateASf_b8337b46d345c66dFROM s_a_1008077892493643776.files_audit_log_20240804t_a_0 2024-08-14 09:21:18.707 INFO --- [nio-8100-exec-7] i.d.dataset.manage.DatasetDataManage : calcite data count sql: SELECT COUNT(*) FROM (SELECTt_a_0.f_d02b91fd118c8474ASf_ax_0,t_a_0.f_43b5b3c12d896a93ASf_ax_1,t_a_0.f_fb29cae42a44a80fASf_ax_2,t_a_0.f_c9f15447af5a30d3ASf_ax_3,t_a_0.f_8606eeccaba1c43cASf_ax_4,t_a_0.f_5182571ece664b31ASf_ax_5,t_a_0.f_e7ab300732547472ASf_ax_6,t_a_0.f_074b32dd70804f1fASf_ax_7,t_a_0.f_f0fa3c1fc330318dASf_ax_8,t_a_0.f_dde20d015da499aaASf_ax_9,t_a_0.f_c6ca6a385d2cbcf6ASf_ax_10,t_a_0.f_4f14c4bd41823145ASf_ax_11,t_a_0.f_5952b3a12fd43a51ASf_ax_12,t_a_0.f_2b94c94df5657a7cASf_ax_13,t_a_0.f_95625daba27243e5ASf_ax_14,t_a_0.f_9b97dd7598f84d94ASf_ax_15,t_a_0.f_712b619703b55328ASf_ax_16,t_a_0.f_e750da619fa0fbcbASf_ax_17,t_a_0.f_71bc90e4677c8881ASf_ax_18,t_a_0.f_f620a1c6b3c92882ASf_ax_19,t_a_0.f_375551f458a00665ASf_ax_20,t_a_0.f_ef31fb4bd96e65d6ASf_ax_21,t_a_0.f_44ca5f57c70c3519ASf_ax_22,t_a_0.f_17fe9af2bf8692b1ASf_ax_23,t_a_0.f_11a2e2148a00179fASf_ax_24,t_a_0.f_932465d90ad5c22cASf_ax_25,t_a_0.f_2875ef3c216cd7e7ASf_ax_26,t_a_0.f_f8b52d430694abb1ASf_ax_27, CAST(t_a_0.f_f76055d69391abe9AS DECIMAL(26, 8)) ASf_ax_28,t_a_0.f_0982d182b70edf63ASf_ax_29,t_a_0.f_a8201f0385fb7411ASf_ax_30,t_a_0.f_b8337b46d345c66dASf_ax_31FROM (SELECT t_a_0.files_audit_log_keyASf_d02b91fd118c8474,t_a_0.computer_guidASf_43b5b3c12d896a93,t_a_0.ipASf_fb29cae42a44a80f,t_a_0.useridASf_c9f15447af5a30d3,t_a_0.computer_nameASf_8606eeccaba1c43c,t_a_0.loginidASf_5182571ece664b31,t_a_0.organization_nameASf_e7ab300732547472,t_a_0.org_keyASf_074b32dd70804f1f,t_a_0.rule_nameASf_f0fa3c1fc330318d,t_a_0.audit_typeASf_dde20d015da499aa,t_a_0.audit_actionASf_c6ca6a385d2cbcf6,t_a_0.audit_modeASf_4f14c4bd41823145,t_a_0.audit_valueASf_5952b3a12fd43a51,t_a_0.affect_fileASf_2b94c94df5657a7c,t_a_0.affect_dateASf_95625daba27243e5,t_a_0.affect_timeASf_9b97dd7598f84d94,t_a_0.affect_filepathASf_712b619703b55328,t_a_0.affectuserASf_e750da619fa0fbcb,t_a_0.extstornameASf_71bc90e4677c8881,t_a_0.extstorusbidASf_f620a1c6b3c92882,t_a_0.custodianASf_375551f458a00665,t_a_0.custodian_orgASf_ef31fb4bd96e65d6,t_a_0.uncpathASf_44ca5f57c70c3519,t_a_0.drivetypeASf_17fe9af2bf8692b1,t_a_0.processASf_11a2e2148a00179f,t_a_0.smartit_serialASf_932465d90ad5c22c,t_a_0.affect_file_unicodeASf_2875ef3c216cd7e7,t_a_0.affect_filepath_unicodeASf_f8b52d430694abb1,t_a_0.affect_filesizeASf_f76055d69391abe9,t_a_0.rule_typeASf_0982d182b70edf63,t_a_0.sourcepathASf_a8201f0385fb7411,t_a_0.modify_dateASf_b8337b46d345c66dFROMfiles_audit_log_20240804t_a_0) ASt_a_0) t_a_0 2024-08-14 09:22:11.882 ERROR --- [nio-8100-exec-7] i.d.exception.GlobalExceptionHandler : Method[deExceptionHandler][SQL ERROR: Statement cancelled due to timeout or client request]

yayanpei-fit2cloud commented 1 month ago

感谢反馈，建议把下面的两个参数调大一些试试。 image image

tzengshinfu commented 1 month ago

您好，谢谢回覆， 尝试将“请求报时”和“查询超时”都改为9999秒， 并重启DataEase容器， image_20240815_084710 image_20240815_084719

但还是发生超时异常，而且09：09：03->09：09：51不到1分钟。 2024-08-15 09:09:03.619 INFO --- [nio-8100-exec-5] i.d.dataset.manage.DatasetSQLManage : calcite origin sql: ...... 2024-08-15 09:09:03.620 INFO --- [nio-8100-exec-5] i.d.dataset.manage.DatasetDataManage : calcite data count sql: ...... 2024-08-15 09:09:51.393 ERROR --- [nio-8100-exec-5] i.d.exception.GlobalExceptionHandler : Method[deExceptionHandler][SQL ERROR: Statement cancelled due to timeout or client request]

目前在看MySQL超时设定 image_20240815_100455

只是觉得奇怪，v1为什么可以正常新增

tzengshinfu commented 1 month ago

今天再测试， 一开始不会出现”SQL ERROR: Statement cancelled due to timeout or client request“的错误讯息， 但等了1小时画面还是停置在保存数据中的确认对话框 1

Chrome开发者工具可看到pending的request 2

再查MySQL正在执行的SQL指令（show full processlist），发现有一笔计算纪录笔数指令刚好也是执行了1小时 SELECT COUNT(*) FROM ( SELECT t_a_0.f_d02b91fd118c8474ASf_ax_0, t_a_0.f_43b5b3c12d896a93ASf_ax_1, t_a_0.f_fb29cae42a44a80fASf_ax_2, t_a_0.f_c9f15447af5a30d3ASf_ax_3, t_a_0.f_8606eeccaba1c43cASf_ax_4, t_a_0.f_5182571ece664b31ASf_ax_5, t_a_0.f_e7ab300732547472ASf_ax_6, t_a_0.f_074b32dd70804f1fASf_ax_7, t_a_0.f_f0fa3c1fc330318dASf_ax_8, t_a_0.f_dde20d015da499aaASf_ax_9, t_a_0.f_c6ca6a385d2cbcf6ASf_ax_10, t_a_0.f_4f14c4bd41823145ASf_ax_11, t_a_0.f_5952b3a12fd43a51ASf_ax_12, t_a_0.f_2b94c94df5657a7cASf_ax_13, t_a_0.f_95625daba27243e5ASf_ax_14, t_a_0.f_9b97dd7598f84d94ASf_ax_15, t_a_0.f_712b619703b55328ASf_ax_16, t_a_0.f_e750da619fa0fbcbASf_ax_17, t_a_0.f_71bc90e4677c8881ASf_ax_18, t_a_0.f_f620a1c6b3c92882ASf_ax_19, t_a_0.f_375551f458a00665ASf_ax_20, t_a_0.f_ef31fb4bd96e65d6ASf_ax_21, t_a_0.f_44ca5f57c70c3519ASf_ax_22, t_a_0.f_17fe9af2bf8692b1ASf_ax_23, t_a_0.f_11a2e2148a00179fASf_ax_24, t_a_0.f_932465d90ad5c22cASf_ax_25, t_a_0.f_2875ef3c216cd7e7ASf_ax_26, t_a_0.f_f8b52d430694abb1ASf_ax_27, CAST(t_a_0.f_f76055d69391abe9AS DECIMAL(26, 8)) ASf_ax_28, t_a_0.f_0982d182b70edf63ASf_ax_29, t_a_0.f_a8201f0385fb7411ASf_ax_30, t_a_0.f_b8337b46d345c66dASf_ax_31 FROM ( SELECT t_a_0.files_audit_log_keyASf_d02b91fd118c8474, t_a_0.computer_guidASf_43b5b3c12d896a93, t_a_0.ipASf_fb29cae42a44a80f, t_a_0.useridASf_c9f15447af5a30d3, t_a_0.computer_nameASf_8606eeccaba1c43c, t_a_0.loginidASf_5182571ece664b31, t_a_0.organization_nameASf_e7ab300732547472, t_a_0.org_keyASf_074b32dd70804f1f, t_a_0.rule_nameASf_f0fa3c1fc330318d, t_a_0.audit_typeASf_dde20d015da499aa, t_a_0.audit_actionASf_c6ca6a385d2cbcf6, t_a_0.audit_modeASf_4f14c4bd41823145, t_a_0.audit_valueASf_5952b3a12fd43a51, t_a_0.affect_fileASf_2b94c94df5657a7c, t_a_0.affect_dateASf_95625daba27243e5, t_a_0.affect_timeASf_9b97dd7598f84d94, t_a_0.affect_filepathASf_712b619703b55328, t_a_0.affectuserASf_e750da619fa0fbcb, t_a_0.extstornameASf_71bc90e4677c8881, t_a_0.extstorusbidASf_f620a1c6b3c92882, t_a_0.custodianASf_375551f458a00665, t_a_0.custodian_orgASf_ef31fb4bd96e65d6, t_a_0.uncpathASf_44ca5f57c70c3519, t_a_0.drivetypeASf_17fe9af2bf8692b1, t_a_0.processASf_11a2e2148a00179f, t_a_0.smartit_serialASf_932465d90ad5c22c, t_a_0.affect_file_unicodeASf_2875ef3c216cd7e7, t_a_0.affect_filepath_unicodeASf_f8b52d430694abb1, t_a_0.affect_filesizeASf_f76055d69391abe9, t_a_0.rule_typeASf_0982d182b70edf63, t_a_0.sourcepathASf_a8201f0385fb7411, t_a_0.modify_dateASf_b8337b46d345c66d FROM files_audit_log_20240804t_a_0 ) ASt_a_0 ) t_a_0

因为该表格数量实在太多，所以卡在这个SQL指令

而在2小时47分（9999秒）之后也如期出现”SQL ERROR: Statement cancelled due to timeout or client request“ 后续会将这个表再优化（分表之类）

但还是觉得奇怪，v1为什么可以正常新增😅