kogito-data-index-postgresql : Exception while fetching data (/UserTaskInstances)

[Mark Jayson Gonzaga]

Hi Team,

Would like to ask if we can use the postgresql version of the kogito-data-index

I'm getting this error

this is my docker-compose

version: '2.1'

services:

  server-db:

    image: postgres:9.6

    environment:

      POSTGRES_USER: kogito

      POSTGRES_PASSWORD: secret

      POSTGRES_DB: soh

    ports:

      - 5432:5432

  zookeeper:

    container_name: zookeeper

    image: strimzi/kafka:0.20.1-kafka-2.6.0

    command: [

      "sh", "-c",

      "bin/zookeeper-server-start.sh config/zookeeper.properties"

    ]

    ports:

      - "2181:2181"

    environment:

      LOG_DIR: "/tmp/logs"

  kafka:

    image: strimzi/kafka:0.20.1-kafka-2.6.0

    container_name: kafka

    command: [

      "sh", "-c",

      "bin/kafka-server-start.sh config/server.properties --override inter.broker.listener.name=$${KAFKA_INTER_BROKER_LISTENER_NAME} --override listener.security.protocol.map=$${KAFKA_LISTENER_SECURITY_PROTOCOL_MAP} --override listeners=$${KAFKA_LISTENERS} --override advertised.listeners=$${KAFKA_ADVERTISED_LISTENERS} --override zookeeper.connect=$${KAFKA_ZOOKEEPER_CONNECT}"

    ]

    depends_on:

      - zookeeper

    ports:

      - "9092:9092"

    environment:

      KAFKA_BROKER_ID: 0

      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181

      KAFKA_LISTENERS: INTERNAL://kafka:29092,EXTERNAL://kafka:9092

      KAFKA_ADVERTISED_LISTENERS: INTERNAL://kafka:29092,EXTERNAL://localhost:9092

      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: INTERNAL:PLAINTEXT,EXTERNAL:PLAINTEXT

      KAFKA_INTER_BROKER_LISTENER_NAME: INTERNAL

      KAFKA_AUTO_CREATE_TOPICS_ENABLE: "true"

      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1

      LOG_DIR: "/tmp/logs"

  data-index:

    container_name: data-index

    image: quay.io/kiegroup/kogito-data-index-postgresql:latest

    ports:

      - "8180:8080"

    depends_on:

      kafka:

        condition: service_started

    environment:

      KAFKA_BOOTSTRAP_SERVERS: kafka:29092

      QUARKUS_DATASOURCE_USERNAME: kogito

      QUARKUS_DATASOURCE_PASSWORD: secret

      QUARKUS_DATASOURCE_JDBC_URL: jdbc:postgresql://server-db:5432/soh

We are going to use this in our project but this is giving us an error.

Thanks for the help.

[Mark Jayson Gonzaga]

This is the error log

 2021-08-14 08:34:40,920 WARN  [not.gra.exe.SimpleDataFetcherExceptionHandler] (executor-thread-0) Exception while fetching data (/UserTaskInstances) : org.hibernate.exception.SQLGrammarException: could not extract ResultSet: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
