CODEWITHSUNDEEP
javasql-serverjsftomcatconnection-pooling
Robert Snyder
Robert Snyder

Reputation: 2409

Why is our Connection Pool Closing?

We have a JSF2.0 webserver running on Apache Tomcat 7.0.54 running on Windows Server 2008 R2. We have 2 SQL servers on on the machine this is running on, and another which hosts our inventory software. One part of our webpage is validation of PartNumbers being added. After reading that Connection Pools are best practice for talking with SQL servers we created one and used it for some of the validation against our inventory software.

Because I wanted a way to be able to check the health of the connection pool I created a test page with a ViewScoped backing bean that validates 2 known good part numbers. Today is the second time this week that the error message "Connection is closed" appeared. Since i'm new to conenction pools and can't seem to find any information regarding this I'm baffled as to what we didn't setup right. I just now reset the Apache server and it is back up and running. So.. to create our connection pool I added some code to the app's META-INF/context.xml.

    <Resource type="javax.sql.DataSource"
        name="jdbc/FOODB"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        url="jdbc:sqlserver://Foobar\Inventory;databaseName=FooInventory;user=johnDoe;password=astrongpassword;"
    />

The basic concept that I have to use the ConnectionPool is that I have an interface called SqlAccessCommand. It is more or less an adapter pattern. In my test page i use the RunUnsafe method so that I can display the error message. So here is the RunUnsafe method.

protected static DataSource getDataSource() throws NamingException {
    Context context = (Context) new InitialContext().lookup("java:/comp/env");
    DataSource ds = (DataSource) context.lookup("jdbc/FOODB");
    return ds;
}
public static <T> T RunUnsafe(SqlAccessCommand<T> command) throws NamingException, SQLException {
    try {
        DataSource ds = getDataSource();
        try (Connection connection = ds.getConnection();
                PreparedStatement statement = connection.prepareStatement(command.getSqlStatement())) {
            command.prepareStatment(statement);
            try (ResultSet rs = statement.executeQuery()) {
                return command.getResults(rs);
            }
        }
    } catch (NamingException | SQLException e) {
        Logger.getLogger(AOSqlInformationHolder.class.getName()).log(Level.SEVERE, null, e);
        throw e;
    }
}

As you can see i use the try with resources which is supposed to close my connections after use no matter what. Much like the try/catch/finally does, just cleaner (IMO). so when my connection is open this works just fine. so far 2x now I have had to restart the server (since I don't know how to re-open said connection any other way) what am I missing? If more parts of the puzzle are needed leave me a comment and I will post what I can. Thank you.

EDIT

just looked into the log files and this morning there was this exception thrown

com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error

According to the comments thus far it was assumed a timeout. Does this error message still point in that direction?

Upvotes: 4

Views: 11807

Answers (3)

Gilbert V
Gilbert V

Reputation: 1040

I would recommend using the Abandon configurations options. 

<Resource type="javax.sql.DataSource"
    name="jdbc/FOODB"
    factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
    driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
    url="jdbc:sqlserver://Foobar\Inventory;databaseName=FooInventory;user=johnDoe;password=astrongpassword;"
    removeAbandoned="true"
        removeAbandonedTimeout="60"
        logAbandoned="true" 
/>

Upvotes: 0

Paul Vargas
Paul Vargas

Reputation: 42010

You need to add some options 1 in the connection pool configuration to detect if the connection is still valid.

The simplest way is to run a simple SQL statement 2 to test the connection.

So the pool configuration can be:

<Resource type="javax.sql.DataSource"
          name="jdbc/FOODB"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
          url="jdbc:sqlserver://Foobar\Inventory;databaseName=FooInventory;user=johnDoe;password=astrongpassword;"
          validationQuery="SELECT 1"
          validationQueryTimeout="1000"
          testOnBorrow="true"
/>

Notes

  1. To view the available options: The Tomcat JDBC Connection Pool
  2. Efficient SQL test query or validation query that will work across all (or most) databases

Upvotes: 6

Elliott Frisch
Elliott Frisch

Reputation: 201409

Your connections are almost certainly timing out due to inactivity, based on your log message of com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error you could read this Technet note which says,

A connection was forcibly closed by a peer. This normally results from a loss of the connection on the remote socket due to a timeout or a restart.

Upvotes: 3

Related Questions