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:

 

Advertisements

About Paul C

I am a Senior Java programmer/Web Technologies, Play in a band called Mandolin Monday, I have a cat and I live on a boat.

Posted on September 27, 2011, in MYSQL. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: