Connection pooling with two databases

[Frans Flippo <ff...@caip.rutgers.edu>]

Hi,

We're using Tomcat 4.1.30 and PostgreSQL 7.3.4 to run a high-volume
website.

We connect to two different databases using database connection
pooling and occasionally a mixup seems to occur where a query intended for
database A is executed on database B, or vice versa. Postgres says
"Relation 'xxx' does not exist" and the database log shows a query being
executed on a Postgres instance that previously connected to the 'wrong'
database. It looks like the JNDI lookup is returning the wrong DataSource
object.
The strange thing is most of the time it works fine. We've run for 3 weeks
without a problem; then just yesterday it happened twice in a row. A
Tomcat restart 'fixes' it, but of course we'd rather avoid that.


Has anybody used _two_ databases in a single web application using
connection pooling? Is it something that should be supported? Does this
seem like a problem in Tomcat, the commons dbcp/pooling code, or the
Postgres jdbc driver?

Thanks in advance for anything that could point us in the right direction.

Regards,
Frans


Here's the code we use to lookup the DataSource and obtain the Connection.
The code is in a method that gets executed twice, with a different
dataSourceName parameter each time (db1 and db2).

-== ==-
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Connection;

. . .
	DataSource dataSource;
	Connection connection;

	Context initialContext = new InitialContext();
	Context envContext = (Context)initialContext.lookup("java:/comp/env");
	dataSource = (DataSource)envContext.lookup(dataSourceName);
	connection = dataSource.getConnection();

-== ==-


Here's the Resources we define in the DefaultContext section of
server.xml:

-== ==-
<Resource name="jdbc/db1" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/db1">
	<parameter>
		<name>factory</name>
		<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
	</parameter>
	<parameter>
		<name>driverClassName</name>
		<value>org.postgresql.Driver</value>
	</parameter>
	<parameter>
		<name>url</name>
		<value>jdbc:postgresql://127.0.0.1:5432/db1</value>
	</parameter>
	<parameter>
		<name>username</name>
		<value>postgres</value>
	</parameter>
	<parameter>
		<name>password</name>
		<value></value>
	</parameter>
	<parameter>
		<name>maxActive</name>
		<value>20</value>
	</parameter>
	<parameter>
		<name>maxIdle</name>
		<value>10</value>
	</parameter>
	<parameter>
		<name>maxWait</name>
		<value>-1</value>
	</parameter>
	<parameter>
		<name>removeAbandoned</name>
		<value>true</value>
	</parameter>
	<parameter>
		<name>removeAbandonedTimeout</name>
		<value>60</value>
	</parameter>
	<parameter>
		<name>logAbandoned</name>
		<value>true</value>
	</parameter>
</ResourceParams>

<Resource name="jdbc/db2" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/db2">
	<parameter>
		<name>factory</name>
		<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
	</parameter>
	<parameter>
		<name>driverClassName</name>
		<value>org.postgresql.Driver</value>
	</parameter>
	<parameter>
		<name>url</name>
		<value>jdbc:postgresql://127.0.0.1:5432/db2</value>
	</parameter>
	<parameter>
		<name>username</name>
		<value>postgres</value>
	</parameter>
	<parameter>
		<name>password</name>
		<value></value>
	</parameter>
	<parameter>
		<name>maxActive</name>
		<value>30</value>
	</parameter>
	<parameter>
		<name>maxIdle</name>
		<value>10</value>
	</parameter>
	<parameter>
		<name>maxWait</name>
		<value>-1</value>
	</parameter>
	<parameter>
		<name>removeAbandoned</name>
		<value>true</value>
	</parameter>
	<parameter>
		<name>removeAbandonedTimeout</name>
		<value>60</value>
	</parameter>
	<parameter>
		<name>logAbandoned</name>
		<value>true</value>
	</parameter>
</ResourceParams>

-== ==-



---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org