You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@kylin.apache.org by May Zhou <ma...@foxmail.com> on 2018/11/15 14:21:31 UTC
回复: 回复:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
Hi Feng,
It can be inferred from your error message, "Query returned 5078491 rows exceeds threshold 5000000," that the query tried to return 5078491 rows.
As far as your concern about why this query would return more than 5000000 rows, I think you could try the following query to set a limit to the query to see whether it throws the same error.
select KMS_DT.YMONTH,V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME,count(*) from V_KMS_DAY_ALLBILLSV4 as V_KMS_DAY_ALLBILLSV4 inner join KMS_DT as KMS_DT on V_KMS_DAY_ALLBILLSV4.BIZDATE = KMS_DT.TDAY inner join V_KMSSTATION_V4 as V_KMSSTATION_V4 on V_KMS_DAY_ALLBILLSV4.STATIONID = V_KMSSTATION_V4.ID inner join V_KMS_BASECARMODEL as V_KMS_BASECARMODEL on V_KMS_DAY_ALLBILLSV4.EXT4 = V_KMS_BASECARMODEL.ID where KMS_DT.YMONTH in ('201801', '201802', '201803', '201804', '201805', '201806','201807', '201808', '201809') group by KMS_DT.YMONTH, V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME limit 2000
And I have reproduced your issue in my environment as i set "kylin.query.max-return-row" to 1 in the project-level, and try to execute a simple query and it throws the similar error "Query returned 12 rows exceeds threshold 1 while executing SQL: "select * from kylin_sales LIMIT 50000"".
Thus, this parameter is valid.
BRs,
May
------------------ 原始邮件 ------------------
发件人: "潘博存"<pa...@teld.cn>;
发送时间: 2018年11月15日(星期四) 晚上9:48
收件人: "May Zhou"<ma...@foxmail.com>;"user@kylin.apache.org 冯志山"<fe...@apache.org>;
抄送: "潘博存"<pa...@teld.cn>;
主题: 回复:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
the sql execute result count only 2000,why report more thsb 5000000 rows error?
------------------------------------------------------------------
发件人:May Zhou<ma...@foxmail.com>
日 期:2018年11月15日 20:50:42
收件人:user@kylin.apache.org 冯志山<fe...@teld.cn>; user<us...@kylin.apache.org>; shaofengshi<sh...@apache.org>
抄 送:潘博存<pa...@teld.cn>
主 题:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
Hi Feng,
(Sorry if interrupt you, but i am wondering whether my reply has been sent)
Have you tried to increase the value of this parameter `kylin.query.max-return-row`,which specifies the maximum number of rows returned by the query and the default value is 5000000.
More configuration information can be referred in Kylin's Document: https://kylin.apache.org/docs/install/configuration.html
BRs,
May
------------------ 原始邮件 ------------------
发件人: "user@kylin.apache.org 冯志山"<fe...@teld.cn>;
发送时间: 2018年11月15日(星期四) 晚上8:37
收件人: "user"<us...@apache.org>;
抄送: "潘博存"<pa...@teld.cn>;
主题: Error:Query returned 5078491 rows exceeds threshold 5000000
hi,all:
when I executing sql,I get this error:
org.apache.kylin.rest.exception.InternalErrorException: Query returned 5078491 rows exceeds threshold 5000000
My kylin version is apache-kylin-2.3.1-bin.The detail log is below:
2018-11-15 11:43:56,163 ERROR [http-bio-7070-exec-29] controller.BasicController:61 :
org.apache.kylin.rest.exception.InternalErrorException: Query returned 5078491 rows exceeds threshold 5000000
while executing SQL: "select KMS_DT.YMONTH,V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME,count(*) from V_KMS_DAY_ALLBILLSV4 as V_KMS_DAY_ALLBILLSV4 inner join KMS_DT as KMS_DT on V_KMS_DAY_ALLBILLSV4.BIZDATE = KMS_DT.TDAY inner join V_KMSSTATION_V4 as V_KMSSTATION_V4 on V_KMS_DAY_ALLBILLSV4.STATIONID = V_KMSSTATION_V4.ID inner join V_KMS_BASECARMODEL as V_KMS_BASECARMODEL on V_KMS_DAY_ALLBILLSV4.EXT4 = V_KMS_BASECARMODEL.ID where KMS_DT.YMONTH in ('201801', '201802', '201803', '201804', '201805', '201806','201807', '201808', '201809') group by KMS_DT.YMONTH, V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME"
at org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:474)
at org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:390)
at org.apache.kylin.rest.controller.QueryController.query(QueryController.java:86)
at sun.reflect.GeneratedMethodAccessor695.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:209)
at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:244)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1115)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
At first I thought it was about configuration:kylin.dictionary.max.cardinality=5000000,but this configuration disappeared after version 2.0. In additon KMS_DT.YMONTH in ('201801', '201802', '201803', '201804', '201805', '201806','201807', '201808') just return 2000 records, So I want to know why this error occurred and how to handle it.
Thanks.
Re:回复:回复:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
Posted by Ma Gang <mg...@163.com>.
The configuration "kylin.query.max-return-row" is used to protect Kylin server from OOM when there are too many results returned from HBase, there are many possible reasons may cause this issue: 1) the filter of your query cannot push down to HBase, so too many result returned. 2) the cube has derived columns defined, and the cardinality of the lookup table's primary key is ultrahigh, and your query filter or group by the derived columns. 3) there are too many regions involved in your query.
You may increase the configuration value if it is too small for your case.
At 2018-11-16 15:26:03, "冯志山" <fe...@teld.cn> wrote:
hi May,
Sorry for not replying to you in time. Thank you very much for your advice, I will try to modify the configuration next.
But I have the doubts: this 5000000 rows is metadata? or In this scenario, this pre-calculation is not functional?
Finally, could the mandatory dimensions will cause the error?
The cube is below:
picture 1
picture 2
Best regards,
Zhishan Feng
------------------------------------------------------------------
发件人:May Zhou <ma...@foxmail.com>
发送时间:2018年11月15日(星期四) 22:21
收件人:冯志山 <fe...@teld.cn>; user <us...@kylin.apache.org>; shaofengshi <sh...@apache.org>
抄 送:潘博存 <pa...@teld.cn>
主 题:回复: 回复:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
Hi Feng,
It can be inferred from your error message, "Query returned 5078491 rows exceeds threshold 5000000," that the query tried to return 5078491 rows.
As far as your concern about why this query would return more than 5000000 rows, I think you could try the following query to set a limit to the query to see whether it throws the same error.
select KMS_DT.YMONTH,V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME,count(*) from V_KMS_DAY_ALLBILLSV4 as V_KMS_DAY_ALLBILLSV4 inner join KMS_DT as KMS_DT on V_KMS_DAY_ALLBILLSV4.BIZDATE = KMS_DT.TDAY inner join V_KMSSTATION_V4 as V_KMSSTATION_V4 on V_KMS_DAY_ALLBILLSV4.STATIONID = V_KMSSTATION_V4.ID inner join V_KMS_BASECARMODEL as V_KMS_BASECARMODEL on V_KMS_DAY_ALLBILLSV4.EXT4 = V_KMS_BASECARMODEL.ID where KMS_DT.YMONTH in ('201801', '201802', '201803', '201804', '201805', '201806','201807', '201808', '201809') group by KMS_DT.YMONTH, V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME
limit 2000
And I have reproduced your issue in my environment as i set "kylin.query.max-return-row" to 1 in the project-level, and try to execute a simple query and it throws the similar error "Query returned 12 rows exceeds threshold 1 while executing SQL: "select * from kylin_sales LIMIT 50000"".
Thus, this parameter is valid.
BRs,
May
------------------ 原始邮件 ------------------
发件人: "潘博存"<pa...@teld.cn>;
发送时间: 2018年11月15日(星期四) 晚上9:48
收件人: "May Zhou"<ma...@foxmail.com>;"user@kylin.apache.org 冯志山"<fe...@apache.org>;
抄送: "潘博存"<pa...@teld.cn>;
主题: 回复:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
the sql execute result count only 2000,why report more thsb 5000000 rows error?
------------------------------------------------------------------
发件人:May Zhou<ma...@foxmail.com>
日 期:2018年11月15日 20:50:42
收件人:user@kylin.apache.org 冯志山<fe...@teld.cn>; user<us...@kylin.apache.org>; shaofengshi<sh...@apache.org>
抄 送:潘博存<pa...@teld.cn>
主 题:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
Hi Feng,
(Sorry if interrupt you, but i am wondering whether my reply has been sent)
Have you tried to increase the value of this parameter `kylin.query.max-return-row`,which specifies the maximum number of rows returned by the query and the default value is 5000000.
More configuration information can be referred in Kylin's Document: https://kylin.apache.org/docs/install/configuration.html
BRs,
May
------------------ 原始邮件 ------------------
发件人: "user@kylin.apache.org 冯志山"<fe...@teld.cn>;
发送时间: 2018年11月15日(星期四) 晚上8:37
收件人: "user"<us...@apache.org>;
抄送: "潘博存"<pa...@teld.cn>;
主题: Error:Query returned 5078491 rows exceeds threshold 5000000
hi,all:
when I executing sql,I get this error:
org.apache.kylin.rest.exception.InternalErrorException: Query returned 5078491 rows exceeds threshold 5000000
My kylin version is apache-kylin-2.3.1-bin.The detail log is below:
2018-11-15 11:43:56,163 ERROR [http-bio-7070-exec-29] controller.BasicController:61 :
org.apache.kylin.rest.exception.InternalErrorException: Query returned 5078491 rows exceeds threshold 5000000
while executing SQL: "select KMS_DT.YMONTH,V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME,count(*) from V_KMS_DAY_ALLBILLSV4 as V_KMS_DAY_ALLBILLSV4 inner join KMS_DT as KMS_DT on V_KMS_DAY_ALLBILLSV4.BIZDATE = KMS_DT.TDAY inner join V_KMSSTATION_V4 as V_KMSSTATION_V4 on V_KMS_DAY_ALLBILLSV4.STATIONID = V_KMSSTATION_V4.ID inner join V_KMS_BASECARMODEL as V_KMS_BASECARMODEL on V_KMS_DAY_ALLBILLSV4.EXT4 = V_KMS_BASECARMODEL.ID where KMS_DT.YMONTH in ('201801', '201802', '201803', '201804', '201805', '201806','201807', '201808', '201809') group by KMS_DT.YMONTH, V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME"
at org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:474)
at org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:390)
at org.apache.kylin.rest.controller.QueryController.query(QueryController.java:86)
at sun.reflect.GeneratedMethodAccessor695.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:209)
at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:244)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1115)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
At first I thought it was about configuration:kylin.dictionary.max.cardinality=5000000,but this configuration disappeared after version 2.0. In additon KMS_DT.YMONTH in ('201801', '201802', '201803', '201804', '201805', '201806','201807', '201808') just return 2000 records, So I want to know why this error occurred and how to handle it.
Thanks.
回复:回复:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
Posted by 冯志山 <fe...@teld.cn>.
hi May,
Sorry for not replying to you in time. Thank you very much for your advice, I will try to modify the configuration next.
But I have the doubts: this 5000000 rows is metadata? or In this scenario, this pre-calculation is not functional?
Finally, could the mandatory dimensions will cause the error?
The cube is below:
picture 1
picture 2
Best regards,
Zhishan Feng
------------------------------------------------------------------
发件人:May Zhou <ma...@foxmail.com>
发送时间:2018年11月15日(星期四) 22:21
收件人:冯志山 <fe...@teld.cn>; user <us...@kylin.apache.org>; shaofengshi <sh...@apache.org>
抄 送:潘博存 <pa...@teld.cn>
主 题:回复: 回复:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
Hi Feng,
It can be inferred from your error message, "Query returned 5078491 rows exceeds threshold 5000000," that the query tried to return 5078491 rows.
As far as your concern about why this query would return more than 5000000 rows, I think you could try the following query to set a limit to the query to see whether it throws the same error.
select KMS_DT.YMONTH,V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME,count(*) from V_KMS_DAY_ALLBILLSV4 as V_KMS_DAY_ALLBILLSV4 inner join KMS_DT as KMS_DT on V_KMS_DAY_ALLBILLSV4.BIZDATE = KMS_DT.TDAY inner join V_KMSSTATION_V4 as V_KMSSTATION_V4 on V_KMS_DAY_ALLBILLSV4.STATIONID = V_KMSSTATION_V4.ID inner join V_KMS_BASECARMODEL as V_KMS_BASECARMODEL on V_KMS_DAY_ALLBILLSV4.EXT4 = V_KMS_BASECARMODEL.ID where KMS_DT.YMONTH in ('201801', '201802', '201803', '201804', '201805', '201806','201807', '201808', '201809') group by KMS_DT.YMONTH, V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME
limit 2000
And I have reproduced your issue in my environment as i set "kylin.query.max-return-row" to 1 in the project-level, and try to execute a simple query and it throws the similar error "Query returned 12 rows exceeds threshold 1 while executing SQL: "select * from kylin_sales LIMIT 50000"".
Thus, this parameter is valid.
BRs,
May
------------------ 原始邮件 ------------------
发件人: "潘博存"<pa...@teld.cn>;
发送时间: 2018年11月15日(星期四) 晚上9:48
收件人: "May Zhou"<ma...@foxmail.com>;"user@kylin.apache.org 冯志山"<fe...@apache.org>;
抄送: "潘博存"<pa...@teld.cn>;
主题: 回复:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
the sql execute result count only 2000,why report more thsb 5000000 rows error?
------------------------------------------------------------------
发件人:May Zhou<ma...@foxmail.com>
日 期:2018年11月15日 20:50:42
收件人:user@kylin.apache.org 冯志山<fe...@teld.cn>; user<us...@kylin.apache.org>; shaofengshi<sh...@apache.org>
抄 送:潘博存<pa...@teld.cn>
主 题:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
Hi Feng,
(Sorry if interrupt you, but i am wondering whether my reply has been sent)
Have you tried to increase the value of this parameter `kylin.query.max-return-row`,which specifies the maximum number of rows returned by the query and the default value is 5000000.
More configuration information can be referred in Kylin's Document: https://kylin.apache.org/docs/install/configuration.html
BRs,
May
------------------ 原始邮件 ------------------
发件人: "user@kylin.apache.org 冯志山"<fe...@teld.cn>;
发送时间: 2018年11月15日(星期四) 晚上8:37
收件人: "user"<us...@apache.org>;
抄送: "潘博存"<pa...@teld.cn>;
主题: Error:Query returned 5078491 rows exceeds threshold 5000000
hi,all:
when I executing sql,I get this error:
org.apache.kylin.rest.exception.InternalErrorException: Query returned 5078491 rows exceeds threshold 5000000
My kylin version is apache-kylin-2.3.1-bin.The detail log is below:
2018-11-15 11:43:56,163 ERROR [http-bio-7070-exec-29] controller.BasicController:61 :
org.apache.kylin.rest.exception.InternalErrorException: Query returned 5078491 rows exceeds threshold 5000000
while executing SQL: "select KMS_DT.YMONTH,V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME,count(*) from V_KMS_DAY_ALLBILLSV4 as V_KMS_DAY_ALLBILLSV4 inner join KMS_DT as KMS_DT on V_KMS_DAY_ALLBILLSV4.BIZDATE = KMS_DT.TDAY inner join V_KMSSTATION_V4 as V_KMSSTATION_V4 on V_KMS_DAY_ALLBILLSV4.STATIONID = V_KMSSTATION_V4.ID inner join V_KMS_BASECARMODEL as V_KMS_BASECARMODEL on V_KMS_DAY_ALLBILLSV4.EXT4 = V_KMS_BASECARMODEL.ID where KMS_DT.YMONTH in ('201801', '201802', '201803', '201804', '201805', '201806','201807', '201808', '201809') group by KMS_DT.YMONTH, V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME"
at org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:474)
at org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:390)
at org.apache.kylin.rest.controller.QueryController.query(QueryController.java:86)
at sun.reflect.GeneratedMethodAccessor695.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:209)
at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:244)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1115)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
At first I thought it was about configuration:kylin.dictionary.max.cardinality=5000000,but this configuration disappeared after version 2.0. In additon KMS_DT.YMONTH in ('201801', '201802', '201803', '201804', '201805', '201806','201807', '201808') just return 2000 records, So I want to know why this error occurred and how to handle it.
Thanks.