0

I am using Spring BOOT application which is storing information in DB. I am getting error :

org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction

I tried searching on INTERNET but could not find any answer. When I restart my jar it works fine - error does not come. This happens on daily basis, i have to restart jar daily.

Exception : org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 44,382,666 milliseconds ago. The last packet sent successfully to the server was 44,382,666 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.

It is scheduled to run daily for every ten minutes.

I have tried to reduce time period - scheduled to run on specific hours, but still no success.

Also tried autoReconnect=true, provided port number in db url.

James Z
  • 12,209
  • 10
  • 24
  • 44
Aman Saxena
  • 11
  • 1
  • 5

1 Answers1

0

Seems this is the answer

spring.datasource.testOnBorrow=true
spring.datasource.testWhileIdle = true
spring.datasource.timeBetweenEvictionRunsMillis = 3600000
spring.datasource.validationQuery=SELECT 1
igorepst
  • 1,230
  • 1
  • 12
  • 20
  • I am using two datasource one for reading and second for writing. I am using below properties. – Aman Saxena Apr 04 '19 at 15:38
  • for reading: spring.datasource.url=jdbc:mysql://ReadingDBIP/DBNAME?autoReconnect=true, spring.datasource.driver-class-name=com.mysql.jdbc.Driver, spring.datasource.tomcat.test-while-idle=true, – Aman Saxena Apr 04 '19 at 15:40
  • spring.datasource.tomcat.test-on-borrow=true, spring.datasource.tomcat.validation-query=SELECT 1, spring.datasource.tomcat.maxActive=100, spring.datasource.tomcat.maxIdle=10, spring.datasource.tomcat.minIdle=1, spring.datasource.tomcat.maxWait=60000, spring.datasource.tomcat.initial-size=10 – Aman Saxena Apr 04 '19 at 15:43
  • for writing : spring.secondDatasource.url=jdbc:mysql://WrtingDBIP/DBNAME?autoReconnect=true, spring.secondDatasource.driver-class-name=com.mysql.jdbc.Driver, spring.secondDatasource.tomcat.test-while-idle=true, spring.secondDatasource.tomcat.test-on-borrow=true, spring.secondDatasource.tomcat.validation-query=SELECT 1, spring.secondDatasource.tomcat.maxActive=100, spring.secondDatasource.tomcat.maxIdle=10, spring.secondDatasource.tomcat.minIdle=1, spring.secondDatasource.tomcat.maxWait=60000, spring.secondDatasource.tomcat.initial-size=10 – Aman Saxena Apr 04 '19 at 15:44
  • should I user apache dbcp2 ? – Aman Saxena Apr 04 '19 at 15:47