Having issue with setting up Hapi fhir as a container app in Azure with Postgres database

[Manoj Srivastava]

I am able to stand up the service.

It connects with the database and creates the tables.

When I search for something it gives the following error:

{ "resourceType": "OperationOutcome",

"issue": [ { "severity": "error",

"code": "processing",

"diagnostics": "HAPI-2223: could not extract ResultSet [ERROR: syntax error at or near \"seq_search\"\n Position: 23] [select next value for seq_search]; SQL [select next value for seq_search]" } ] }

The server console shows the following messages:

ca.uhn.fhir.rest.server.exceptions.InternalErrorException: HTTP 500 : HAPI-2223: could not extract ResultSet [ERROR: syntax error at or near "seq_search"
  Position: 23] [select next value for seq_search]; SQL [select next value for seq_search]
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
        at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
        at ca.uhn.fhir.rest.server.exceptions.BaseServerResponseException.newInstance(BaseServerResponseException.java:317)
        at ca.uhn.fhir.rest.client.impl.BaseClient.invokeClient(BaseClient.java:415)
        at ca.uhn.fhir.rest.client.impl.GenericClient$BaseClientExecutable.invoke(GenericClient.java:585)
        at ca.uhn.fhir.rest.client.impl.GenericClient$SearchInternal.execute(GenericClient.java:2201)
        at ca.uhn.fhir.to.Controller.actionSearch(Controller.java:609)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:569)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:255)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:188)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:926)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:831)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903)
        at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)
        at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
        at org.springframework.web.filter.ServerHttpObservationFilter.doFilterInternal(ServerHttpObservationFilter.java:109)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
        at org.apache.catalina.valves.RemoteIpValve.invoke(RemoteIpValve.java:731)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:389)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:896)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1741)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
        at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
        at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
        at java.base/java.lang.Thread.run(Thread.java:840)
2024-10-19T09:49:56.212Z  WARN 1 --- [nio-8080-exec-5] o.s.web.servlet.PageNotFound             : No mapping for GET /content/custom/logo.jpg
2024-10-19T09:49:56.212Z  WARN 1 --- [nio-8080-exec-5] o.s.web.servlet.PageNotFound             : No endpoint GET /content/custom/logo.jpg.

[James Agnew]

This looks to me like your database isn't initialized. You can use the CLI Migrator Tool to do this, or even apply the schema manually using this schema: https://smilecdr.com/docs/database_administration/setting_up_postgresql.html#setting-up-a-fhir-storage-relational-database

(Although using the migrator is recommended, you'll need to use it to upgrade versions eventually anyhow.)

Cheers,

James

--
You received this message because you are subscribed to the Google Groups "HAPI FHIR" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hapi-fhir+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hapi-fhir/ab04dab3-1879-423c-a4c6-1546e8e9eac9n%40googlegroups.com.

[Manoj Srivastava (मनोज श्रीवास्तव)]

Hi James,

Thanks for replying to my query.

It turns out that database is not the issue. I can connect to the same database from a docker instance of Hapi FHIR running locally on my computer and it works just fine.

The issue according to ChatGPT is that the Azure hosted container app is generating sql statements which are not correct for Postgres. It somehow thinks that it is talking to h2 or MS SQL Server.

ChatGPT: "The SQL select next value for seq_search is valid for databases like H2 or SQL Server, but PostgreSQL uses a different syntax: SELECT nextval('seq_search'). "

It then had me define a few additional env variables but still I am having the same problem.

SPRING_JPA_PROPERTIES_HIBERNATE_USE_NEW_ID_GENERATOR_MAPPINGS=true

HAPI_DB_SEQUENCE_FORMAT=SELECT nextval('%s')

SPRING_JPA_PROPERTIES_HIBERNATE_DIALECT=ca.uhn.fhir.jpa.model.dialect.HapiFhirPostgresDialect

Best regards,

Manoj Srivastava

https://www.linkedin.com/in/manojsrivastava/

[Manoj Srivastava]

I finally got hapi fhir server running in Azure.

Not as a container app but on a VM.

It turns out that the environment variables passed via docker environment tag (commented lines below) are not propagated into the app properly.

If I pass the same values via an external yaml file via the configs tag, it is read in correctly and the server works as expected.

version: '3.8'

services:

  fhir:

    container_name: fhir

    image: "hapiproject/hapi:latest"

    ports:

      - "8080:8080"

    # environment:

    #   - SPRING_DATASOURCE_URL=jdbc:postgresql://my-postgres-server:5432/hapi

    #   - SPRING_DATASOURCE_USERNAME=hapi_admin

    #   - SPRING_DATASOURCE_PASSWORD=hapi_password

    #   - SPRING_DATASOURCE_DRIVERCLASSNAME=org.postgresql.Driver

    #   - SPRING_JPA_PROPERTIES_HIBERNATE_DIALECT=ca.uhn.fhir.jpa.model.dialect.HapiFhirPostgresDialect

    #   - SPRING_JPA_PROPERTIES_HIBERNATE_SEARCH_ENABLED=false

    configs:

      - source: hapi

        target: /app/config/application.yaml

configs:

  hapi:

     file: ./hapi.application.yaml

hapi.application.yaml:

spring:

  datasource:

    url: 'jdbc:postgresql://my-postgres:5432/hapi'

    username: hapi_admin

    password: hapi_password

    driverClassName: org.postgresql.Driver

  jpa:

    properties:

      hibernate.dialect: ca.uhn.fhir.jpa.model.dialect.HapiFhirPostgresDialect

      hibernate.search.enabled: false

I am sure the Azure container app will also work the same way once I figure out how to share the external yaml file with the container app.