4

Since I added Hibernate Search on my application, seems that I'm losing the connection from Hikari's pool after some time (it seems to be over than 8 hours)

I've been struggling with this error a full week and I don't really know why it's happening:

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement

Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement

Caused by: java.sql.SQLException: Connection is closed

I tried: To set some hikari's properties like:

 spring.datasource.type=com.zaxxer.hikari.HikariDataSource
 spring.datasource.hikari.minimumIdle=20
 spring.datasource.hikari.maximumPoolSize=100
 spring.datasource.hikari.idleTimeout=300000
 spring.datasource.hikari.poolName=SpringBootJPAHikariCP
 spring.datasource.hikari.maxLifetime=200000
 spring.datasource.hikari.connectionTimeout=20000
 spring.datasource.hikari.registerMbeans=true

to config my bean to use a testQuery like bellow:

    public DataSource siteDataSource() {

    HikariConfig config = new HikariConfig();
    HikariDataSource dataSource;

    config.setJdbcUrl(env.getProperty("spring.mysql.datasource.url"));
    config.setUsername(env.getProperty("spring.mysql.datasource.username"));
    config.setPassword(env.getProperty("spring.mysql.datasource.password"));
    config.setDriverClassName(env.getProperty("spring.mysql.datasource.driver-class-name"));
    config.setMaximumPoolSize(15);
    config.setConnectionTestQuery("SELECT 1");

    // performance senstive settings
    config.setMinimumIdle(0);
    config.setConnectionTimeout(30000);
    config.setIdleTimeout(35000);
    config.setMaxLifetime(45000);
    dataSource = new HikariDataSource(config);

    return dataSource;
}

(this config's stuff was removed too and the error still the same)

and to use:

autoReconnect=true

I was also getting the error below, which seems that disappeared with the current implementation (but to be honest, I have no idea why - yes, I'm quite lost here):

Caused by: com.mysql.cj.exceptions.CJCommunicationsException: The last packet successfully received from the server was 50,090,585 milliseconds ago.  The last packet sent successfully to the server was 50,090,585 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

I don't even know how to reproduce this error. I have to wait like a full day to test it again for each new test.

Seems to be something with Tomcat + Hibernate + Hikari, but to be honest, I don't have any further idea.

Do you guys have some idea how to solve this, or even how to reproduce this error with a short period of time?

Thanks.

Raphael M.
  • 140
  • 6
  • https://github.com/brettwooldridge/HikariCP/issues/1288 please look here – Yan Dec 18 '19 at 12:28
  • I already read it. I've tried everything they suggested. The only thing i didn't try (because seems gooddamn terrible) it is to have a cron job making a fake request each hour to keep the connection alive. – Raphael M. Dec 18 '19 at 12:33
  • I can't find a solution for this... – Raphael M. Dec 27 '19 at 11:54
  • I assume you have some code which doesn't return db connection to pool. e.g. you use connection object somewhere. "The last packet successfully received from the server was 50,090,585 milliseconds" can be connected to the fact that you lock thread which sends heartbeat requests to db. I would recommend doing thread dump in order to check threads states. – Yan Dec 27 '19 at 19:38
  • I've managed it to work by making a random request every minute to keep the connection alive. I didn't find anything else better by now. – Raphael M. Jan 06 '20 at 19:44

2 Answers2

1

Steps that helped us to resolve the same issue:

  • Upgrade your SpringBoot version.
  • Upgrade your database connector library version.
  • Remove other database connection pooling libraries, as usually default Hikari from SpringBoot should be used.
Zon
  • 18,610
  • 7
  • 91
  • 99
  • do you have more detailed information about which version of Spring Boot, Hibernate Search and Hibernate ORM you have updated to which? – J3ernhard Jul 28 '21 at 12:47
  • Take latest Springboot (current is 2.5.0) and remove versions for your other dependencies. Springboot has it's own list of versions for compliant libraries. If not - try to find matching versions by library and springboot release dates. – Zon Jul 29 '21 at 08:26
0

this error solved with me by re-create the entityManager object

this.entityManager = entityManagerFactory.createEntityManager();

so all you need to do is: catch the exception and re-create the entityManager object then search again

optionally read(manual index recommendation): when I save(create/update) any object to database by Jpa

repository.save(obj)

found that the object doesn't index in hibernate-search(Lucien/Elastic Search/..), So I have to index the object manually after save it in database(but I'm not have to delete the indexed object manually when delete it from database).

repository.save(objFromDB);
manualIndexToHibernateSearch(objFromDB);
...
public void manualIndexToHibernateSearch(MyObject objFromDB) {
    FullTextEntityManager fullTextEntityManager = Search.getFullTextEntityManager(entityManager);
    fullTextEntityManager.getTransaction().begin();
    //find object by id in hibernate-search 
    MyObject objFromHibernateSearch = fullTextEntityManager.find(MyObject.class, objFromDB.getId());
    //you have to replace 'text' field with your fields that have @Field annotation
    objFromHibernateSearch.setText(objFromDB.getText());
    //index
    fullTextEntityManager.index(objFromHibernateSearch);
    //commit
    fullTextEntityManager.getTransaction().commit();
}
Ahmed Ramadan
  • 1
  • 2