No operations allowed after connection closed on mysql

Question

I changed host company for my webiste, in which I use a mySQL db. From time to time I get this error:

No operations allowed after connection closed.

I remember I had the same problem the first time I run my site on the old server, and I fixed the problem with a "configuration fix" and not with a "code fix", but I don't remember what I did :(

I read mant posts here, but every talks about code fix and I don't think it's my case

Can someone help me?

Thank you

EDIT

I found this on my old context.xml file; probably this is the way I fixed the problem

  validationQuery="SELECT 1"
  testOnBorrow="true"

testOnBorrow: (boolean) The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. Default value is false

validationQuery:(String) The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query does not have to return any data, it just can't throw a SQLException. The default value is null. Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server)

Can someone confirm?

Answer 1

You can use an autoReconnect=true parameter in your JDBC connection URL: (for example: "jdbc:mysql://localhost:3306/my-db?autoReconnect=true").

Note, though, that this can lead to problems if your application doesn't handle SQLExceptions properly.

From MySql documentation:

autoReconnect

Should the driver try to re-establish stale and/or dead connections? If > enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, as a last option, investigate setting the MySQL server variable "wait_timeout" to a high value, rather than the default of 8 hours.

Answer 2

In your configuration properties you might have used c3p0 property maxIdleTime, but c3p0 properties are configured using c3p0. prefix or hibernate.c3p0.. Note that enabling any of c3p0 properties automatically enables the corresponding connection provider by hibernate heuristics. In the log isn't seen that you are using c3p0. If you are not configured data sources then Hibernate will use hibernate.connection.provider_class org.hibernate.connection.DriverManagerConnectionProvider. This connection provider has in-built rudimentary connection pool for which you can set a hibernate.connection.pool_size, but it is used only for development purposes. Never use it in the production environment.

You might have endless discussion on topic How to fix java.net.SocketException: Broken pipe. After some time you will realize that you have remained open connections in the pool, which are suddenly closed on the other side by the following reasons:

• Firewalls or routers may clamp down on idle connections (the MySQL client/server protocol does not ping).

• The MySQL Server may be closing idle connections that exceed the wait_timeout or interactive_timeout threshold.

  To help troubleshoot these issues, the following tips can be used:

• A recent (5.1.13+) version of JDBC driver is used.

• Ensure that wait_timeout and interactive_timeout are set sufficiently high. Check if interactiveClient is used. •Ensure that tcpKeepalive is enabled.
• Ensure that any configurable firewall or router timeout settings allow for the maximum expected connection idle time.
• Ensure connections are valid when used from the connection pool. Use a query that starts with /* ping */ to execute a lightweight ping instead of full query. Note, the syntax of the ping needs to be exactly as specified here.
• Explicitly validate the connection before using it if the connection has been left idle for an extended period of time.
• Minimize the duration a connection object is left idle while other application logic is executed.

To comply to some of this options you'd better use a connection pool that you can use with Hibernate. Hibernate has support for commons-dbcp, c3p0, and proxool. Also you can configure JNDI data source on web server to use with hibernate, it has a connection pool.