SQLContainer too many connections to mysql database

Hello,
I’m using the SQL container in my application to query MySQL database. I’ve attached the SQLContainer as datasource to a table. There are multiple tables I need to query, but at each click on the query the number of connections to the database increases by 20 and it is causing too many connection exceptions. do you know how I can resove this issue?

My application is readonly and does not write to the database.

Thanks,
Jacob.

Jun 28, 2011 10:10:29 AM com.vaadin.Application terminalError
SEVERE: Terminal error:
com.vaadin.event.ListenerMethod$MethodException
Cause: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections
at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:510)
at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:164)
at com.vaadin.ui.AbstractComponent.fireEvent(AbstractComponent.java:1193)
at com.vaadin.ui.AbstractField.fireValueChange(AbstractField.java:891)
at com.vaadin.ui.AbstractField.setValue(AbstractField.java:529)
at com.vaadin.ui.AbstractSelect.setValue(AbstractSelect.java:666)
at com.vaadin.ui.AbstractSelect.changeVariables(AbstractSelect.java:475)
at com.vaadin.ui.Table.changeVariables(Table.java:2038)
at com.vaadin.terminal.gwt.server.AbstractCommunicationManager.handleVariableBurst(AbstractCommunicationManager.java:1299)
at com.vaadin.terminal.gwt.server.AbstractCommunicationManager.handleVariables(AbstractCommunicationManager.java:1219)
at com.vaadin.terminal.gwt.server.AbstractCommunicationManager.doHandleUidlRequest(AbstractCommunicationManager.java:735)
at com.vaadin.terminal.gwt.server.CommunicationManager.handleUidlRequest(CommunicationManager.java:296)
at com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:501)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:164)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:563)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:403)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:301)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:162)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:309)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections
at com.vaadin.addon.sqlcontainer.query.TableQuery.fetchMetaData(TableQuery.java:525)
at com.vaadin.addon.sqlcontainer.query.TableQuery.(TableQuery.java:87)
at com.vaadin.addon.sqlcontainer.query.TableQuery.(TableQuery.java:101)
at com.ericsson.ossmt.util.DatabaseHelper.getManagedComponentDowntimeDetails(DatabaseHelper.java:132)
at com.ericsson.ossmt.ManagedComponentDowntimeDetails.(ManagedComponentDowntimeDetails.java:12)
at com.ericsson.ossmt.OSSMonitoringTool.getManagedComponentDowntimeDetailsView(OSSMonitoringTool.java:265)
at com.ericsson.ossmt.OSSMonitoringTool.valueChange(OSSMonitoringTool.java:184)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:490)
… 29 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:935)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4101)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1300)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2337)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:792)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor12.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
at com.vaadin.addon.sqlcontainer.connection.SimpleJDBCConnectionPool.createConnection(SimpleJDBCConnectionPool.java:124)
at com.vaadin.addon.sqlcontainer.connection.SimpleJDBCConnectionPool.initializeConnections(SimpleJDBCConnectionPool.java:76)
at com.vaadin.addon.sqlcontainer.connection.SimpleJDBCConnectionPool.reserveConnection(SimpleJDBCConnectionPool.java:83)
at com.vaadin.addon.sqlcontainer.query.TableQuery.fetchMetaData(TableQuery.java:480)
… 40 more

Hi,

when you are creating your SimpleJDBCConnectionPool you should set the initialConnections parameter low enough - this number of connections is created at the init of the connection pool. It is also strongly recommended that the maxConnections parameter should be set to a lower (or the same) value as what is set in your db as maximum amount of connections.

Also note that you can and should reuse the same connection pool for all your SQLContainers whenever possible. If you are re-creating the connection pool e.g. for each query or for each SQLContainer you use in your application, you will soon end up with enormous amounts of connections.


Tepi

Hi Teppo,
Thanks for taking time for helping me out. I upgraded my database to MySQL 5.5 from 5.0 and now I’m not having a very high number of connections like over 100. But still the connections are about 30 - 40 which I feel maybe higher.

Any ways back to your comment: I apologize in advance for this very basic question/comment on my code (below), but I would like to get expert opinions :slight_smile:

I think I’m only using one Connection Pool and not creating new connections for my queries. I have a Singleton Database Helper Class and in the constructor I create a connection pool. And then I’ve multiple functions in my DatabaseHelper class returning SQL Containers.

Do you think this is correct, does it cause it to create multiple connections?

Thanks for your help
Jacob.

My constructor

private JDBCConnectionPool pool;

private DatabaseHelper {
pool = new SimpleJDBCConnectionPool(“com.mysql.jdbc.Driver”, “jdbc:mysql://localhost:3306/mydb”, “user”, “password”);
}

Then I’ve multiple functions for querying my tables which return a SQLContainer and I create my container using the following code

public SQLContainer getUsersTableContainer() {

SQLContainer container = null;
try {
TableQuery query = new TableQuery(“users”, pool);
container = new SQLContainer(query);
} catch (SQLException e) {

e.printStackTrace();

}

return container;
}

The database (or version) used should not affect the amount of connections made by the SQLContainer.

This is a valid way of approaching the problem. Another approach is to create a static field in e.g. your application class for the connection pool, initialize it once (with enough initial/max connections) and have a static method for accessing the single connection pool. You may want to use the following constructor:

public SimpleJDBCConnectionPool(String driverName, String connectionUri, String userName, String password, int initialConnections, int maxConnections)

I can’t right away see anything wrong with this, but there must be multiple connection pools created at some point, because the default value for max. number of connections is 20 and you’re not setting it to a higher value. If you’re seeing 30-40 connections there must be connections from another connection pool or from somewhere else.

As such the SQLContainer does not require a high amount of connections. For example, you might want to see the test classes in the SQLContainer package; they are all run with a connection pool that has a maximum number of connections fixed at 2.


Tepi

Hi Tepi,
Do you think it could because there are multiple users accessing the webgui? Or is it because I keep redeploying the multiple times WAR file during development without restarting tomcat? Between, I’m using tomcat 7 for deploying the applications. I tried to use Container Datasource, but for some reason I couldn’t get it to work, I’ll try that again.

Thanks,
Jacob.

Hi,

Multiple users or redeployment should not leave old connections open or create multiple connection pools. Without seeing your complete code here, I’d suggest that you debug your app and put a break point e.g. on the line where the connection pool is created. That way you could easily see how many of them are created and where the call is coming from.


Tepi