CODEWITHSUNDEEP
mysqlspring-bootspring-jdbc
Saurabh Jhunjhunwala
Saurabh Jhunjhunwala

Reputation: 2922

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]

Upvotes: 4

Views: 26726

Answers (3)

Jon Wang
Jon Wang

Reputation: 319

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

Upvotes: -1

Daniel Xav De Oliveira
Daniel Xav De Oliveira

Reputation: 428

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

Upvotes: 1

Saurabh Jhunjhunwala
Saurabh Jhunjhunwala

Reputation: 2922

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

Upvotes: 1

Related Questions