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.

Criticizing is easy, doing is hard

Sometimes I fall into the trap of overly criticizing and asking annoying questions to people who are presenting their work. It's very easy to spot that little grey dot on a white piece of paper. Harder it is to create something completely clean and spotless. And how annoying it is when you've put a lot of effort into something and people only whine about the few little things that are not 100% perfect.

The best solution to participate and do the work yourself. You're not going to heavily criticize yourself. And it shows the reasons why things are not perfect, because it's hard to achieve.

That's why I'm happy with the suggestion of my manager to participate in some of the handover meetings we've been having. I was asking a lot of critical questions, because I like doing that. It's to test my own knowledge and I also like to be right. But only being critical is not the way to go. I'll be more careful.

Wordle cloud

This little toy Wordle is quite cool. Check my word cloud:

Procrastination

The last few days I've been working on a new website cms for Sanne. It's strange that I totally want to build this site for my girl. But something inside me is strongly resisting. Getting started is so hard. Once you start you get going. I still have to teach myself to break this feeling because this is painful.

This letter to a young procrastinator is very striking.

Inbox Zen

Something I've been following for a while: the inbox zero principle. It's quite simple: always have an empty inbox. When you check your mail categorize each item until the inbox is empty. You can either:

• Delete
• Delegate
• Respond
  
• Defer
• Do

The great advantage of this is: less worrying, more doing. Every time you see the rising queue in your mailbox you get demotivated by the amount of work left. If you handle everything you can right away and schedule everything else you save time you can spend on other things!

One other thing I particulary like is having a 'waiting for' folder. Here I move all the mail I have send that I'm expecting a reply to. This way there's no more need to remember if I received a reply. Once a day or so I check my 'waiting for' and send a reminder to people if I think they're taking too long. It's amazing how many people (sorry to say, especially managers) fail to reply to an explicit question in a mail.

Check out this great talk about inbox zero by Merlin Mann. He writes 47 folders and also co-hosts the hilarious podcast You Look Nice Today.

Unit testing hooray!

Slowly I'm getting more into unit testing. It's hard to enforce the discipline to write the tests during development because writing tests takes more time and managers don't like things taking time. But they should be written during development because:
a) That's when you are most into the problem and thus best able to write the test
b) It makes you think about the code you are writing, perhaps discovering bugs as you create them
c) After the fact - there is never time to write tests anyway so it won't happen

When unit tests have been written the benefits are so marvelous. You can re-run them any time and be be sure all your app is still ok. It takes away the fear of changing things because they might break, just run the tests and you are sure you didn't break anything. Beats manually re-testing by a mile.

Finally, I've been getting into EasyMock to mock out the dependencies of my classes. Works great once you get used to it. Say you have a Controller class that needs a Service to search for something:

class Controller {
  private final SearchService searcher;
  public Controller(SearchService searcher) {
    this.searcher = searcher;
  }
}

interface SearchService {
  List doSearch(String query);
}

class SearchServiceImpl implements SearchService {
  List doSearch(String query) {
    // Searching code here
  }
}

Now to unit test the Controller just mock the SearchService:

@Test
public void testController() {
  SearchService searchMock = EasyMock.createMock(SearchService.class);
  EasyMock.expect(searchMock.doSearch("something")).andReturn(isA(List.class));
  EasyMock.replay(searchMock);
  EasyMock.verify(searchMock);
}

And the test can run without an implementation needed. And as a bonus you can check if the mock is called in the expected way.

Btw, I have to find some code syntax highlighting for Blogger.

Using HibernateInterceptor to make database schema name configurable

To fit in our DTAP (Development Test Acceptance Production) cycle I needed to make our application database schema name configurable. The idea is that multiple instances of the application and database can run side-by-side on the same server so each developer and tester can have their own environment if needed.

The problem I quickly realized is that our application uses two different schemas but only one Hibernate Session and the configuration is done using annotations where the schema name for each bean is hardcoded on the @Table annotation. No chance to replace that at runtime, except maybe messing with Reflection which I'm not going to get into now.

So the solution I went with was replacing the schema name on the query just before it is send to the DB using a HibernateInterceptor as described in this blog entry.

Here's how the solution looked in short:

