Unable to see existing table

[Edward Pimentel]

Hi,

I am trying to connect hapi fhir starter to mysql. It is working properly when i am connecting to my local mysql using root user. However when i am trying to connect hapi fhir starter on the mysql on a test server it was able to automatically generate the tables it needs but afterwards it says that a table does not exist. I checked the database and confirmed that the table does exist. Note that on my local mysql i am using root user and on the test server i am using a custom created user that is granted all permissions and rights for the table created by the root user of the mysql on the test server. Is this a problem with my local copy of hapi fhir starter? Or is this a problem with the mysql in thw test server?

Thanks in advance for the help!

[Edward Pimentel]

I am getting this error when I am trying to run hapi fhir.

But after checking on my remote database server i can confirm that the table exists. I am even able to insert a sample data into it.

[James Agnew]

The fact that it's failing with an SQLGrammarException could mean you have the wrong Hibernate dialect selected. I'd recommend checking your config.

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/078cce96-b4b5-4ecf-8aad-6183f2a27648n%40googlegroups.com.

[Torbjørn Torsvik]

Hi. 

Im having the exact same problem. The tables are being generated, and can be accessed and manipulated. Still hibernate claims they dont exist.  

I run mysql 8.0.23 on ubuntu 20.04. In my application.yaml, i have set => "hibernate.dialect: org.hibernate.dialect.MySQL8Dialect" under spring jpa properties. The pom has the appropriate dependency for mysql-connector-java 8.0.23. 

When running jetty, i get a line stating that mysql8dialect is in fact used:

"2021-04-15 13:58:22.463 [main] INFO  org.hibernate.dialect.Dialect [Dialect.java:175] HHH000400: Using dialect: org.hibernate.dialect.MySQL8Dialect"

Still no luck. 

When trying to run jetty, i get lots of warnings about classes being in multiple locations. javaee-api-8.0.1.jar seems to always be one of the duplicates. 

example: [WARNING] javax.annotation.Generated scanned from multiple locations: jar:file:///home/myusername/.m2/repository/javax/annotation/javax.annotation-api/1.3.2/javax.annotation-api-1.3.2.jar!/javax/annotation/Generated.class, jar:file:///home/myusername/.m2/repository/javax/javaee-api/8.0.1/javaee-api-8.0.1.jar!/javax/annotation/Generated.class

Could this be related? 

Thanks in advance for any help!

[Torbjørn Torsvik]

The problem seems to stem from a difference in capitalization in the database. Hibernate stores all the tables in lowercase, but asks for them in uppercase. The server runs fine when i change all the tablenames to uppercase. 

I have troubles modifying the hibernate namingstrategy so that it stores things in uppercase. From what i can tell, turning off hibernate validation should also work, but i havnet been able to do that either.

I tried :

spring

    jpa

        properties

            javax.persistence.validation.mode: none

            hibernate.naming.physical-strategy: ca.uhn.fhir.jpa.starter.CustomPhysicalNamingStrategy

[James Agnew]

Any chance you could submit your workaround as a PR against the README?

To view this discussion on the web visit https://groups.google.com/d/msgid/hapi-fhir/fd04ee68-4f65-449e-b0b3-82880f2733f7n%40googlegroups.com.

[Torbjørn Torsvik]

Hi. 

I have a working solution now. My previous hibernate property name was wrong. 

In the spring boots documentation they tell you to set spring.jpa.hibernate.naming.physical-strategy. This didnt work for me. From what i can tell, you have to set it under properties as  spring.jpa.properties.hibernate.physical_naming_strategy. If not, its overwritten by ca.uhn.fhir.jpa.starter.EnvironmentHelper. 

The capitalization issue was resolved for me by using org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl like this:

spring:

    jpa:

        properties:

           hibernate.physical_naming_strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

I think this is only relevant for users trying to run the server against mysql on ubuntu (as ubuntu mysql is case sensitive, unlike on windows)

Is PR short for pull request? If so, I would be happy to contribute.