sql query cannot be closed, resulting in cluster exception，Restart Cluster Recovery
Elasticsearch version: 7.9.3
query
GET _sql?format=txt
{"query": """
SELECT app,tag,count(1) as c FROM "test"
group by app,tag
order by c desc
"""}
error
{
"error" : {
"root_cause" : [
{
"type" : "sql_illegal_argument_exception",
"reason" : "The default limit [65535] for aggregate sorting has been reached; please specify a LIMIT"
}
],
"type" : "sql_illegal_argument_exception",
"reason" : "The default limit [65535] for aggregate sorting has been reached; please specify a LIMIT"
},
"status" : 500
}
cluster _cat/tasks?v
action task_id parent_task_id type start_time timestamp running_time ip node
indices:data/read/sql dWoWAYBgTIS7-HQeKmKwrQ:75066 - transport 1640862717973 11:11:57 6.1m 172.20.124.55 test-7-9-3-es-default-0
indices:data/read/search dWoWAYBgTIS7-HQeKmKwrQ:76457 - transport 1640863084733 11:18:04 1.1s 172.20.124.55 test-7-9-3-es-default-0
_tasks?
{
"completed" : false,
"task" : {
"node" : "dWoWAYBgTIS7-HQeKmKwrQ",
"id" : 75066,
"type" : "transport",
"action" : "indices:data/read/sql",
"description" : "SQL [\nSELECT app,tag,count(1) as c FROM \"test\"\ngroup by app,tag\norder by c desc\n][null]",
"start_time_in_millis" : 1640862717973,
"running_time_in_nanos" : 199331352367,
"cancellable" : false,
"headers" : { }
}
}
{
"completed" : false,
"task" : {
"node" : "dWoWAYBgTIS7-HQeKmKwrQ",
"id" : 75820,
"type" : "transport",
"action" : "indices:data/read/search",
"description" : "indices[test], types[], search_type[QUERY_THEN_FETCH], source[{\"size\":0,\"timeout\":\"45s\",\"_source\":false,\"stored_fields\":\"_none_\",\"aggregations\":{\"groupby\":{\"composite\":{\"size\":1000,\"sources\":[{\"4a3c589\":{\"terms\":{\"field\":\"app\",\"missing_bucket\":true,\"order\":\"asc\"}}},{\"918103b0\":{\"terms\":{\"field\":\"tag\",\"missing_bucket\":true,\"order\":\"asc\"}}}],\"after\":{\"918103b0\":\"17111\",\"4a3c589\":\"17110\"}}}}}]",
"start_time_in_millis" : 1640862899924,
"running_time_in_nanos" : 17403653173,
"cancellable" : true,
"headers" : { }
}
}
Elasticsearch logs
[root@node16 tmp]# kubectl logs -f test-7-9-3-es-default-0
"\tat org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:344) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.action.search.FetchSearchPhase.moveToNextPhase(FetchSearchPhase.java:231) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.action.search.FetchSearchPhase.lambda$innerRun$1(FetchSearchPhase.java:119) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.action.search.FetchSearchPhase.innerRun(FetchSearchPhase.java:125) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.action.search.FetchSearchPhase.access$000(FetchSearchPhase.java:47) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.action.search.FetchSearchPhase$1.doRun(FetchSearchPhase.java:95) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:44) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:737) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.9.3.jar:7.9.3]",
"\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) [?:?]",
"\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) [?:?]",
"\tat java.lang.Thread.run(Thread.java:832) [?:?]"] }
{"type": "server", "timestamp": "2021-12-30T11:22:13,442Z", "level": "WARN", "component": "r.suppressed", "cluster.name": "test-7-9-3", "node.name": "test-7-9-3-es-default-0", "message": "path: /_sql, params: {pretty=true, format=txt}", "cluster.uuid": "71JWVjUCRVGbAA9JCpZYWw", "node.id": "dWoWAYBgTIS7-HQeKmKwrQ" ,
"stacktrace": ["org.elasticsearch.xpack.sql.SqlIllegalArgumentException: The default limit [65535] for aggregate sorting has been reached; please specify a LIMIT",
"at org.elasticsearch.xpack.sql.execution.search.Querier$LocalAggregationSorterListener.consumeRowSet(Querier.java:252) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.xpack.sql.execution.search.Querier$LocalAggregationSorterListener.onResponse(Querier.java:228) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.xpack.sql.execution.search.Querier$LocalAggregationSorterListener.onResponse(Querier.java:184) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:63) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.xpack.sql.execution.search.CompositeAggCursor.handle(CompositeAggCursor.java:195) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.xpack.sql.execution.search.CompositeAggCursor$1.onResponse(CompositeAggCursor.java:140) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.xpack.sql.execution.search.CompositeAggCursor$1.onResponse(CompositeAggCursor.java:137) [x-pack-sql-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:89) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:83) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.AbstractSearchAsyncAction.sendSearchResponse(AbstractSearchAsyncAction.java:545) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.ExpandSearchPhase.run(ExpandSearchPhase.java:117) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.AbstractSearchAsyncAction.executePhase(AbstractSearchAsyncAction.java:350) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:344) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.FetchSearchPhase.moveToNextPhase(FetchSearchPhase.java:231) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.FetchSearchPhase.lambda$innerRun$1(FetchSearchPhase.java:119) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.FetchSearchPhase.innerRun(FetchSearchPhase.java:125) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.FetchSearchPhase.access$000(FetchSearchPhase.java:47) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.action.search.FetchSearchPhase$1.doRun(FetchSearchPhase.java:95) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:44) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:737) [elasticsearch-7.9.3.jar:7.9.3]",
"at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [elasticsearch-7.9.3.jar:7.9.3]",
"at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) [?:?]",
"at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) [?:?]",
"at java.lang.Thread.run(Thread.java:832) [?:?]"] }
, Thanks.