data-index    |         at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
data-index    |         at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1626)
data-index    |         at org.hibernate.query.Query.getResultList(Query.java:165)
data-index    |         at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:76)
data-index    |         at org.kie.kogito.index.postgresql.storage.PostgreSqlQuery.execute(PostgreSqlQuery.java:106)
data-index    |         at org.kie.kogito.index.graphql.GraphQLSchemaManager.executeAdvancedQueryForCache(GraphQLSchemaManager.java:224)
data-index    |         at org.kie.kogito.index.graphql.GraphQLSchemaManager.getUserTaskInstancesValues(GraphQLSchemaManager.java:228)
data-index    |         at graphql.execution.ExecutionStrategy.fetchField(ExecutionStrategy.java:270)
data-index    |         at graphql.execution.ExecutionStrategy.resolveFieldWithInfo(ExecutionStrategy.java:203)
data-index    |         at graphql.execution.AsyncExecutionStrategy.execute(AsyncExecutionStrategy.java:60)
data-index    |         at graphql.execution.Execution.executeOperation(Execution.java:165)
data-index    |         at graphql.execution.Execution.execute(Execution.java:104)
data-index    |         at graphql.GraphQL.execute(GraphQL.java:557)
data-index    |         at graphql.GraphQL.parseValidateAndExecute(GraphQL.java:482)
data-index    |         at graphql.GraphQL.executeAsync(GraphQL.java:446)
data-index    |         at io.vertx.ext.web.handler.graphql.impl.GraphQLHandlerImpl.execute(GraphQLHandlerImpl.java:372)
data-index    |         at io.vertx.ext.web.handler.graphql.impl.GraphQLHandlerImpl.executeOne(GraphQLHandlerImpl.java:330)
data-index    |         at io.vertx.ext.web.handler.graphql.impl.GraphQLHandlerImpl.handlePostQuery(GraphQLHandlerImpl.java:224)
data-index    |         at io.vertx.ext.web.handler.graphql.impl.GraphQLHandlerImpl.handlePostJson(GraphQLHandlerImpl.java:178)
data-index    |         at io.vertx.ext.web.handler.graphql.impl.GraphQLHandlerImpl.handlePost(GraphQLHandlerImpl.java:149)
data-index    |         at io.vertx.ext.web.handler.graphql.impl.GraphQLHandlerImpl.handle(GraphQLHandlerImpl.java:104)
data-index    |         at io.vertx.ext.web.handler.graphql.impl.GraphQLHandlerImpl.handle(GraphQLHandlerImpl.java:52)
data-index    |         at org.kie.kogito.index.vertx.BlockingGraphqlRouterProducer.graphQLHandler(BlockingGraphqlRouterProducer.java:55)
data-index    |         at org.kie.kogito.index.vertx.BlockingGraphqlRouterProducer_RouteHandler_graphQLHandler_58e64ac2df61758efa0ee3f8bb1d4ca2a5e87953.invoke(BlockingGraphqlRouterProducer_RouteHandler_graphQLHandler_58e64ac2df61758efa0ee3f8bb1d4ca2a5e87953.zig:107)
data-index    |         at io.quarkus.vertx.web.runtime.RouteHandler.handle(RouteHandler.java:97)
data-index    |         at io.quarkus.vertx.web.runtime.RouteHandler.handle(RouteHandler.java:22)
data-index    |         at io.vertx.ext.web.impl.BlockingHandlerDecorator.lambda$handle$0(BlockingHandlerDecorator.java:48)
data-index    |         at io.vertx.core.impl.ContextImpl.lambda$null$0(ContextImpl.java:160)
data-index    |         at io.vertx.core.impl.AbstractContext.dispatch(AbstractContext.java:96)
data-index    |         at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$1(ContextImpl.java:158)
data-index    |         at io.vertx.core.impl.TaskQueue.run(TaskQueue.java:76)
data-index    |         at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:481)
data-index    |         at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2442)
data-index    |         at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1476)
data-index    |         at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
data-index    |         at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
data-index    |         at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
data-index    |         at java.base/java.lang.Thread.run(Thread.java:829)
data-index    | Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
data-index    |         at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
data-index    |         at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
data-index    |         at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
data-index    |         at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
data-index    |         at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
data-index    |         at org.hibernate.loader.Loader.getResultSet(Loader.java:2309)
data-index    |         at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2062)
data-index    |         at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2024)
data-index    |         at org.hibernate.loader.Loader.doQuery(Loader.java:955)
data-index    |         at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:356)
data-index    |         at org.hibernate.loader.Loader.doList(Loader.java:2855)
data-index    |         at org.hibernate.loader.Loader.doList(Loader.java:2837)
data-index    |         at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2669)
data-index    |         at org.hibernate.loader.Loader.list(Loader.java:2664)
data-index    |         at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:540)
data-index    |         at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
data-index    |         at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
data-index    |         at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1443)
data-index    |         at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1649)
data-index    |         at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
data-index    |         ... 36 more
data-index    | Caused by: org.postgresql.util.PSQLException: ERROR: relation "usertaskinstanceentity" does not exist
data-index    |   Position: 685
data-index    |         at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
data-index    |         at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
data-index    |         at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
data-index    |         at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
data-index    |         at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
data-index    |         at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
data-index    |         at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
data-index    |         at io.agroal.pool.wrapper.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:78)
data-index    |         at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
data-index    |         ... 51 more
data-index    |

[Cristiano Nicolai]

Hi Mark,

It seems like the tables were not created on PostgreSQL, if you want DAta Index to create it on start you can use the following property:

- quarkus.hibernate-orm.database.generation=create

Otherwise, there is a DDL script you could use here: https://github.com/kiegroup/kogito-apps/blob/main/data-index/data-index-storage/data-index-storage-postgresql/src/main/resources/create.sql

We're working to provide a DDL zip file containing all necessary DDL for Kogito services.

--
You received this message because you are subscribed to the Google Groups "Kogito development mailing list" group.
To unsubscribe from this group and stop receiving emails from it, send an email to kogito-developm...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/kogito-development/CADNbDsD2jLAGRXz5sHBRGgAojgJv33ZucySPV67Ru7dgve3Akg%40mail.gmail.com.

--

Best regards,

Cristiano Nicolai