Connections with MySQL

In May, I wrote a blog about trying to figure out issues that I had with my Tomcat server handling connection pooling with a MySQL database. I have been doing some digging, and thanks to some friends of mine: Jose Sandoval and Norber Mika, I was able to determine that MySQL is not set up to handle connection pooling. Connection pooling is a feature available on larger enterprise databases such as Oracle. The benefit of it is that it allows a connection to be maintained between the application and the database then multi threading is used to pass the connection parts of the application that need to communicate with the database. By sharing database connections from a pool rather than re-connecting to the database every time database activity is required, execution time is improved. However, if a connection is left open for a long period of time (ie, 8 hours), MySQL views it as a stale connection. Then that connection becomes nonfunctional and will likely generates null responses.

The best way I found to get around this problem when using MySQL is to just connect and dis-connect after each transaction with the database. It may cause a small performance hit, but it improves the stability of the application.