Search code examples
mysqljsf-2connection-pooling

JSF Application creating too many DB connections

I've built a (relatively)simple JSF application using a MYSQL 5.1 db and Tomcat 7. Things are working in the sense that nothing blows up, but there is a curiosity...

Using the MySQL workbench, I can see that, over a period of time, I build up an ever increasing list of DB connections that are never released. Each one has the format:

[id] [user] [host] [dbname] [command] [time] [State] [info]

State is always blank and Command is always 'sleep'.

I'm following best practices when opening and closing connections, statements, and as far as I can tell, none of my ManagedBeans has any persistent connection to the dataSource, all of them should fall out of scope anyway as soon as I switch between pages.

I'm struggling to find answers, and that's partly because I'm not quite sure where to look. Any suggestions on what to review/troubleshoot, are there some common JSF/DBCP pitfalls I'm not aware of ?

This might be useful:

<Resource 
    name="jdbc/TrackerDB" 
    auth="Container" 
    type="javax.sql.DataSource"
    maxActive="20" 
    maxIdle="10" 
    maxWait="100"
    username="xxxx" 
    password="xxxx" 
    removeAbandoned="true"
    removeAbandonedTimeout="300"
    logAbandoned="true"
    driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/tracker"
/>
Solution

  • What kind of persistence framework are you using?

    If it's JPA you should close() the EntityManager each time (falling out of scope isn't good enough)!

    If it's hibernate you should close() the Session each time.

    If it's JDBC you should close() the Statement each time.

    Closing such a resource is usually done in a finally clause (of the try block).

    P.S. You don't mention closing ResultSets. I assume you close those too?