JDBC connection to mysql server fails after about 10-20 min of inactivity

Question

As I said in the title, my JDBC connection to my mysql server fails after about 10-20 min of inactivity. I connect to the database using the following code:

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.swing.*;

import org.apache.commons.net.ftp.FTP;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.UserInfo;

public class MyDB
{
    public Connection connect = null;
    public Statement statement = null;
    public PreparedStatement preparedStatement = null;
    public ResultSet resultSet = null;

    public void readDatabase() throws Exception
    {
        JSch jsch = new JSch();
        String host = "ssh.binero.se";
        String user = "username";
        Session session = jsch.getSession(user, host, 22);
        int lport = 1234;
        String rhost = "Ip_to_host";
        int rport = 3306;
        UserInfo ui = new MyUserInfo();
        session.setUserInfo(ui);
        session.connect();

        int assigned_port = session.setPortForwardingL(lport, rhost, rport);
        System.out.println("localhost:" + assigned_port + " -> " + rhost + ":" + rport);
        Class.forName("com.mysql.jdbc.Driver");
        connect = DriverManager
            .getConnection("jdbc:mysql://localhost:1234/database?
            user=user&password=pass&connectTimeout=28800000");

This works fine, but after about 10-20 min of inactivity I get the following error message when I try to use the database connection:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 6 810 583 milliseconds ago.
The last packet sent successfully to the server was 6 810 800 milliseconds ago.

...

Caused by: java.net.SocketException: Software caused connection abort: socket write error

I know I could solve this by disconnecting from the database after every query but I would prefer not to. Also, since I am disconnected from the database after a certain time, I thought I was being timed out. However, I have not found any variables that have helped when I have changed them.

Check whether proxy / firewall sitting in between , that may have timeout — Dhananjay, Jul 11 '12 at 09:54
Coud be socket timeout issue. Does ssh have a socket factory? — MadProgrammer, Jul 11 '12 at 09:56
@blob Ah, that could probably be it. Unfortunately I don't have access to the firewall at work and the guy that does is not here. I will have to wait until next week to find out I guess. — Medo, Jul 11 '12 at 09:57
@Hemant Metalia Does the newly added code make it more clearer? — Medo, Jul 11 '12 at 10:02
Please post the entire code, Are you closing the connection after you done with it? — Tomer, Jul 11 '12 at 10:18
Why don't you use a third-party connection manager; like DBCP? Use spring JDBC template to reduce boilerplate. Spring jdbc also manages DataSource for you. — Sree, Jul 11 '12 at 10:31

score -2 · Answer 1 · answered Jul 11 '12 at 10:18

-2

       Class.forName("com.mysql.jdbc.Driver");
       con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Table_Name","root","");

try this, I've never been timed out! Good luck...!

answered Jul 11 '12 at 10:18

robi terebesi

65
2

JDBC connection to mysql server fails after about 10-20 min of inactivity

1 Answers1