【7.0.01288.0】Mysql数据库与ES数据库使用参数报错

Eden.Sun · 创建数据集 - 威卡威商业智能 - Google Chrome 2024-09-19 17-39-03.zip

你好，

我需要用业务数据库与ES数据库的数据进行join，需要通过时间范围传参保证数据量可控
单独使用ES数据集或Mysq数据集功能都正常，但两个数据集join后（4种join都试过了），只要时间范围内有一个数据集没有数据，系统就会报错，而且无法保存

客户现在急需看到该报表，还请尽快帮忙排查一下

感谢

详情请查看附件视频

Eden.Sun · Eden.Sun

本帖最后由 Eden.Sun 于 2024-9-26 16:27 编辑

Pirit_Xu 发表于 2024-9-26 16:21
葡萄城：ElasticSearchDSL;
ES版本：7.10.2

好的我们在继续看一下。

您的过滤人条件是在 filter 中写的。

您在must中来指定index试试，如下：

{
  "query": {
"bool": {
   "must": [
      {
      "match": {
         "_index": "javen_test"
      }
      }

}
  }
}

lucas.Yan · lucas.Yan

看您的视频后，感觉您是通过自定义表写的sql然后利用参数添加的筛选和查询，当某个查询的结果为空的时候，此时使用join链接会报错。
可以提供一下您的数据集文件吗？我们需要更多的信息来判断如何解决这样的问题。

Pirit_Xu · Pirit_Xu

lucas.Yan 发表于 2024-9-19 18:24
看您的视频后，感觉您是通过自定义表写的sql然后利用参数添加的筛选和查询，当某个查询的结果为空的时候， ...

还是造一下数据吧两个数据集中字段join一下，比如Mysql中 snCode，startTime, endTime; ES中 snCode，eventTime，result；用snCode join，使用时间过滤

lucas.Yan · lucas.Yan

早上好，我在本地自己构建了一个测试环境，使用sql和参数配合进行时间的筛选，然而并没有复现出报错的问题。如果您的数据量不是很大，可以尝试不在sql中进行过滤。转而使用数据集的数据过滤器功能。

image.png820357055.png

您尝试一下上面这种方式。

Pirit_Xu · Pirit_Xu

补充一下后台报错信息，麻烦帮忙看看，很不稳定

2024-09-24 07:47:51.144+00:00 [ERR] 0HN6SD573TH2R:00000008 Grapecity.Enterprise.Reporting.Plugin.V2.Controllers.ReportsController Failed to get report parameters values. Error: An error occurred during report fail check procedure: Specified data inaccessible: Server inner error on dataset executor. Exception detail:Meta:_gcErrorCode=E_80029;
_gcErrorCategory=SqlExecutor;

GrapeCity.Data.DataSource.Common.Abstraction.Exceptions.GcException: Internal executor error. Method or code should not be reached.
at GrapeCity.Enterprise.Data.DataSource.Common.SqlExecutor.Core.JoinConstraintOptimizer.<ParseBooleanExpression>b__9_3(ColumnIdentity ci)
at GrapeCity.Enterprise.Data.DataSource.Common.SqlExecutor.Core.ExecuteContext`1.ExecuteColumnExpContext.MakeFunc()
at GrapeCity.Enterprise.Data.DataSource.Common.SqlExecutor.Core.JoinConstraintOptimizer.ParseBooleanExpression(IExpression expression, JoinConstraintOptimizationResult result)
at GrapeCity.Enterprise.Data.DataSource.Common.SqlExecutor.Core.JoinConstraintOptimizer.Optimize(IExpression expression)
at GrapeCity.Enterprise.Data.DataSource.Common.SqlExecutor.Core.CompilerCore.JoinCore[TLeftTable,TLeftTableRow,TLeftTableColumn,TRightTable,TRightTableRow,TRightTableColumn,TResultTable,TResultTableRow,TResultTableColumn](TLeftTable leftTableContext, TRightTable rightTableContext, JoinType joinType, IExpression joinConstraint, Func`3 columnCombinator, Func`3 rowCombinator, Func`4 resultTableConstructor, GlobalExecuteContext globalExecuteContext)
at GrapeCity.Enterprise.Data.DataSource.Common.SqlExecutor.Core.CompositeTable.Join(TableContext secondTable, JoinType joinType, IExpression joinConstraint, GlobalExecuteContext globalExecuteContext)
at GrapeCity.Enterprise.Data.DataSource.Common.SqlExecutor.MemorySqlExecutor.ExecuteStatementBody(SelectStatement statement, String alias, GlobalExecuteContext globalExecuteContext, Boolean ignoreOrderBy)
at GrapeCity.Enterprise.Data.DataSource.Common.SqlExecutor.MemorySqlExecutor.ExecuteStatement(SelectStatement statement, String alias, GlobalExecuteContext globalExecuteContext)
at GrapeCity.Enterprise.Data.DataSource.Common.SqlExecutor.MemorySqlExecutor.ExecuteImpl(SelectStatement selectStatement, GlobalExecuteContext globalExecuteContext)
at GrapeCity.Enterprise.Data.DatasetManager.GcefModelQuery.ExecuteAsync(Dictionary`2 connectionStringModel, DataSetDocument datasetDoc, Dictionary`2 runtimeParamValues, Dictionary`2 procedureParametersDic, Boolean isValidationMode, Int32 rowLimit)
at GrapeCity.Enterprise.Data.DatasetManager.DatasetManager.GCEFModelQuery(GcefQueryParameter queryArgument, Dictionary`2 nonFixedParams, Boolean isValidationMode, Boolean enableRowLimit, Int32 rowLimit)
at GrapeCity.Enterprise.Data.DatasetManager.DatasetManager.GetTableContextAsync(GcefQueryParameter queryArgument, Boolean enableRowLimit, Int32 rowLimit, Boolean isValidationMode, String customSQL)
at GrapeCity.Enterprise.Data.DatasetManager.DatasetManager.GetTableContextWithoutCalcAndCatFieldsAsync(GcefQueryParameter queryArgument, Boolean enableRowLimit, Int32 rowLimit, Boolean isValidationMode)
at GrapeCity.Enterprise.Data.DatasetManager.DatasetManager.QueryDataImplAsync(GcefQueryParameter queryArgument, Int32 rowLimit, Boolean indexMode, Boolean isValidationMode, String customSQL)
at GrapeCity.Enterprise.Data.DatasetManager.DatasetManager.QueryDataAsync(GcefQueryParameter parameter, Nullable`1 rowLimit, Boolean isValidationMode, String customSQL)
at GrapeCity.Enterprise.Data.DatasetManager.DatasetManager.QueryDataWithViewAsync(GcefQueryParameter parameter, IViewInfo view, Nullable`1 rowLimit, String customSQL)
at GrapeCity.Enterprise.Server.Dataset.DatasetController.GetNormalDatasetStreamDataResultAsync(DataSetDocument datasetDoc, Dictionary`2 datasourceDocDict, Dictionary`2 runtimeParameters, List`1 requiredParameters, IDatasetManager datasetManager, Int32 previewRows, ILogger`1 logger, IDatasetViewCalculator datasetViewCalculator)
at GrapeCity.Enterprise.Server.Dataset.DatasetController.QueryDatasetData(DataSet model, IExecuteSession executeSession, IDatasetManager datasetManager, IStreamingDatasetService streamingDatasetService, IDatabaseOperationService databaseOperationService, IPushDatasetDatabaseAdapter pushDatasetDatabaseAdapter, INativeQueryDatasetService nativeQueryDatasetService, IDatasetViewCalculator datasetViewCalculator, ILogger`1 logger, ILoggerFactory loggerFactory).
2024-09-24 07:47:51.145+00:00 [INF] 0HN6SD573TH2R:00000008 Serilog.AspNetCore.RequestLoggingMiddleware Request finished HTTP/1.1 POST /api/v2.0/reporting/reports/077a9cea-13e8-4039-91b1-2e179f68986c/render-parameters-values - 400 application/json; charset=utf-8 364.1526 ms
2024-09-24 07:47:58.382+00:00 [INF] Grapecity.Enterprise.Scheduler.Schedule.Actors.ProxyDispatchActor task<51702419-cd32-449f-bc07-30e448b70c1e> get a available worker: [akka.tcp://ScheduleCluster@127.0.0.1:51990/user/schedule-proxy#847092613]