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

 

Before you do any of the following make sure you are using the very latest MYSQL driver.

I have been hunting through the internet to find a solution to this one, after about 24 hours it seemed that the Glassfish 3 server could no longer find connections. The solution was actually in the error message (but you know how you never read ALL the way through a java error message).

The last packet successfully received from the server was 48,673,937 milliseconds ago. The last packet sent successfully
to the server was 48,673,937 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. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at

Actually this is middy deceptive, because you need to set more than that.

I set in my sun-resources.xml file…

<jdbc-connection-pool allow-non-component-callers="false" associate-with-thread="false" connection-creation-retry-attempts="10" connection-creation-retry-interval-in-seconds="1" connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0" connection-validation-method="auto-commit" datasource-classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" fail-all-connections="false" idle-timeout-in-seconds="300" is-connection-validation-required="false" is-isolation-level-guaranteed="true" lazy-connection-association="false" lazy-connection-enlistment="false" match-connections="false" max-connection-usage-count="0" max-pool-size="30000" max-wait-time-in-millis="60000" name="mysql_sollatek_adminPool" non-transactional-connections="false" pool-resize-quantity="3" res-type="javax.sql.ConnectionPoolDataSource" statement-timeout-in-seconds="-1" steady-pool-size="8" validate-atmost-once-period-in-seconds="0" wrap-jdbc-objects="false">
    <property name="User" value="admin"/>
    <property name="Password" value="xxxx"/>
    <property name="serverName" value="xxxx"/>
    <property name="portNumber" value="3306"/>
 <property name="autoReconnect" value="true"/>
 <property name="autoReconnectForPools" value="true"/>
    <property name="databaseName" value="xxxx"/>
    <property name="URL" value="jdbc:mysql://xxxx>
    <property name="driverClass" value="com.mysql.jdbc.Driver"/>
  </jdbc-connection-pool>

And in fact any where else you make a connection. Ensure that the auto reconnect is set to true. You could also set the number of retries in the reconnections to a higher property (default is 3).

Full details of these properties are usefully available here

Oh and a gotcha, don’t forget that if you have an app/jdbc resource on the server you need to add the properties to it in the jDBC connection pool and restart the server otherwise it won’t get there!

If none of that works, try making sure that the glass fish server only keeps the connections open for a shorter time than the mysql server.

In your connection pools…

Pool Settings

Initial and Minimum Pool Size:	Connections Minimum and initial number of connections maintained in the pool
Maximum Pool Size:	Connections Maximum number of connections that can be created to satisfy client requests
Pool Resize Quantity:	Connections Number of connections to be removed when pool idle timeout expires
Idle Timeout:	Seconds Maximum time that connection can remain idle in the pool
Max Wait Time:	Milliseconds Amount of time caller waits before connection timeout is sent

If none of this works and you still have the problem (which is odd on one glass fish server this fixed it) then change the datasource from a connection pool driver to an XA datasource driver. 

Pool Name:	my connectionpoolname
Resource Type:	javax.sql.DataSource  javax.sql.XADataSource  javax.sql.ConnectionPoolDataSource  java.sql.Driver  Must be specified if the datasource class implements more than 1 of the interface.
Datasource Classname: