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'
The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection. By default, the server closes the connection after 8 hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld.
Open the MySQL Workbench Preferences. Check if the SSH Timeout and DBMS Timeout value is set to only a few seconds. Try to increase the default value of the connection timeouts. Save the settings, close the MySQL Workbench and reopen the connection to see if you are able to connect to the database.
Check this answer: Spring Boot JPA - configuring auto reconnect
In short, you'll need:
spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With