07/20 01:38:26 hibernate.util.JDBCExceptionReporter     - Communications link failure

The last packet successfully received from the server was 7.202.618 milliseconds ago.  The last packet sent successfully to the server was 7.202.618 milliseconds ago.

This will show you the current connections:

show status like '%onn%';

Example of output:

mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 0     |
| Connections              | 50    |
| Max_used_connections     | 9     |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 8     |
+--------------------------+-------+
7 rows in set (0.00 sec)

Pay attention to the data “Threads_connected”, which is similar to the result of the show processlist.

show variables like "max_connections";

mysql> show variables like '%imeout%';
+----------------------------+----------+
| 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 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+
10 rows in set (0.00 sec)

Eventually I've implemented a configuration in the connection pooling mechanism to:

• verify the connection when it enters into the pool
• put a timeout to this connection, releasing it after this period

This will prevent the case when a connection is held but the JDBC driver drops out.

My config parameters:

    <property name="minPoolSize" value="2"/>
    <property name="maxPoolSize" value="4"/>
    <property name="initialPoolSize" value="1"/>
    <!-- good for mysql. For oracle, select * from dual could be good enough -->
    <property name="preferredTestQuery" value="select 1"/>
    <property name="testConnectionOnCheckin" value="true"/>
    <property name="idleConnectionTestPeriod" value="60"/>

And where I've taken them:

http://www.mchange.com/projects/c3p0/