search

OpenLiberty / open-liberty

Open Liberty is a highly composable, fast to start, dynamic application server runtime environment
https://openliberty.io
Eclipse Public License 2.0
1.14k stars 587 forks source link

how to have two data source connection in open liberty #29195

Open Masood312 opened 1 month ago

Masood312 commented 1 month ago

I encountered an error during the implementation for two data sources:

DSRA0080E: An exception was received by the Data Store Adapter. See original exception message: Cannot call 'cleanup' on a ManagedConnection while it is still in a transaction.

This error occurs when two data source connections are available during server startup. However, when I changed one of the data source types from type="javax.sql.ConnectionPoolDataSource" to type="javax.sql.XADataSource", it started working fine.

I would like to know how to configure two data sources with the type javax.sql.ConnectionPoolDataSource.

jhanders34 commented 1 month ago

Please provide details about what your server.xml configuration looks like. Specifically looking for details of your <dataSource/> and <connectionManager/> settings if you have them. This server.xml from one of our tests gives an example of doing multiple data sources for instance.

From the liberty documentation page for jdbc, you will find the below content:

You can define multiple data sources and associate each with a different connection manager. 
However, you cannot associate multiple data sources with a single connection manager.

If you have your two data sources pointing to the same connection manager, maybe that is your problem? Hard to tell since you didn't provide your configuration or more details about the original exception message that is referenced in the error message you provided.

Masood312 commented 1 month ago

Hi, thanks for the response.

This is how my data source looks:

<server>
    <featureManager>
        <feature>jdbc-4.2</feature>
    </featureManager>
    <jdbcDriver id="postgres_driver">
        <library>
            <fileset dir="${shared.resource.dir}/lib/postgres" includes="*.jar"/>
        </library>
    </jdbcDriver>
    <dataSource isolationLevel="TRANSACTION_READ_COMMITTED" jdbcDriverRef="postgres_driver" jndiName="jdbc/DataSource1"
                type="javax.sql.ConnectionPoolDataSource" statementCacheSize="150">
        <connectionManager maxIdleTime="5m" maxPoolSize="55"/>
        <properties.postgresql databaseName="$dbname$" password="$dbpassword$" portNumber="$DB_SERVER_PORT$" serverName="$DB_HOST$"
                            user="$dbuser$" stringType="unspecified" defaultAutoCommit="false" ssl="false"/>
    </dataSource>
    <dataSource isolationLevel="TRANSACTION_READ_COMMITTED" jdbcDriverRef="postgres_driver" jndiName="jdbc/DataSource2"
                type="javax.sql.ConnectionPoolDataSource" statementCacheSize="150" maxPoolSize="55">
        <properties.postgresql databaseName="$dbname$" password="$dbpassword$" portNumber="$DB_SERVER_PORT$" serverName="$DB_HOST$"
                            user="$dbuser$" stringType="unspecified" defaultAutoCommit="false" ssl="false"/>

    </dataSource>
</server>
KyleAure commented 1 month ago

@Masood312 can you also provide the exception stack that accompanied DSRA0080E?

I suspect the issue might be defaultAutoCommit="false"

The Connection javadoc says:

By default, new connections are in auto-commit mode.

Which means we would except any connection from the PostgreSQL JDBC driver to be in auto-commit mode by default. If defaultAutoCommit="false" makes it so that the PostgreSQL JDBC driver is returning connections with auto-commit disabled by default then it would stand to reason that there would be places in Liberty where we fail to call con.commit() because we expect the JDBC driver to correctly implement to JDBC API.

Masood312 commented 1 month ago

this is the exception stack 

[7/25/24, 5:14:08:771 UTC] 000000af MCWrapper     E   J2CA0081E: Method cleanup failed while trying to execute method cleanup on ManagedConnection WSRdbManagedConnectionImpl@8ad4a22f from resource jdbc/DataSource1. Caught exception: com.ibm.ws.rsadapter.exceptions.DataStoreAdapterException: DSRA0080E: An exception was received by the Data Store Adapter. See original exception message: Cannot call 'cleanup' on a ManagedConnection while it is still in a transaction..
        at com.ibm.ws.rsadapter.impl.WSRdbManagedConnectionImpl.cleanupTransactions(WSRdbManagedConnectionImpl.java:3205)
        at com.ibm.ws.rsadapter.impl.WSRdbManagedConnectionImpl.cleanup(WSRdbManagedConnectionImpl.java:2737)
        at com.ibm.ejs.j2c.MCWrapper.cleanup(MCWrapper.java:1550)
        at com.ibm.ejs.j2c.FreePool.returnToFreePoolDelegated(FreePool.java:294)
        at com.ibm.ejs.j2c.FreePool.returnToFreePool(FreePool.java:281)
        at com.ibm.ejs.j2c.PoolManager.release(PoolManager.java:948)
        at com.ibm.ejs.j2c.MCWrapper.releaseToPoolManager(MCWrapper.java:2146)
        at com.ibm.ejs.j2c.MCWrapper.releaseToPoolManager(MCWrapper.java:2134)
        at com.ibm.ejs.j2c.LocalTransactionWrapper.afterCompletion(LocalTransactionWrapper.java:960)
        at com.ibm.tx.ltc.impl.LocalTranCoordImpl.driveSynchronization(LocalTranCoordImpl.java:1331)
        at com.ibm.tx.ltc.embeddable.impl.EmbeddableLocalTranCoordImpl.informSynchronizations(EmbeddableLocalTranCoordImpl.java:197)
        at com.ibm.tx.ltc.impl.LocalTranCoordImpl.complete(LocalTranCoordImpl.java:744)
        at com.ibm.tx.ltc.impl.LocalTranCoordImpl.complete(LocalTranCoordImpl.java:605)
        at com.ibm.tx.ltc.impl.LocalTranCurrentImpl.complete(LocalTranCurrentImpl.java:323)
        at com.ibm.tx.ltc.impl.LocalTranCurrentSet.complete(LocalTranCurrentSet.java:221)
        at com.ibm.ejs.container.CallbackContextHelper.complete(CallbackContextHelper.java:318)
        at com.ibm.ejs.container.StatelessBeanO.initialize(StatelessBeanO.java:258)
        at com.ibm.ejs.container.BeanOFactory.create(BeanOFactory.java:105)
        at com.ibm.ejs.container.EJSHome.createBeanO(EJSHome.java:932)
Masood312 commented 1 month ago

Does the error relate to this?

https://www.ibm.com/support/pages/apar/PK52881

KyleAure commented 1 month ago

@Masood312

Reply

PK52881 - is unrelated to your issue, this was for WebSphere Application Server, and was fixed

Recreation attempt

I did some testing and wasn't able to replicate your error. We have existing tests to ensure that we correctly intercept and set the default autoCommit based on the transaction context (local vs global transactions) https://github.com/OpenLiberty/open-liberty/blob/eb09137e8e7c42630f1859ce934a410f296782eb/dev/com.ibm.ws.jdbc_fat_postgresql/publish/servers/postgresql-test-server/server.xml#L80-L91

Investigation

However, when I changed one of the data source types from type="javax.sql.ConnectionPoolDataSource" to type="javax.sql.XADataSource", it started working fine.

That would be expected because the PostgreSQL XADataSource does not support to the defaultAutoCommit property.

Questions

  1. What are datasource1 and datasource2 used for are they both just application datasources or are they used for configuring JMS, JPA, or some other feature?
  2. I see com.ibm.ejs.container.CallbackContextHelper.complete in your trace. Are you using the EJB container feature?

To debug this fully I might need trace to be collected. If you are a customer you can open a case here: https://www.ibm.com/mysupport/s/faq