Connection lost overnight (spring boot + mysql)

Question

My spring boot application always show me this whitelabel error in the morning: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.TransactionException: JDBC begin transaction failed:

I searched the web I think it might be that mysql closes the connection for 8 hours of inactive. However according to the document spring boot will automatically configure a pooling apache datasource. http://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html

I am not sure how to configure the application code or database.

Here is the build.gradle I am using:

buildscript {
    ext {
        springBootVersion = '1.2.3.RELEASE'
    }
    repositories {
        maven { url "http://repo.spring.io/libs-snapshot" }
        mavenLocal()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:1.2.3.RELEASE")
    }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'idea'
apply plugin: 'spring-boot'
apply plugin: 'war'
apply plugin: 'application'

repositories {
    mavenCentral()
    maven { url "http://repo.spring.io/libs-snapshot" }
    maven { url "http://maven.springframework.org/milestone" }
}

// Seems tomcat 8 doesn't work with paypal
configurations.all {
    resolutionStrategy {
        eachDependency {
            if (it.requested.group == 'org.apache.tomcat.embed') {
                it.useVersion '7.0.59'
            }
        }
    }
}

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web:${springBootVersion}")
    compile("org.springframework.boot:spring-boot-starter-tomcat:${springBootVersion}")
    compile("org.springframework.boot:spring-boot-starter-actuator")
    compile("org.springframework.boot:spring-boot-starter-mail:${springBootVersion}")
    compile("org.springframework.boot:spring-boot-starter-aop:${springBootVersion}")
    compile("org.springframework.boot:spring-boot-starter-test:${springBootVersion}")
    compile("org.springframework.boot:spring-boot-starter-security:${springBootVersion}")
    compile("org.springframework.boot:spring-boot-starter-data-jpa:${springBootVersion}")
    compile("org.springframework.data:spring-data-rest-webmvc")
    compile("javax.servlet:jstl:1.2")
    compile("org.apache.logging.log4j:log4j-api:2.3")
    compile("org.apache.logging.log4j:log4j-core:2.3")
    compile("com.paypal.sdk:rest-api-sdk:1.2.1")
    compile("com.opencsv:opencsv:3.4")
    compile("mysql:mysql-connector-java:5.1.35")
    compile("com.google.guava:guava:17.0")
    compile("org.apache.httpcomponents:httpclient:4.3.4")
    compile("com.squareup.retrofit:retrofit:1.6.0")
    compile("commons-io:commons-io:2.4")
    compile("org.apache.commons:commons-lang3:3.4")
    compile("com.amazonaws:aws-java-sdk:1.9.34")

    providedCompile("org.apache.tomcat.embed:tomcat-embed-jasper:8.0.22")

    testCompile("junit:junit")
}

sourceCompatibility = 1.7
targetCompatibility = 1.7

war {
    baseName = 'gs-convert-jar-to-war'
    version =  '0.1.0'
}

task wrapper(type: Wrapper) {
    gradleVersion = '2.3'
}

Here is the database configurations in application.properties:

spring.datasource.url=jdbc:mysql://fakeurl:3306/qa
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
#
# hibernate
#
spring.jpa.hibernate.ddl-auto=update

For the datasource I use spring data's repository:

@Repository
public interface EventRepository extends CrudRepository<EventDetail, Long> {

}

Here is the result against the mysql database running "show variables like '%timeout%':

'connect_timeout','10'
'delayed_insert_timeout','300'
'innodb_flush_log_at_timeout','1'
'innodb_lock_wait_timeout','50'
'innodb_rollback_on_timeout','OFF'
'interactive_timeout','28800'
'lock_wait_timeout','31536000'
'net_read_timeout','30'
'net_write_timeout','60'
'rpl_stop_slave_timeout','31536000'
'slave_net_timeout','3600'
'wait_timeout','28800'

score 18 · Accepted Answer · edited May 23 '17 at 12:08

18

Check this answer: Spring Boot JPA - configuring auto reconnect

In short, you'll need:

spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1

edited May 23 '17 at 12:08

Community

1
1

answered Jul 04 '15 at 19:32

Lukas Hinsch

1,840
14
8

Didn't see that message in recent two days. I think it is working, – darklord Jul 06 '15 at 14:49

score 6 · Answer 2 · answered Jan 11 '16 at 14:42

6

You can also see this link It tells you to include the following lines in the application.properties file.

spring.datasource.testWhileIdle = true
spring.datasource.timeBetweenEvictionRunsMillis = 3600000
spring.datasource.validationQuery = SELECT 1

answered Jan 11 '16 at 14:42

Soumya

1,833
5
34
45

score 0 · Answer 3 · answered Jul 28 '16 at 17:07

You can fix this by setting the property wait_timeout = 31536000 in the DB Server. This is equivalent to one year. In case if you DB is running in the AWS environment, go to Parameters group-> create a new group -> set wait_timeout = 31536000 and assign the newly created parameters group to your RDS instance. If you want to fix this in the spring boot instance, you can fix by C3P0 settings only, but I haven't tried that.

Connection lost overnight (spring boot + mysql)

3 Answers3

Linked