Connection pooling with Java and MySQL in Tomcat web application?

I'd wonder why you're using ConnectionPool_DBA in your code instead of letting Tomcat handle the pooling and simply looking up the connection using JNDI.

I'd wonder why you're using ConnectionPool_DBA in your code instead of letting Tomcat handle the pooling and simply looking up the connection using JNDI. Why are you using an Oracle connection pool with MySQL? When I do JNDI lookups and connection pooling, I prefer the Apache DBCP library.

I find that it works very well. I'd also ask if your DatabaseUtils methods throw any exceptions, because if either of the calls prior to your call to pool.freeConnection() throw one you'll never free up that connection. I don't like your code much because a class that performs SQL operations should have its Connection instance passed into it, and should not have the dual responsibility of acquiring and using the Connection.

A persistence class can't know if it's being used in a larger transaction. Better to have a separate service layer that acquires the Connection, manages the transaction, marshals the persistence classes, and cleans up when it's complete. UPDATE: Google turned up the Oracle class with the same name as yours.

Now I really don't like your code, because you wrote something of your own when a better alternative was easily available. I'd ditch yours right away and redo this using DBCP and JNDI.

That's probably unlikely due to time and other constraints. Thanks though. – Martin OShea Sep 19 '09 at 19:20 Could become more attractive if you can't solve this and it happens again.

– duffymo Sep 19 '09 at 19:56.

There are a few pointers on avoiding this situation, obtained from other sources, especially from the connection pool implementations of other drivers and from other application servers. Some of the information is already available in the Tomcat documentation on JNDI Data Sources. Establish a cleanup/reaper schedule that will close connections in the pool, if they are inactive beyond a certain period.It is not good practice to leave a connection to the database open for 8 hours (the MySQL default).

On most application servers, the inactive connection timeout value is configurable and is usually less than 15 minutes (i.e. Connections cannot be left in the pool for more than 15 minutes unless they are being reused time and again). In Tomcat, when using a JNDI DataSource, use the removeAbandoned and removeAbandonedTimeout settings to do the same.

When a new connection is return from the pool to the application, ensure that it is tested first. For instance, most application servers that I know, can be configured so that connection to an Oracle database are tested with an execute of "SELECT 1 FROM dual". In Tomcat, use the validationQuery property to set the appropriate query for MySQL - I believe this is "SELECT 1" (without quotes).

The reason why setting the value of the validationQuery property helps, is because if the query fails to execute, the connection is dropped from the pool, and new one is created in its place. As far are the behavior of your application is concerned, the user is probably seeing the result of the pool returning a stale connection to the application for the first time. The second time around, the pool probably returns a different connection that can service the application's queries.

Tomcat JNDI Data Sources are based on Commons DBCP, so the configuration properties applicable to DBCP will apply to Tomcat as well.

This error indicates server closes connection unexpectedly. This can occur in following 2 cases, MySQL closes idle connection after certain time (default is 8 hours). When this occurs, no thread is responsible for closing the connection so it gets stale.

This is most likely the cause if this error only happens after long idle. If you don't completely read all the responses, the connection may get returned to the pool in busy state. Next time, a command is sent to MySQL and it closes connection for wrong state.

If the error occurs quite frequent, this is probably the cause. Meanwhile, setting up an eviction thread will help to alleviate the problem. Add something like this to the Data Source, ... removeAbandoned="true" removeAbandonedTimeout="120" logAbandoned="true" testOnBorrow="false" testOnReturn="false" timeBetweenEvictionRunsMillis="60000" numTestsPerEvictionRun="5" minEvictableIdleTimeMillis="30000" testWhileIdle="true" validationQuery="select now().

If so, you must have your connection pool either discard unused-for-more-than-XX-minutes connections from the pool, or do some kind of ping every YY minutes on the connection to keep it active.

None as far as I'm aware. I think it's something in the settings as Vineet suggests. – Martin OShea Sep 19 '09 at 18:30.

On the off chance you haven't found your answer I've been dealing with this for the last day. I am essentially doing the same thing you are except that I'm basing my pooling off of apache.commons.pool. Same exact error you are seeing EOF.

Check your mysqld error log file which is most likely in your data directory. Look for mysqld crashing. Mysqld_safe will restart your mysqld quickly if it crashes so it won't be apparent that this is the case unless you look in its logfile.

/var/log is not help for this scenario. Connections that were created before the crash will EOF after the crash.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.