'Unable to commit against JDBC Connection

I am using springboot to connect to a mysql database. Please find my configuration below

spring.datasource.url=jdbc:<connection-url>
spring.datasource.username=<username>
spring.datasource.password=<password>
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.show-sql=true

spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.format_sql=true
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-idle=20
spring.datasource.tomcat.max-wait=20000
spring.datasource.tomcat.min-idle=15

Api code

@CrossOrigin(origins = "*", allowedHeaders = "*")
@GetMapping(value = "/validateuser/{consumerName}")
@Transactional
public Boolean valiadateuser(@PathVariable String consumerName) {
    LOGGER.info("Inside validateuser -1");
    ConsumerName user = consumerRepository.findByName(consumerName);
    LOGGER.info("Inside validateuser -2 :::: " + user);
    if (user != null) {
        return Boolean.TRUE;
    }
    return Boolean.FALSE;
}

Below is my exception

 org.springframework.orm.jpa.JpaSystemException: Unable to commit against JDBC Connection; nested exception is org.hibernate.TransactionException: Unable to commit against JDBC Connection
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:353) ~[spring-orm-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255) ~[spring-orm-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:538) ~[spring-orm-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743) [spring-tx-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711) [spring-tx-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:665) [spring-tx-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:370) [spring-tx-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) [spring-tx-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) [spring-aop-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749) [spring-aop-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691) [spring-aop-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at com.server.controller.SubscribeController$$EnhancerBySpringCGLIB$$14f090fd.subscribeTopic(<generated>) [classes!/:0.0.1-SNAPSHOT]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_251]
mysqlspring-bootspring-jdbc


Solution 1:[1]

Got the answer

updated the resource.properties

spring.datasource.url=jdbc:<connection-url>
spring.datasource.username=<username>
spring.datasource.password=<password>
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.show-sql=true

spring.jpa.hibernate.ddl-auto=update
spring.datasource.hikari.connectionTimeout=20000
spring.datasource.hikari.maximumPoolSize=5

Solution 2:[2]

I was experiencing the same exception on my Spring / Postgres stack. Basically, the DB can not return/commit all the rows that match your query in time.

It can be fixed by creating indexes on the columns used in the particular query. This speeds the query up.

CREATE INDEX index_redflag_person
ON redflag_person (firstname, alias,lastname,address,birthplace);

Solution 3:[3]

I use Spring and Hibernate and PostgreSQL for practice, got the similar exception:

org.springframework.transaction.TransactionSystemException: Could not commit Hibernate transaction; 
nested exception is org.hibernate.TransactionException: 
Unable to commit against JDBC Connection] with root cause
  org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.

my hibernate.prop has some config below

hibernate.dialect=org.hibernate.dialect.PostgreSQL10Dialect
hibernate.connection.handling_mode=DELAYED_ACQUISITION_AND_RELEASE_AFTER_STATEMENT

packages.to.scan=org.practice.dao.entity

It's weird bcs the default hibernate.connection.autocommit value in hibernate 5.6 is false

After a whole day search, I could not find the same error on the internet, finally I figured it out: somehow I added hibernate.connection.handling_mode in my config file, remove it(the default is fine) and the app works as expected

so maybe check the config and use the simplest param would help someone else

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Saurabh Jhunjhunwala
Solution 2 Dharman
Solution 3 Jon Wang

Related Questions