Why is our connection pool a pool?
We have a JSF2.0 web server running on Apache Tomcat 7.0.54 running on Windows Server 2008 R2. We have 2 SQL servers on the machine it is running on and the other on our inventory software. One part of our web page is to check if PartNumbers have been added. After reading that connection pools are best practice for communicating with SQL servers, we created one and used it for some validation on our inventory software.
Since I need a way to test the health of the connection pool, I created a test page with ViewScoped bean support that checks for 2 known good numbers. Today, the second time this week, the error message "Connection is closed" appeared. Since I am new to join pools and cannot find any information on this, I am confused as to what we did not configure correctly. I have just now reset the Apache server and it is up and running. So .. to create a connection pool, I added code to the META-INF / context.xml application.
<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 main concept I have to use the ConnectionPool is that I have a SqlAccessCommand interface. This is more or less an adapter pattern. On my test page, I am using RunUnsafe method to display an 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 am using try with resources which should close my connections after use no matter what. Much like try / catch / finally, just cleaner (IMO). so when my connection is open it works fine. so far 2 times I had to restart the server (since I don't know how to reopen the said connection in some other way), what am I missing? If you need additional puzzle pieces, please leave me a comment and I'll post what I can. Thank.
EDIT
just looked through the log files and there was an exception thrown this morning
com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by error: socket write error
As per the comments so far it has been assumed to time out. Does this show an error in this direction?
source to share
In the connection pool configuration, you need to add a few 1 options to determine if the connection is still valid.
The easiest way is to run a simple SQL 2 statement 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
- To view the available options: Tomcat JDBC Connection Pool
- Efficient SQL or validation query that will work on all (or most) databases
source to share
Your connections are almost certainly exhausted due to inactivity based on your com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error log message, you can read this Technet which says:
The connection was forcibly closed by a peer. This usually happens due to a connection loss on the remote socket due to a timeout or restart.
source to share
I would recommend using the Abandon configuration 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"
/>
source to share