Connection Timeout when application is Idle

Question

I am not able to connect to the database when it my application remains idle for some time. My database is Mysql and server is Tomcat. Application is built using Spring-Hibernate. My connection settings in persistance.xml are like this-

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
    version="1.0">
    <persistence-unit name="GTPU"  >
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
            <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
            <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/application_db_name"/>
            <property name="hibernate.connection.username" value="***"/>
            <property name="hibernate.connection.password" value="***"/>
            <property name="hibernate.show_sql" value="false" />
            <property name="hibernate.format_sql" value="true" />
            <property name="hibernate.use_sql_comments" value="true" />
            <property name="hibernate.connection.zeroDateTimeBehavior" value="convertToNull"/>
        </properties>
    </persistence-unit>
</persistence>

But if I try after some time, it connects to the database successfully.

Mysql variables are set like this

-------------------------------------
Variable_name                 Value
-------------------------------------
connect_timeout            10
delayed_insert_timeout     300
innodb_lock_wait_timeout    50
innodb_rollback_on_timeout  OFF
interactive_timeout        28800
lock_wait_timeout         31536000
log_output                  FILE
net_read_timeout             30
net_write_timeout            60
slave_net_timeout           3600
wait_timeout              28800
--------------------------------------

What can be the reason? how can I solve this.

score 0 · Answer 1 · answered Jun 03 '14 at 06:33

0

Increase your wait_timeout variable to a value that would exceed your maximum idle period. This will solve your problem.

answered Jun 03 '14 at 06:33

aryann

909
5
11

score 0 · Accepted Answer · answered Jun 03 '14 at 13:00

I solved it using a connection pooling library - C3P0 http://www.mchange.com/projects/c3p0/#idleConnectionTestPeriod

Using property idle_test_period, which pings database server after specified interval.

<property name="hibernate.c3p0.idle_test_period" value="100" />
<property name="hibernate.c3p0.preferredTestQuery" value="select 1;" />

Connection Timeout when application is Idle

2 Answers2