5

When using the PostgreSQL JDBC driver in my project i get a weird error stating "Unexpected error trying to gauge level of JDBC REF_CURSOR support : null"

It causes my application to hang when starting up for anywhere between eighty to three hundred seconds. After startup everything works fine. I am using the following driver version:

(Gradle Dependency style) 'org.postgresql:postgresql:9.3-1102-jdbc41'

My application is a Spring Boot application and the hanging happens upon starting that application.

I saw the error after turning on Hibernate logging. The full log is at the following paste.

http://pastebin.com/CAjSyQw9

After it hits the last line of the log in that paste then the application hangs for eighty to three hundred seconds, usually for about 100 seconds on average. The application still starts up fine and works as expected.

Does this sort of an error seem like an issue with the JDBC driver?

There is a similar SO question on this topic here.

Edit 1:

It looks like something really weird is happening on lines 69 and 70 of my paste bin paste. It looks like my specified dialect of org.postgresql.Dialect is being changed to some MySQL thing.

[org.hibernate.dialect.Dialect] : [MySQL5] -> [org.hibernate.dialect.MySQL5Dialect] (replacing [org.hibernate.dialect.MySQL5Dialect])
2014-10-16 08:05:50.561 DEBUG 36916 --- [ost-startStop-1] o.h.b.r.s.internal.StrategySelectorImpl  : Registering named strategy selector [org.hibernate.dialect.Dialect] : [MySQL5InnoDB] -> [org.hibernate.dialect.MySQL5InnoDBDialect] (replacing [org.hibernate.dialect.MySQL5InnoDBDialect])
Community
  • 1
  • 1
Kent Bull
  • 1,144
  • 3
  • 21
  • 41
  • Why are you using **MySQL5Dialect** for Postgres? –  Oct 17 '14 at 17:57
  • @a_horse_with_no_name I am not intentionally using a MySQL dialect with PostgreSQL. In my application.properties file I set it to be `org.hibernate.dialect.PostgreSQLDialect` so it makes no sense that it would all of the sudden change to a MySQL dialect. I am wondering if there is something unexpected happening between Spring Data, Spring Boot, or Spring JPA. – Kent Bull Oct 17 '14 at 19:36
  • @KentJohnson do you have found a solution on that? I am experiencing the same, strange change to the `MySQL5Dialect` instead of the (specified) `PostgresSQLDialect`. – fdomig Feb 05 '15 at 10:18
  • The only solution I found was that when I used my company's mobile VPN client then I was getting the error. When I put my java app on one of the Unix servers behind our VPN then everything worked fine for some odd reason. So this isn't really a solution, just something that worked for me. – Kent Bull Feb 07 '15 at 01:42

3 Answers3

8

I had the same issue. I fixed it ...er... worked around it, by adding the System property defined in the answer here.

It is something to do with how Hibernate and Postgresql looks up the database metadata. If there are a lot of datatypes in the database, the process can take a long time as each data type requires a SQL roundtrip for the information. The System property defined below circumvents the whole metadata retrieval and uses the Hibernate defaults for Postgresql.

-Dhibernate.temp.use_jdbc_metadata_defaults=false

Hope this helps someone else.

Community
  • 1
  • 1
gjrwebber
  • 2,658
  • 2
  • 22
  • 26
2

I ran into a similar issue.

To fix it with Spring boot use:

spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false

This worked for me.

Patrick Trentin
  • 7,126
  • 3
  • 23
  • 40
Dhruv
  • 165
  • 1
  • 7
0

I'm not a Java man, but a bit of googling suggests to me that the REF_CURSOR stuff was put into JDBC in 4.2 and you appear to be running 4.0

So - it can't find the "supportsRefCursors" method and returns null, logging the exception.

Do you need to match your Hibernate version with your JDBC version or some such?

Whether that explains the delay, I don't know. Try logging SQL - then you can see what's really going on.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • I am already logging Hibernate at DEBUG. When you say log SQL what more do I need to do? – Kent Bull Oct 16 '14 at 23:55
  • If you can't log it at the JDBC level, you can turn statement logging on in PostgreSQL itself. – Richard Huxton Oct 17 '14 at 07:46
  • I think I found the issue. If you look at lines 69 and 70 of my Pastebin paste you will see it is trying to do some weird replacement with MySQL. – Kent Bull Oct 17 '14 at 16:18
  • Perhaps write up the exact details as an answer and accept it. – Richard Huxton Oct 18 '14 at 08:05
  • I haven't solved the issue yet. I am leaning towards thinking it is a version incompatibility between my database driver and the version of PostgreSQL installed on the Server. When I connect to a database on my local machine that has a 9.3 version of the PosgreSQL database I have no issue. When I connect to a 9.2 database I have an issue. After a little more testing I'll probably write up an answer in a few days. – Kent Bull Oct 19 '14 at 21:45
  • I am going to accept this answer since logging SQL did give me the lead I needed to ask the PostgreSQL team what was going on. I don't know that there is a clear answer to this question though I think it doesn't really matter since I solved the problem one of two ways, by putting my application binary on a Unix server or by pointing the application to a database on my local machine . For some reason this solved my issue. If I had a more clear answer I would post it and accept it though since I don't yours is accepted. – Kent Bull Feb 19 '15 at 16:37