public class ConfigurableSchemaNameHibernateInterceptor extends EmptyInterceptor {
  public static final String DB_SCHEMA1_PATTERN = "schema1";
  public static final String DB_SCHEMA2_PATTERN = "schema2";

  @Override
  public String onPrepareStatement(String sql) {
    String prepedStatement = super.onPrepareStatement(sql);
    prepedStatement = prepedStatement.replaceAll("(?i)" + DB_SCHEMA1_PATTERN, schema1ConfiguredName);
    prepedStatement = prepedStatement.replaceAll("(?i)" + DB_SCHEMA2_PATTERN, schema1ConfiguredName);

    logger.debug("Query replaced, old sql: " + sql);
    logger.debug("Query replaced, new sql: " + prepedStatement);

    return prepedStatement;
  }
}

Maybe this is not too clean and a bit error prone (could accidently replace some genuine content..). But I learned some new things about Hibernate so I'm blogging this anyway. Next time I'll refactor it to use multiple Hibernate sessions and no harcoded schema name.

Productivity and being watched

A thing I noticed is that I am way more productive when I'm working directly with someone. If I'm sitting side to side I don't take all the long way to get somewhere. This person wants results, and wants them now. It amazes me how fast and productive I can be then. Results also motive me a great deal, encourage me to carry on.

I guess I tend to wander off sometimes while working. I discover a problem or something that can be improve. I dive into that. Soon I find something else interesting. It's a recursive process. In the end overall progress is slow.

It's just me being like this though. It's all within myself so I should be able to force myself to work faster and more goal oriented to achieve great results.

It's not also good to take the shortest route though. This usually means using things I already know instead of learning something new. And I tend to make shortcuts and make unclean solutions. That's great for quickly having something tangible but not so great so long lasting quality results. The ideal way would be in the middle somewhere.

Joining on a subquery

Pretty awesome SQL trick I just learned from Wouter. In SQL you can join on a subquery and then you can even select columns from it! The cool thing is that you can do transformations such as Group By in this subquery. This enables things I could not do before such as multiple count columns in one query.

For example:

SELECT account.IDENTIFIER, subscriptionCount.subscriptions, promotionCount.promotions
FROM tomtomtbaccount account
JOIN (SELECT account_fk, COUNT(*) AS subscriptions FROM tomtomtbsubscription GROUP BY account_fk) subscriptionCount ON account.account_id = subscriptionCount.account_fk
JOIN (SELECT account_fk, COUNT(*) AS promotions FROM tomtomtbpromotion GROUP BY account_fk) promotionCount ON account.account_id = promotionCount.account_fk
ORDER BY subscriptionCount.subscriptions
LIMIT 100;

(this query is a bit heavy though...)

Also a nice memonic Wouter provided:
if you want results side by side: JOIN
if you want results in a list: UNION

Go see people

When small problems or misunderstandings occur, just go see the people in person instead of sending mail. The mail communication can cause even more confusion. People in person are generally kinder and have more attention.

Today I was helping a colleague with setting up my application on a test environment. He was complaining so i sat with him and we quickly solved the problem. We found another problem and solved it on the spot too. Within half an hour it was all running. It would have taken a lot of more time and frustration for him to solve all the problems. Now I was giving him attention and thereby promotion myself.

Controlling your boss for fun and profit

Very good article on influencing without power:
http://blogs.msdn.com/eric_brechner/archive/2005/08/01/august-1-2005-controlling-your-boss-for-fun-and-profit.aspx

Basically: don't whine about your manager not listening. You're telling it the wrong way and do something about it if you want to make a change.

Don't be afraid to ask

Don't spend hours staring at the screen. Ask advice. People like being asked for advice, it makes them feel knowledge-able. I can learn a lot from their experience. Other people often have a different view that can bring more ideas. Even explaining the problem is a way of ordering and thinking about it. Not rarely I explain a problem and come up with a solution myself on the spot. Just because I took some distance and thought about it.

Create action lists

Write everything todo down so I don't have to think about it. Spend my braintime on actually doing something.

Testing

A good presentation on unit testing. I should really set up tests from the start. It saves a lot of work in maintenance.

http://www.masukomi.org/talks/unit_testing_talk_2/index.xul?data=slide_data.txt#page1