The last packet sent successfully to the server was 79,547 milliseconds ago. is longer than the server configured value of 'wait_timeout'

Question

I have a big problem and I do not know how to fix it:

I have a singleton instance of the database as follow:

public Connection getConnection() throws SQLException {
    if (db_con == null)
        db_con = createConnection();

    return db_con;
}

And I have a code that as follow:

   shortTextScoringComponent.scoreComponent(    "RS",SelectDataBase.getBlogs(rightSarcastic));
    shortTextScoringComponent.scoreComponent( "RNS",SelectDataBase.getBlogs(rightNonSarcasm));
    shortTextScoringComponent.scoreComponent( "FNS",SelectDataBase.getBlogs(wrongNonSarcasm));
    shortTextScoringComponent.scoreComponent( "FS",SelectDataBase.getBlogs(wrongSarcasm));

So as you can see I call the database 4 times and it is note worthy that between each call there is a long time of processing so after he second line is performed successfuly I mean this line:

SelectDataBase.getBlogs(rightNonSarcasm);

when it comes to the third line I get the following error:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet    successfully received from the server was 79,547,948 milliseconds ago.  The last packet sent successfully to the server was 79,547,964 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

   com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No     operations allowed after connection closed.

I searched a lot but there are many different answers which confuses me, do you have any idea what my exact problem is?

Answer 1

First of all as the exception says do add

autoReconnect=true

in your connectionString also add this as well

tcpKeepAlive=true

Secondly you can keep a polling thread to keep checking connection activeness

class PollingThread extends Thread
{
    private java.sql.Connection connection;

    PollingThread(int index, java.sql.Connection connection)
    {
        super();
        this.connection = connection;
    }

    public void run()
    {
        Statement stmt = null;
        while (!interrupted())
        {
            try
            {
                sleep(15 * 60 * 1000L);
                stmt = connection.createStatement();
                stmt.execute("do 1");
            }
            catch (InterruptedException e)
            {
                break;
            }
            catch (Exception sqle)
            {
                /* This thread should run even on DB error. If autoReconnect is true,
                 * the connection will reconnect when the DB comes back up. */
            }
            finally
            {
                if (stmt != null)
                {
                    try
                    {
                        stmt.close();
                    } catch (Exception e)
                    {}
                }
                stmt = null;
            }
        }