JBoss 4.0.2 Connection Pool Bug

[johnda]

I?m not sure whether we have a bug here, but the JBoss 4.0.2 connection pool does not work the way that one would expect it to. Consider the case where the same database server (e.g., PostgreSQL) has two or more logical databases. Now, two J2EE applications would want to connect to the same database server but separate databases using two different connection URLs as follows:

<connection-url>jdbc:postgresql://anyserver:5432/databaseone</connection-url>

and

<connection-url>jdbc:postgresql://anyserver:5432/databasetwo</connection-url>

Having inspected the logs, I noticed the connectors get correctly bound at load time. Depending on which one of the two connectors is bound first, however, the second one remains unnoticed/unused in that the application using the second connector is always connected to ?databaseone? (just like the application using the first connector)! The bug does not apparently occur if either application is running by itself ? in such cases the correct database is bound.

Unless this is a bug, there must be a simple way to bind each application to a particular database when the same database server is used. I?d hoped that the above connectors would do the trick but it does not appear to be the case. Do you have any suggestions as to how each application can use the connection pool to access the various databases it wants?

[weston.price]

Two different connection-urls in what? Is this an HA datasource? Do you have 2 seperate datasources, each bound into JNDI?

Please provide more information or better yet, read the post READ THIS FIRST at the top of this forum to learn how to appropriately post questions.

[johnda]

I have two separate datasources each bound into JNDI mapping eventually to the same database server. Each data source is referenced via JNDI by a separate application. As already mentioned, however, these applications require different databases on the same database server. At runtime, both applications are connected to the same database (the second one is ignored) even though the JNDI references point to different databases.

[weston.price]

Please post both *-ds.xml files, or the single *-ds.xml file with multiple datasources, depending upon your type of configuration.

[johnda]

I use separate *-ds.xml files, one for each datasource. Note that these files work perfectly when an application runs on its own. The problem occurs when two applications are loaded, each application referencing a different file through JNDI. In effect, the two applications end up using the first file's database (lda_inter).

1) First *-ds.xml
===========

<?xml version="1.0" encoding="UTF-8"?>

<local-tx-datasource>
<jndi-name>PostgreSQLDSsitaInternetSPE</jndi-name>
<!-- PCPIE Network server connection string -->
<connection-url>jdbc:postgresql://pcpie:5432/lda_inter</connection-url>
<!-- SITA Network server connection string
<connection-url>jdbc:postgresql://ilanga-dev:5432/lda_inter</connection-url>
-->
<!-- LDA AgriPortal Network server connection string
<connection-url>jdbc:postgresql://10.156.123.40:5432/lda_inter</connection-url>
-->
<driver-class>org.postgresql.Driver</driver-class>
<user-name>postgres</user-name>
passdeword
<check-valid-connection-sql>set search_path to lda_inter;</check-valid-connection-sql>
<new-connection-sql>set search_path to lda_inter;</new-connection-sql>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
</local-tx-datasource>


2nd *-ds.xml
=========

<?xml version="1.0" encoding="UTF-8"?>

<local-tx-datasource>
<jndi-name>PostgreSQLDSsitaIntranetSPE</jndi-name>
<!-- PCPIE Network server connection string -->
<connection-url>jdbc:postgresql://pcpie:5432/limpAgriIntra</connection-url>
<!-- SITA Network server connection string
<connection-url>jdbc:postgresql://ilanga-dev:5432/limpAgriIntra</connection-url>
-->
<!-- LDA AgriPortal Network server connection string
<connection-url>jdbc:postgresql://10.156.123.41:5432/limpAgriIntra</connection-url>
-->
<driver-class>org.postgresql.Driver</driver-class>
<user-name>postgres</user-name>
passdeword
<check-valid-connection-sql>set search_path to limpAgriIntra;</check-valid-connection-sql>
<new-connection-sql>set search_path to limpAgriIntra;</new-connection-sql>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
</local-tx-datasource>

[weston.price]

You are using the same JNDI name for each datasource. This is in effect causing you to bind only ONE datasource into JNDI and as a result, each application uses the same one.

<jndi-name>PostgreSQLDSsitaInternetSPE</jndi-name> <jndi-name>PostgreSQLDSsitaIntranetSPE</jndi-name>

Give each DS a unique JNDI name, and locate that DS by JNDI name. Even better, use a resource-ref to give yourself a level of indirection between your component and the datasource.


Example:

<jndi-name>DS1</jndi-name>
<jndi-name>DS2</jndi-name>

[johnda]

I've lost you now...the JNDI name tags are unique in that they differ by the characters 'Internet' and 'Intranet'. Are you perhaps referring to some other tags somewhere? Note also that I do use 'resource-ref' as the following extract from my jboss.xml file indicates:


<ejb-name>DownloadIntranetBean</ejb-name>
<jndi-name>ejb/za.co.sita.spe.downloads/IntranetDownloadBean/Remote</jndi-name>
<local-jndi-name>ejb/za.co.sita.spe.downloads/IntranetDownloadBeanLocal/Local</local-jndi-name>
<resource-ref>
<res-ref-name>jdbc/OracleDSsitaIntranetSPE</res-ref-name>
<jndi-name>java:/OracleDSsitaIntranetSPE</jndi-name>
</resource-ref>
<resource-ref>
<res-ref-name>jdbc/PostgreSQLDSsitaIntranetSPE</res-ref-name>
<jndi-name>java:/PostgreSQLDSsitaIntranetSPE</jndi-name>
</resource-ref>
<resource-ref>
<res-ref-name>jdbc/SQLServerDSsitaIntranetSPE</res-ref-name>
<jndi-name>java:/SQLServerDSsitaIntranetSPE</jndi-name>
</resource-ref>

[weston.price]

Ok, missed the Inter/Intra text...it's too early :-)

Hmmm....ok, do you see both DataSources bound into JNDI appropriately?
You can see this using JNDIView or via the twiddle console. I am also assuming that both *-ds.xml files are going into the appropriate place (deploy) directory.

How are your application's packages? EAR/WAR/EJB-JAR?

Have you tried writing a simple servlet or EJB that looks up both datasources directly?

[weston.price]

Note, I am also assuming that you are getting the connection to the database in the following manner: \

DataSource ds = (DataSource)initCtx.lookup("java:comp/env/refname");
ds.getConnection();

and you aren't passing username/password. If this is not correct, please let me know.

[johnda]

I've got the 'later' advantage that I'm South-East from you and it's nearly time for me to go home for the weekend (time here in Pretoria, South Africa is nearly 3:15 p.m.) . Everything you've mentioned in your last message(s) is in order, but I do like the idea of writing some code to check up on the connections and possibly assign the required database. Do you have an example of code that can access connection infomation so that I can write some kind of control logic that can dynamically assign connections for database requests?

Another related question that's been dogging me is the visibility of these questions to specific EJBs used by applications. If, for example, two applications seek the same EJBs does each respective set of EJBs work exclusively within the application's space or are the same EJBs shared?

[johnda]

It's been a while since we communicated on this topic and the silence on replies confirms that this is a genuine bug with JBoss connection pooling. In the meantime, I wrote persistence management code to take care of the allocation of connections dynamically whilst still using the EJB layer and this solution is working wonderfully. I suppose this matter can now be closed and I thank you for your assistance.

[weston.price]

As for it being a 'bug' in JBoss, I was never able to reproduce what you are talking about. However, it has been awhile since I have looked at it, so I may go back and give it another shot.