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] 3 Answers
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 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); 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
