Connection is closed when using Hibernate Search with Spring application

Question

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.

Answer 1

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();
}

Answer 2

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.