How to Troubleshoot \'com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed\'?

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

Issue:
When deploying an application to a remote server, a MySQL connection exception occurs after the application runs for over a day. The exception message states that no operations are allowed after the connection is closed.

Cause:
The exception is likely caused by a broken connection due to inactivity. The default timeout settings for the MySQL server and client might be too short, leading to connections being closed prematurely.

Solution:

1. Adjust Server and Client Timeouts

• Server (MySQL Configuration):

  • Increase the "wait_timeout" value in the MySQL database configuration file (my.cnf or my.ini). This setting determines the maximum amount of time a connection can remain inactive before being closed.

• Client (Hibernate/JDBC Connection Pool):

  • Set the "timeout" property in the Hibernate configuration file to a higher value, such as the recommended 600 seconds (10 minutes). This ensures that Hibernate does not close connections too quickly.

2. Enable Connection Pooling

• Use a connection pool such as C3P0 or DBCP to manage database connections efficiently. Connection pools create and maintain a pool of pre-established connections, reducing the overhead of opening and closing connections each time.
• Configure the connection pool to automatically check and validate connections before use. This ensures that any broken connections are detected and discarded, preventing exceptions.

Additional Measures:

• Set the "autoReconnect" property in the JDBC connection pool configuration to true. This allows the pool to automatically re-establish connections when they fail.
• Use the "testConnectionOnCheckout" property to test connections before using them. This ensures that any stale connections are detected and removed from the pool.
• Monitor the application logs regularly to detect any potential connection issues early on.

Updated Hibernate Configuration:

The provided updated Hibernate configuration file uses C3P0 connection pooling and includes the recommended settings:

<hibernate-configuration>
  <session-factory>
    <!-- Database connection settings -->
    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/xyz</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">root</property>

    <!-- Enable C3P0 connection pooling -->
    <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
    <property name="c3p0.max_size">20</property>
    <property name="c3p0.min_size">5</property>
    <property name="c3p0.max_statements">0</property>
    <property name="c3p0.timeout">600</property>
    <property name="c3p0.idleConnectionTestPeriod">3600</property>
    <property name="c3p0.testConnectionOnCheckout">true</property>
  </session-factory>
</hibernate-configuration>

The above is the detailed content of How to Troubleshoot \'com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed\'?. For more information, please follow other related articles on the PHP Chinese website!