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 namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter parameter namedriverClassName/name valueorg.postgresql.Driver/value /parameter parameter nameurl/name valuejdbc:postgresql://127.0.0.1:5432/db1/value /parameter parameter nameusername/name valuepostgres/value /parameter parameter namepassword/name value/value /parameter parameter namemaxActive/name value20/value /parameter parameter namemaxIdle/name value10/value /parameter parameter namemaxWait/name value-1/value /parameter parameter nameremoveAbandoned/name valuetrue/value /parameter parameter nameremoveAbandonedTimeout/name value60/value /parameter parameter namelogAbandoned/name valuetrue/value /parameter /ResourceParams Resource name=jdbc/db2 auth=Container type=javax.sql.DataSource/ ResourceParams name=jdbc/db2 parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter parameter namedriverClassName/name valueorg.postgresql.Driver/value /parameter parameter nameurl/name valuejdbc:postgresql://127.0.0.1:5432/db2/value /parameter parameter nameusername/name valuepostgres/value /parameter parameter namepassword/name value/value /parameter parameter namemaxActive/name value30/value /parameter parameter namemaxIdle/name value10/value /parameter parameter namemaxWait/name value-1/value /parameter parameter nameremoveAbandoned/name valuetrue/value /parameter parameter nameremoveAbandonedTimeout/name value60/value /parameter parameter namelogAbandoned/name valuetrue/value /parameter /ResourceParams -== ==- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection pooling with two databases
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:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 10:58 AM To: [EMAIL PROTECTED] 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 namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter parameter namedriverClassName/name valueorg.postgresql.Driver/value /parameter parameter nameurl/name valuejdbc:postgresql://127.0.0.1:5432/db1/value /parameter parameter nameusername/name valuepostgres/value /parameter parameter namepassword/name value/value /parameter parameter namemaxActive/name value20/value /parameter parameter namemaxIdle/name value10/value /parameter parameter namemaxWait/name value-1/value /parameter parameter nameremoveAbandoned/name valuetrue/value /parameter parameter nameremoveAbandonedTimeout/name value60/value /parameter parameter namelogAbandoned/name valuetrue/value /parameter /ResourceParams Resource name=jdbc/db2 auth=Container type=javax.sql.DataSource/ ResourceParams name=jdbc/db2 parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter parameter namedriverClassName/name valueorg.postgresql.Driver/value /parameter parameter nameurl/name valuejdbc:postgresql://127.0.0.1:5432/db2/value /parameter parameter nameusername/name valuepostgres/value /parameter parameter namepassword/name value/value /parameter parameter
