tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Holly, Michael" <mho...@talisentech.com>
Subject RE: Connection pooling with two databases
Date Tue, 29 Jun 2004 17:05:10 GMT
Frans

I use dbcp to pool two different databases on different servers. A mysql
on localhost and an Oracle on a remote host.  I have never had a
problem.
I define my pools in my webapp.xml so they are specifically restricted
to the app that uses them.   I would examine your dataSourceName and
understand how this value affects which database the query gets given
to.  You might try making the dsnames differ for more than one charater.
The other thing that comes to mind is if you are running some sort of
query object/layer in between the pool and your object model. If you
reuse an object like this you might get crosstalk. The other question
that I would ask would be if the error happens within the same session
or across sessions.  If it happens across sessions then you are storing
a reference to a connection in an instance var in a servlet (a major No
No).   I know these are vague... Hope this points you toward a solution.



Regards

Michael 

-----Original Message-----
From: Frans Flippo [mailto:fflippo@caip.rutgers.edu] 
Sent: Tuesday, June 29, 2004 10:58 AM
To: tomcat-user@jakarta.apache.org
Subject: Connection pooling with two databases


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


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


Mime
View raw message