darklord darklord - 4 months ago 46
SQL Question

Connection lost overnight (spring boot + mysql)

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'

Answer

Check this answer: Spring Boot JPA - configuring auto reconnect

In short, you'll need:

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