Kibana Canvas SQL Error

I just Updated to Elasticsearch 7.2 and now I'm getting this error when using Canvas with Elasticsearch SQL data:

Whoops! Expression failed
Expression failed with the message:

[essql] > Unexpected error from Elasticsearch: [illegal_argument_exception] Illegal Capacity: -1

Before the update it was working correctly, not sure if it's directly connected to the new update or not, but seems probable.
The SQL data query must correct because it use to work (yesterday and before)

I could not find any help regarding to this error, does anyone have a clue?

Hi @AdrianSanchezLopez,

Can you share your SQL query?

Hi @tims

I am getting the same error for the following essql query:

SELECT "updateDate", "searchBaseType", "summary", "hostmessage.messageString", "hostmessage.responseString", "transaction.fromOrderId", "transaction.toOrderId", "transaction.fromLocationId", "transaction.toLocationId", "transaction.transactionType" FROM "wm6-orderstatusaudittrail" order by canvasTimefilterField desc

Elastic throws:

  "error": {
    "root_cause": [
        "type": "illegal_argument_exception",
        "reason": "Illegal Capacity: -5"
    "type": "illegal_argument_exception",
    "reason": "Illegal Capacity: -5"
  "status": 400

This happens regardless of wheter I perform the query from Canvas or from the Kibana Management Console.

After setting the logging level of Elasticsearch to DEBUG I got the following Stacktrace:

[2019-07-01T11:00:13,668][DEBUG][r.suppressed             ] [SCHB7M-000461] path: /_sql, params: {pretty=, format=txt}
java.lang.IllegalArgumentException: Illegal Capacity: -5
        at java.util.ArrayList.<init>( ~[?:1.8.0_202]
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.buildIndices( ~[?:?]
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.mergedMappings( ~[?:?]
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3( ~[?:?]
        at org.elasticsearch.action.ActionListener$1.onResponse( ~[elasticsearch-7.2.0.jar:7.2.0]
        at$1.onResponse( ~[elasticsearch-7.2.0.jar:7.2.0]
        at$1.onResponse( ~[elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1( ~[elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse( [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse( [elasticsearch-7.2.0.jar:7.2.0]
        at$1.onResponse( [elasticsearch-7.2.0.jar:7.2.0]
        at$1.onResponse( [elasticsearch-7.2.0.jar:7.2.0]
        at$AsyncSingleAction$2.handleResponse( [elasticsearch-7.2.0.jar:7.2.0]
        at$AsyncSingleAction$2.handleResponse( [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleResponse( [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.processResponse( [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse( [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.transport.TaskTransportChannel.sendResponse( [elasticsearch-7.2.0.jar:7.2.0]
        at [elasticsearch-7.2.0.jar:7.2.0]
        at [elasticsearch-7.2.0.jar:7.2.0]
        at$1.doRun( [elasticsearch-7.2.0.jar:7.2.0]
        at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun( [elasticsearch-7.2.0.jar:7.2.0]
        at [elasticsearch-7.2.0.jar:7.2.0]
        at java.util.concurrent.ThreadPoolExecutor.runWorker( [?:1.8.0_202]
        at java.util.concurrent.ThreadPoolExecutor$ [?:1.8.0_202]
        at [?:1.8.0_202]

Hope this helps!

Thanks and best regards,

Also it seems to be a problem with index aliases: Queries which query only one index seem to work fine but the "wm6-orderstatusaudittrail" index from above is an alias over three different indices and any essql query directed at that alias seems to throw the exeption above..

Hi all,
same problem here even with the simplest search on index patterns.
Same problem even with Elasticsearch Docs datasource.

And I confirm that with a single index instead of an index pattern everything works fine

1 Like

@tarkin67 any chance you could share the mapping of the indices (all three of them) you used in the test, please? This is definitely an ES SQL issue, judging by the stacktrace. But, a simple test I performed didn't reproduce this issue.

Sure, you can access it here, had to upload it there because of the char limit on posts.. As you can see, the alias is directed at more than three indices, sorry for the confusion.

Hope this helps!

I can confirm that it is related to the number of index on the same index template.
When using SQL on this index pattern:
on theses indexes:
I get the error. But if I do a query to a single index without pattern it works.
I did a workaround by re-indexing 03, 04, 05 onto the same index, and deleting the old ones.
So I end up with "index-2019" and "index-2019-06" and with only those it seems to work.
But the problem is still there until I get 4 indexes again ..

@tarkin67 thank you, I can reproduce it now.

1 Like

I've created this bug since I am also seeing this issue with non-SQL queries, and Andrei's fix appears to be SQL-specific:

1 Like

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.