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.

Persistent JMS Topics using ActiveMQ and Spring

At work we noticed that a messaging application based on Topics was loosing pending messages after a restart of ActiveMQ. It turns out we were not using Durable Topic Subscribers. To configure these add this to your Spring Listener configuration:
<bean id="myListenerContainer"
class="org.springframework.jms.listener.DefaultMessageListenerContainer">
    <!-- never use concurrentConsumers with topics ! -->
    <property name="concurrentConsumers" value="1">
    <property name="connectionFactory" ref="jmsActiveMQFactory">
    <property name="destination" ref="myTopic">
    <property name="messageListener" ref="myMessageListener">
    <property name="sessionTransacted" value="true">
    <!-- Specify topic style publish/subscribe -->
    <property name="pubSubDomain" value="true">
    <!-- Will guaranty that we receive messages in queue after broker goes down -->
     <property name="subscriptionDurable" value="true">
     <property name="clientId" value="myApp">
     <property name="durableSubscriptionName" value="myApp">
</bean>

Both the durableSubscriptionName and clientId are required.

Also, you should ensure the JmsTemplate is configured to persistent delivery mode (this is the default):
<bean id="jmsActiveMQTemplate" class="org.springframework.jms.core.JmsTemplate">
     <property name="connectionFactory" ref="jmsActiveMQFactory">

     <!-- Value = javax.jms.DeliveryMode.PERSISTENT -->
     <property name="deliveryMode" value="2">

     <!-- Value = javax.jms.Session.CLIENT_ACKNOWLEDGE -->
     <property name="sessionAcknowledgeMode" value="2">

     <!-- Needs to be true for the deliveryMode to work -->
     <property name="explicitQosEnabled" value="true">
</bean>

To check if your Topic Subscribers are durable you can use the ActiveMQ web admin interface, your application should be listed under 'Subscribers'.

SpringOne EU 2009 wrapup

So here's a small summary of the highlights at SpringOne EU 2009. Fortunately for me the conference was in Amsterdam so I could come by bike :-).

Keynote Rod Johnson:
There where some interesting statements from Rod about Oracle's acquisition of Sun. For Java itself it actually doesn't matter. Java is now open source so cannot be taken away from us. And open source is the space where most of the innovation is happening now anyway. He compared Larry Ellison with Genghis Khan asking "what would Genghis Khan do?". Let us just assume the worst about it. We are already independent anyway.
He also had a positive message for us java developers. Despite all the fuss from dynamic languages we are a stable and proven platform. Frameworks like Spring help us build systems better and easier.
There were some new tech annoucements to help us out. Spring Roo is a command line tool to quickly generate scaffolding code for you app so you can get quickly started. Interesting that text input had been chosen instead of a GUI. As developers this gives us more power and flexibility. Roo has been released tonight!
Also the SpringSource Tool Suite will be available for free from now on. From what I've seen in the demos it looks pretty nice. Unfortunately the source code will not be available. Will the source ever be available? What's that about SpringSource?

Monday:
Introduction to Spring MVC and Spring 3.0. Everything can be configured using annotations and thanks to convention over configuration there is a lot less to configure. Ben Alex raced through his new tool Roo and we can vote to change the name (I voted to keep Roo).

Tuesday:
Adrian Colyer gave an interesting and entertaining talk about deploying into the cloud (app engine, amazon, etc). He had 4 demos - no less. Another new web feature in Spring 3.0: REST. Nice annotation based syntax with some powerful features such as content negotiation to allow for different views. The talk on Terracotta was more or less a sales pitch but yet it was interesting to learn about the technology. Cool demos too.

Wednesday:
Very interesting introduction to Groovy from the head of Groovy development: Guillaume Laforge. Groovy is very much like Ruby to me. The combination with Grails seems even more perfect. Grails is build on top of Spring MVC. A dynamic web framework on a stable stack. Unfortunately the talk about Grails was a bit chaotic. There were some complex demos about mixing Java with Groovy code but I really expected an introduction to the framework like the one for Groovy. I'm going to look into Grails anyway.
Two fascinating talks about Spring Integration and ActiveMQ. It's about the patterns as proposed in the book Enterprise Integration Patterns. Messages queues, channels, routers, etc. The talks were packed with information and excitement, very technically stimulating. This is probably because I myself recently developed a queueing system in Java with ActiveMQ. It has gone into production last week.
For the final talk there was an excellent recap of the AOP choices you have with Spring. AspectJ is a DSL for AOP, it's a superset of Java so you can write any Java code with it. Interesting. I've learned about AOP before, but never actually felt the need to use it to solve a problem. Guess when you have a hammer everything starts looking like a nail.

