PGPool not working with HikariCP in SpringBoot application

Question

I have enabled replication for Postgresql and I am using PGPool to make load balancing.

I am having issues to connect to Postgres using HikariCP and even with Apache DBCP.

Is there a way to use PGPool in SpringBoot application?

Please find the configuration file

spring:
  datasource:
    driverClassName: org.postgresql.Driver
    url: jdbc:postgresql://192.168.177.148:9999/test
    username: myUser
    password: XXXXXXX
  jpa:
    properties.hibernate.dialect: org.hibernate.dialect.PostgreSQLDialect
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate.ddl-auto: update
logging:
  level:
    org.springframework: DEBUG

Please find the stack trace:

2018-08-10 10:20:19.124 INFO 37879 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2018-08-10 10:20:20.617 ERROR 37879 --- [ main] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Exception during pool initialization.

org.postgresql.util.PSQLException: FATAL: unable to bind Detail: cannot get parse message "" at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.core.SetupQueryRunner.run(SetupQueryRunner.java:53) ~[postgresql-42.2.4.jar:42.2.4]

018-08-10 10:20:20.620 WARN 37879 --- [ main] o.s.b.a.orm.jpa.DatabaseLookup : Unable to determine jdbc url from datasource

More Trace

org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta-data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is org.postgresql.util.PSQLException: FATAL: unable to bind Detail: cannot get parse message "" at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:328) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:356) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.boot.autoconfigure.orm.jpa.DatabaseLookup.getDatabase(DatabaseLookup.java:72) ~[spring-boot-autoconfigure-2.0.4.RELEASE.jar:2.0.4.RELEASE]

More Trace

Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is org.postgresql.util.PSQLException: FATAL: unable to bind Detail: cannot get parse message "" at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:319) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE] ... 61 common frames omitted Caused by: org.postgresql.util.PSQLException: FATAL: unable to bind Detail: cannot get parse message "" at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.core.SetupQueryRunner.run(SetupQueryRunner.java:53) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.core.v3.ConnectionFactoryImpl.runInitialQueries(ConnectionFactoryImpl.java:674) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:223) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.jdbc.PgConnection.(PgConnection.java:195) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.Driver.makeConnection(Driver.java:454) ~[postgresql-42.2.4.jar:42.2.4] at org.postgresql.Driver.connect(Driver.java:256) ~[postgresql-42.2.4.jar:42.2.4] at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:117) ~[HikariCP-2.7.9.jar:na] at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:123) ~[HikariCP-2.7.9.jar:na] at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:365) ~[HikariCP-2.7.9.jar:na]

What error/stacktrace do you get? Also, share the configuration files/classes if possible — Raja Anbazhagan
Hi @RajaAnbazhagan, Thanks for reply. I have updated my question. Please have a look. — Munish Dhiman

Bhuwan Prasad Upadhyay Bhuwan Prasad Upadhyay · Accepted Answer · 2020-01-08T05:19:20

Our Scenario

We have also almost the same issue on the Kubernetes cluster. The database was closed and HikariCP not able to validate the connection properly. The exception is given by a spring boot application like below:

2020-01-06 09:38:13.013  WARN 1 --- [nio-8080-exec-8] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@490c2ee7 (This connection has been closed.)

2020-01-06 09:38:13.045  WARN 1 --- [nio-8080-exec-8] org.postgresql.jdbc.PgConnection         : Validating connection.

org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.

    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:333) ~[postgresql-42.2.2.jar!/:42.2.2]

    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.2.jar!/:42.2.2]
... common frames omitted

Caused by: java.io.EOFException: null

    at org.postgresql.core.PGStream.receiveChar(PGStream.java:295) ~[postgresql-42.2.2.jar!/:42.2.2]

    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1947) ~[postgresql-42.2.2.jar!/:42.2.2]

    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306) ~[postgresql-42.2.2.jar!/:42.2.2]

    ... 111 common frames omitted

Solution

To fix this issue we supply the following properties for Hikari in spring boot application.

spring.datasource.hikari.leakDetectionThreshold=5000  
spring.datasource.hikari.maxLifetime=900000  
logging.level.com.zaxxer.hikari=DEBUG

References

PGPool not working with HikariCP in SpringBoot application

1 Answers