[CI] JdbcCsvSpecIT and GeoJdbcCsvSpecIT failures due to too many dynamic scripts

[dimitris-athanasiou]

Build scan: https://gradle-enterprise.elastic.co/s/5k5evupqox3sc

Repro line: This seems to be related to running many tests and possible the cleanup between them. Repro lines for individual tests can be found in build scan

Reproduces locally?: No

Applicable branches: 7.16

Failure excerpt:

java.sql.SQLException: Server encountered an error [all shards failed]. [Failed to execute phase [query], all shards failed; shardFailures {[rESJZJ7kSBONpXrg22Se9Q][geo][0]: RemoteTransportException[[integTest-0][127.0.0.1:44009][indices:data/read/search[phase/query]]]; nested: GeneralScriptException[Failed to compile inline script [InternalSqlScriptUtils.floor(InternalSqlScriptUtils.div(InternalSqlScriptUtils.stDistance(InternalSqlScriptUtils.geoDocValue(doc,params.v0),InternalSqlScriptUtils.stWktToSql(params.v1)),params.v2))] using lang [painless]]; nested: CircuitBreakingException[[script] Too many dynamic script compilations within, max: [150/5m]; please use indexed, or scripts with parameters instead; this limit can be changed by the [script.max_compilations_rate] setting]; } |  
-- | --
  | at org.elasticsearch.action.search.AbstractSearchAsyncAction.onPhaseFailure(AbstractSearchAsyncAction.java:671) |  
  | at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:389) |  
  | at org.elasticsearch.action.search.AbstractSearchAsyncAction.onPhaseDone(AbstractSearchAsyncAction.java:703) |  
  | at org.elasticsearch.action.search.AbstractSearchAsyncAction.onShardFailure(AbstractSearchAsyncAction.java:472) |  
  | at org.elasticsearch.action.search.AbstractSearchAsyncAction.access$000(AbstractSearchAsyncAction.java:64) |  
  | at org.elasticsearch.action.search.AbstractSearchAsyncAction$1.onFailure(AbstractSearchAsyncAction.java:321) |  
  | at org.elasticsearch.action.ActionListener$Delegating.onFailure(ActionListener.java:66) |  
  | at org.elasticsearch.action.ActionListenerResponseHandler.handleException(ActionListenerResponseHandler.java:48) |  
  | at org.elasticsearch.action.search.SearchTransportService$ConnectionCountingHandler.handleException(SearchTransportService.java:470) |  
  | at org.elasticsearch.transport.TransportService$4.handleException(TransportService.java:742) |  
  | at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleException(TransportService.java:1309) |  
  | at org.elasticsearch.transport.TransportService$DirectResponseChannel.processException(TransportService.java:1418) |  
  | at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1392) |  
  | at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:50) |  
  | at org.elasticsearch.transport.TransportChannel.sendErrorResponse(TransportChannel.java:45) |  
  | at org.elasticsearch.action.support.ChannelActionListener.onFailure(ChannelActionListener.java:40) |  
  | at org.elasticsearch.action.ActionRunnable.onFailure(ActionRunnable.java:77) |  
  | at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:28) |  
  | at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:33) |  
  | at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:737) |  
  | at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:26) |  
  | at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) |  
  | at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) |  
  | at java.base/java.lang.Thread.run(Thread.java:831) |  
  | Caused by: CircuitBreakingException[[script] Too many dynamic script compilations within, max: [150/5m]; please use indexed, or scripts with parameters instead; this limit can be changed by the [script.max_compilations_rate] setting] |  
  | at org.elasticsearch.script.ScriptCache.checkCompilationLimit(ScriptCache.java:165) |  
  | at org.elasticsearch.script.ScriptCache.lambda$compile$0(ScriptCache.java:94) |  
  | at org.elasticsearch.common.cache.Cache.computeIfAbsent(Cache.java:419) |  
  | at org.elasticsearch.script.ScriptCache.compile(ScriptCache.java:83) |  
  | at org.elasticsearch.script.ScriptService.compile(ScriptService.java:398) |  
  | at org.elasticsearch.index.query.SearchExecutionContext.compile(SearchExecutionContext.java:560) |  
  | at org.elasticsearch.search.aggregations.support.AggregationContext$ProductionAggregationContext.compile(AggregationContext.java:423) |  
  | at org.elasticsearch.search.aggregations.support.ValuesSourceConfig.createScript(ValuesSourceConfig.java:215) |  
  | at org.elasticsearch.search.aggregations.support.ValuesSourceConfig.internalResolve(ValuesSourceConfig.java:126) |  
  | at org.elasticsearch.search.aggregations.support.ValuesSourceConfig.resolve(ValuesSourceConfig.java:59)

[elasticmachine]

Pinging @elastic/es-ql (Team:QL)

[Luegg]

It's a mystery to me how this error could arise, script.disable_max_compilations_rate is true on the IT nodes so the limit should not be enforced.