1

We are migrating our system from MS SQL Server to MySQL and we are using spring boot for our application. Our application facing "Connection has been already closed" SQLException for some days and we use to restart our application and it started working fine but here is the catch after some hours it start giving same connection closed SQLException.

Here is the configs properties xfor connection:

database.jndiName=jdbc/SmartData
database.driverClassName=com.mysql.jdbc.Driver
database.url=XXXXXX
database.username=XXXXXXXXXXX
database.password=XXXXXX
database.factory=org.apache.tomcat.jdbc.pool.DataSourceFactory
database.initialSize=2
database.logAbandoned=false
database.maxIdle=4
database.maxWaitMillis=29998
database.minEvictableIdleTimeMillis=3000
database.minIdle=2
database.removeAbandonedTimeout=6
database.removeAbandoned=true
database.testOnBorrow=true
database.testOnReturn=false
database.testWhileIdle=false
database.timeBetweenEvictionRunsMillis=3000
database.validationQuery=SELECT 2+2
database.maxActive=10
database.validationInterval=3000
halfer
  • 19,824
  • 17
  • 99
  • 186
riya ahuja
  • 260
  • 6
  • 18
  • how do you get your connection?? maybe you defined it as a static and that's why you're getting this error. – Pmakari Jun 02 '17 at 13:33
  • Since you are using pooled data source for connections, your application might be making too many connections than the allowable limit, After it has reached the limit, it might not be allowing further connections. – Ravi Chandra Jun 02 '17 at 13:33
  • add this in your config jdbcInterceptors="ResetAbandonedTimer" – Pmakari Jun 02 '17 at 13:37
  • @ParvizMakari in our code this is set resource.setProperty("jdbcInterceptors", "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;" + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"); – riya ahuja Jun 02 '17 at 13:43
  • add this property also org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer, you are using database.removeAbandonedTimeout=6 and this resets your connection – Pmakari Jun 02 '17 at 13:46

2 Answers2

2

Few suggestions that you can follow from similar posts here on SO, for example the verified Validation Query seems to be SELECT 1 which is not used in your case.

Refer to this question on SO, which illustrates the usage of Validation queries for different DBs.

database.validationQuery=SELECT 1

Another suggestion that can work for you is the connectionTimeout parameter which can be set to a higher value, or else a smaller value will force the session to close.

connectionTimeout="300000"

Hope these suggestions help!

N00b Pr0grammer
  • 4,503
  • 5
  • 32
  • 46
0

Try to add below properties in your application.properties or configuration file in case external configuration.
database.test-on-borrow=true
database.validation-query=SELECT 1

Ashok Prajapati
  • 374
  • 2
  • 7