That's it for now. It was a fun and interesting conference. Hope to do some more of these in the future.

You don't have to remember anything when you write unit tests

I'm a fan of English rapper Mike Skinner (The Streets), he gives some good advice in his songs. For example:

If you never tell a lie to her, you don't have to remember anything.

That's easy right; you never have to be paranoid about creeping inconsistencies into your story if you just stick to the facts.

It's kind of the same with unit testing. Ever have the feeling 'I sure hope no-one will break this subtle case' when you're writing code with some twisted logic. You try to clarify it with comments, but still it doesn't feel right and it keeps hogging your mind. Well when you write a test you don't have to bother worrying anymore, the test will fail when you break the code and if you cover your subtle cases you're safe.

It is hard to convince developers to write unit tests. Some feel like writing tests is a waste of time. I felt the same way, until the first time the tests found errors that I did not expect. Every time a test fails is like a little gift to yourself; some fustrating hours have been saved thanks to the test harness.

Double Brace Initialization

While browsing some code I came across this nugget:

List colors = new ArrayList() {{
add("red");
add("yellow");
add("blue");
add("green");
}};

What is this? Some kind of secret syntax for initializing collections?

No. Look further... it's double brace initialization.

The first brace creates an inner class, like how you would instantiate a thread:

Thread t = new Thread() {
public void run() {}
};

The second brace is an intializer block, a not so often used feature to initiate class instances like:

class Thingy {
 int x;

 {
   x = 42;
 }
}

Together they create an anonymous inner class with a block of code that will execute immediately. This way you can call any method of the instance you want, such as add() in the example.

Nice trick to freak out your colleagues :-). Beware though as a colleague warned me. You have to be careful since the instance is not of the reference type class but of a subclass. The result from equals() might not always be as you expect when it calls getClass() on the object.

There are other interesting Java idioms to read about too.

EasyMock gotcha: you can't use argument matchers as return value

I was getting some really nasty error messages while running my unit tests. A test that used EasyMock (using 2.0) failed but it wasn't anything in the actual test, it was caused by a test that ran before it. When skipping the test case the next test case using EasyMock would fail.

The error I was getting:

java.lang.IllegalStateException: 2 matchers expected, 1 recorded.
   at org.easymock.internal.ExpectedInvocation.createMissingMatchers(ExpectedInvocation.java:41)
   at org.easymock.internal.ExpectedInvocation.(ExpectedInvocation.java:33)
   at org.easymock.internal.ExpectedInvocation.(ExpectedInvocation.java:26)
   at org.easymock.internal.RecordState.invoke(RecordState.java:63)
   at org.easymock.internal.MockInvocationHandler.invoke(MockInvocationHandler.java:24)
   at org.easymock.internal.ObjectMethodsFilter.invoke(ObjectMethodsFilter.java:45)
   at $Proxy88.handleRequest(Unknown Source)

First off, to find the offending test I build a test suite and reduced the tests run to isolate the tests where the error occurred until I had found the one causing the problem.

The code looked like this:

       JdbcOperations jdbcOperationsMock = createMock(JdbcOperations.class);

       // Expect query to use supplied alternative schema
       expect(
               jdbcOperationsMock.query(
                       eq("SELECT ARTICLE_SKU, ARTICLE_NAME FROM alt_schema_subs.tomtomtbarticle;"),
                       isA(RowMapper.class))
               )
               .andReturn(isA(List.class));

Which seems to be ok. The only problem was that I was using a argument matcher on a return value (see documentation). The solution was to supply a normal return value:

  expect(
    jdbcOperationsMock.query(
      eq("SELECT ARTICLE_SKU, ARTICLE_NAME FROM alt_schema_subs.tomtomtbarticle;"),
      isA(RowMapper.class))
    )
    .andReturn(new ArrayList<Object>());

And now the tests succeed.

Hope this was helpful. I intent to update the blog with more day-to-day problems in hope some people might find it on google.

Keeping it up

Oops, then I suddenly stopped updating the blog. This is often the case with me, start something enthusiastically which slowly dies soon after that. Keeping something on going after the start is quite hard.

Anyway, some wise words I heard today at work: "it's worse to make no decision then to make a bad decision". The logic behind that is if you make a bad decision you can't always identify that and change, you learn something. Whereas when you make no decision everything will just drop and nothing will happen. You have to keep on moving forward to keep the pace.