Problem with multiple database connection

Technical Questions
1

Hi everyone,
I’ve created two datasource to connect two difference databases, everything is fine except that i always get warning with content:
WARN overy-thread b.tm.r.j.JdbcPooledConnection close connection with usage count > 0, a JdbcPooledConnection from datasource transactional_second_DS in state ACCESSIBLE with usage count 1 wrapping a JDBC LrcXAConnection on a JDBC LrcConnectionJavaProxy on ConnectionID:164 ClientConnectionId: 063a3df8-3e56-41bf-96f4-69810fd8b077
The default datasource is configured:

<datasource group-name="transactional" database-conf-name="${entity_ds_db_conf}" schema-name="${entity_ds_schema}"
                runtime-add-missing="${entity_add_missing_runtime}" startup-add-missing="${entity_add_missing_startup}">
            <!-- by default no inline-jdbc or jndi-jdbc elements, use default from database conf -->
</datasource>

And the second datasource is:

<datasource group-name="transactional_second" database-conf-name="${entity_ds_brv_db_conf}" schema-name="${entity_ds_brv_schema}"
                    runtime-add-missing="false" startup-add-missing="true" disabled="${entity_ds_brv_disabled}">
            <inline-jdbc jdbc-uri="jdbc:sqlserver://${entity_ds_brv_host}:${entity_ds_brv_port?:'1433'};databaseName=${entity_ds_brv_database};SelectMethod=Cursor"
                         jdbc-username="${entity_ds_brv_user}" jdbc-password="${entity_ds_brv_password}"/>
</datasource>

If the second datasource is disabled, the warning message will go away. Is there any error with my config?

1 Like
2

In my experience, if I get a warning and everything is functioning properly, then I don’t worry about it too much.

Is there a reason that you have two databases setup with Moqui?

3

This might be obvious but, did you define all the variables that you’re using in the datasource declaration?

1 Like
4

Yes taher, all variables are set like this

    <default-property name="entity_ds_brv_disabled" value="false"/>
    <default-property name="entity_ds_brv_db_conf" value="mssql"/>
    <default-property name="entity_ds_brv_schema" value="dbo"/>
    <default-property name="entity_ds_brv_host" value="localhost"/>
    <default-property name="entity_ds_brv_port" value="14331"/>
    <default-property name="entity_ds_brv_database" value="aphbrv"/>
    <default-property name="entity_ds_brv_user" value="sa"/>
    <default-property name="entity_ds_brv_password" value="sa@123"/>
1 Like
5

Hi Michael, the reason for this is that i have to exchange data with another system through an intermediate database, my database will push (or fetch) data to (or from) this intermediate database.

1 Like
6

For multiple databases and full two phase commit support you have to use the XADataSource, which is done with the xa-properties element. The inline-jdbc element with the jdbc-uri and other attributes instead of an xa-properties child element will not work because that is for the DataSource driver.

Why the different configurations for DataSource and XADataSource? Because there is no real configuration standard for XADataSource and it requires JDBC driver specific properties for configuration, very different from the concept of a JDBC URI.

2 Likes
7

Hi Jonesde,

You mean not wok or not work correctly, because when I created testing services for CRUD in two database, it seem work fine.
Additional, the reason I can’t use XADataSource because the database i’m using is SQLServer and it require to enable MS DTC for XA transactions (follow by link) in the server that database installed, and I have no right to do this

1 Like
8

Sure, it ‘works’, but not fully XA (transactional) across multiple databases. It is okay for a single database, but for multiple you’re running into limits of what a transaction manager (default is Bitronix in Moqui) can do with non-XA JDBC drivers, especially for the two-phase commit I mentioned (necessary for transactional commit across multiple resources, including databases, transactional messaging, etc).

If you can’t get a JDBC driver with an XADataSource you’re going to have problems with multiple databases, that’s just the nature of those lower level standard Java APIs.

There might be ways around this, and perhaps another transaction manager like Atomikos could do a better job than Bitronix. The thing with Atomikos is that while they have an open source distribution I would NOT recommend using it, buy the commercial version or stick with Bitronix. It may be better now as I haven’t tried Atomikos in a few years, but the reason I stopped using or supporting it (see the moqui-atomikos git repo) is that the open source version is super buggy, and seemingly intentionally in order to encourage the purchase of the commercial version which gets bug fixes right away where the open source one gets them… eventually.

I don’t know what other constraints you have, but MS SQLServer is not a database I’d recommend. Some have used it with Moqui, but I never have and it’s not something I or others without commercial access would even be able to test or support. The best DB for Moqui these days is Postgres, and then coming in second (for fewer features, less friendly licensing) is MySQL.

3 Likes