Running out of connections when Querying MySQL using Spring and JDBC

Recently at work we ran into a problem when running some of our data migration tooling that uses Spring, JDBC and MySQL. At seemingly random moments into the process the application would fail with an exception.

The information shown depends in the location the exception is happening. Initially we only saw this:
Connection timed out: connect


Or this:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure


It might even be as insightful as this:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.
This is usually caused by a limit on the number of sockets imposed by the operating system. This limit is usually configurable.
For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system reconfiguration may also be required.
For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271).
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:406)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2103)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:718)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)
at sun.reflect.GeneratedConstructorAccessor65.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:406)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:154)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:173)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:164)


Here the cause is clearly stated. Somehow the MySQL driver is opening many new connections until it reached the limits of the Operating System. A solution could be to try to find a better driver, but instead we opted to introduce connection pooling using Apache commons-dbcp as suggested in the comments for DriverManagerDataSource and this SpringSource forum post. Simply add it to your pom.xml (if you are using maven):

<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.3</version>
</dependency>


And change your datasource:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>


Done. Now when running our tooling only 18 connections are made instead of 3000+ before.