We are using multiple spring datasource to connect multiple database. We are getting the following errors only Occasionally, and then after a while everything stars working again automatically
o.h.e.j.s.SqlExceptionHelper.logExceptions:129 - SQL Error: 0, SQLState: null
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed:
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 84,250,518 milliseconds ago. The last packet sent successfully to the server was 84,250,518 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.
Found the problem, due to some inactivity datasource becomes idle and looses connection from pool.
The solution was to add validation query and set testOnBorrow true on spring application properties file
validationQuery="select 1"
testOnBorrow=true
autoRecconect = true doesn't help in this case