JBoss EAP datasource migration to AWS JDBC Wrapper #217

pexa-araza · 2022-10-10T23:46:02Z

pexa-araza
Oct 10, 2022

In our JBoss application, we currently have got two data sources defined

1. Writer

<connection-url>jdbc:postgresql://**AWS Cluster Endpoint**:5432,**AWS RO Cluster Endpoint**:5432/dbname</connection-url>
<connection-property name="ApplicationName">testWriterApplication</connection-property>
<connection-property name="targetServerType">primary</connection-property>

Reader

<connection-url>jdbc:postgresql://**AWS RO Cluster Endpoint**:5432/dbname</connection-url>
<connection-property name="ApplicationName">testReaderApplication</connection-property>
<connection-property name="targetServerType">secondary</connection-property>
<connection-property name="readOnly">true</connection-property>

How would the above two configurations be translated to AWS Advanced JDBC Wrapper along with the failover plugin?

Thank You

sergiyvamz · 2022-10-11T18:44:03Z

sergiyvamz
Oct 11, 2022
Maintainer

Hello @pexa-araza

Thank you for reaching out! I'd suggest to use the following configurations for AWS JDBC Driver.

Writer

<connection-url>jdbc:aws-wrapper:postgresql://**AWS Cluster Endpoint**:5432/dbname?wrapperPlugins=failover,efm</connection-url>
<connection-property name="ApplicationName">testWriterApplication</connection-property>
<connection-property name="targetServerType">primary</connection-property>

Reader

<connection-url>jdbc:aws-wrapper:postgresql://**AWS RO Cluster Endpoint**:5432/dbname?wrapperPlugins=failover,efm</connection-url>
<connection-property name="ApplicationName">testReaderApplication</connection-property>
<connection-property name="targetServerType">secondary</connection-property>
<connection-property name="readOnly">true</connection-property>

Depending on your application workflows and usual SQL statements you may consider to replace efm plugin with a simple network timeout. https://github.com/awslabs/aws-advanced-jdbc-wrapper/blob/main/docs/using-the-jdbc-driver/using-plugins/UsingTheHostMonitoringPlugin.md#the-benefits-of-enhanced-failure-monitoring

Thank you!

0 replies

pexa-araza · 2022-10-17T22:53:28Z

pexa-araza
Oct 17, 2022
Author

Thank you.

Now after updating everything and running the application, the log is full of the following error message:

2022-10-14 15:05:56,285 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (pexa-threads task-4004) Transaction resolution unknown. Please re-configure the session state if required and try restarting the transaction.

The application currently uses dedicated Aurora Writer and Replica with their own cluster endpoints. Previously without postgres jdbc driver, we did not see this message/error. Please note that the application is configured to use Spring AbstractRoutingDataSource and updates the data source at runtime, based on Spring's read-only transaction annotation.

Although, the application is working fine but getting the above error message is concerning and is a blocker for production implmentation.

Could you please suggest what need to be done to fix the above?

Thanks

0 replies

davecramer · 2022-10-18T11:50:09Z

davecramer
Oct 18, 2022
Maintainer

@pexa-araza
Can you tell us how you defined the datasources ?

The error messages are telling you that the driver detected a failover.

0 replies

karenc-bq · 2022-10-18T18:04:09Z

karenc-bq
Oct 18, 2022
Maintainer

Hi @pexa-araza, in addition to @davecramer's comment, would you also be able to provide us with the full logs? Thank you.

0 replies

pexa-araza · 2022-10-18T23:48:12Z

pexa-araza
Oct 18, 2022
Author

Interestingly there is no database failover as I can't see anything on AWS RDS Console regarding failover. or driver detects a different kind of failover?

JBoss EAP Data Sources:

<datasource jndi-name="java:jboss/datasources/test" pool-name="test" statistics-enabled="true"> <connection-url>jdbc:aws-wrapper:postgresql://Writer Cluser Endpoint:5432/test</connection-url> <connection-property name="ApplicationName">test-writer</connection-property> <connection-property name="targetServerType">primary</connection-property> <connection-property name="wrapperPlugins">failover,efm</connection-property> <security> <user-name>test</user-name> <credential-reference store="test" alias="test"/> </security> <driver>PostgresqlJDBCDriver</driver> <pool> <min-pool-size>15</min-pool-size> <max-pool-size>40</max-pool-size> <prefill>true</prefill> <use-strict-min>true</use-strict-min> <flush-strategy>AllIdleConnections</flush-strategy> </pool> <validation> <validate-on-match>false</validate-on-match> <background-validation>true</background-validation> <background-validation-millis>60000</background-validation-millis> <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/> <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/> </validation> <timeout> <blocking-timeout-millis>10000</blocking-timeout-millis> <<idle-timeout-minutes>2</idle-timeout-minutes> </timeout> <statement> <prepared-statement-cache-size>700</prepared-statement-cache-size> <share-prepared-statements>true</share-prepared-statements> </statement> </datasource> <datasource jndi-name="java:jboss/datasources/test_replica" pool-name="test_replica" statistics-enabled="true"> <connection-url>jdbc:aws-wrapper:postgresql://RO Cluster End Point:5432/test</connection-url> <connection-property name="ApplicationName">test-replica-reader</connection-property> <connection-property name="targetServerType">secondary</connection-property> <connection-property name="wrapperPlugins">failover,efm</connection-property> <connection-property name="readOnly">true</connection-property> <security> <user-name>test</user-name> <credential-reference store="test" alias="test"/> </security> <driver>PostgresqlJDBCDriver</driver> <pool> <min-pool-size>15</min-pool-size> <max-pool-size>40</max-pool-size> <prefill>true</prefill> <use-strict-min>true</use-strict-min> <flush-strategy>AllIdleConnections</flush-strategy> </pool> <validation> <validate-on-match>false</validate-on-match> <background-validation>true</background-validation> <background-validation-millis>60000</background-validation-millis> <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/> <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/> </validation> <timeout> <blocking-timeout-millis>10000</blocking-timeout-millis> <<idle-timeout-minutes>2</idle-timeout-minutes> </timeout> <statement> <prepared-statement-cache-size>700</prepared-statement-cache-size> <share-prepared-statements>true</share-prepared-statements> </statement> </datasource>

Logs:

Caused by: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet at deployment.pexa_web.war//org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:112) at deployment.pexa_web.war//org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at deployment.pexa_web.war//org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) at deployment.pexa_web.war//org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) at deployment.pexa_web.war//org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67) at deployment.pexa_web.war//org.hibernate.loader.Loader.getResultSet(Loader.java:2303) at deployment.pexa_web.war//org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2056) at deployment.pexa_web.war//org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2018) at deployment.pexa_web.war//org.hibernate.loader.Loader.doQuery(Loader.java:948) at deployment.pexa_web.war//org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349) at deployment.pexa_web.war//org.hibernate.loader.Loader.doList(Loader.java:2849) at deployment.pexa_web.war//org.hibernate.loader.Loader.doList(Loader.java:2831) at deployment.pexa_web.war//org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2663) at deployment.pexa_web.war//org.hibernate.loader.Loader.list(Loader.java:2658) at deployment.pexa_web.war//org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506) at deployment.pexa_web.war//org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400) at deployment.pexa_web.war//org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219) at deployment.pexa_web.war//org.hibernate.internal.SessionImpl.list(SessionImpl.java:1414) at deployment.pexa_web.war//org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1625) at deployment.pexa_web.war//org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1593) ... 244 more Caused by: software.amazon.jdbc.plugin.failover.TransactionStateUnknownSQLException: Transaction resolution unknown. Please re-configure session state if required and try restarting the transaction. at [email protected]//software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.failover(FailoverConnectionPlugin.java:649) at [email protected]//software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.pickNewConnection(FailoverConnectionPlugin.java:774) at [email protected]//software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.dealWithOriginalException(FailoverConnectionPlugin.java:590) at [email protected]//software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.execute(FailoverConnectionPlugin.java:196) at [email protected]//software.amazon.jdbc.ConnectionPluginManager.lambda$execute$3(ConnectionPluginManager.java:298) at [email protected]//software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$2(ConnectionPluginManager.java:250) at [email protected]//software.amazon.jdbc.ConnectionPluginManager.executeWithSubscribedPlugins(ConnectionPluginManager.java:228) at [email protected]//software.amazon.jdbc.ConnectionPluginManager.execute(ConnectionPluginManager.java:295) at [email protected]//software.amazon.jdbc.util.WrapperUtils.executeWithPlugins(WrapperUtils.java:224) at [email protected]//software.amazon.jdbc.wrapper.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:224) at [email protected]//org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:113) at [email protected]//org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504) at deployment.pexa_web.war//org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)

0 replies

davecramer · 2022-10-19T14:34:33Z

davecramer
Oct 19, 2022
Maintainer

In the short term I would recommend removing the efm plugin from the data source.
Additionally some code changes are required to handle the exceptions that are thrown. Specifically have a look at the code in https://github.com/awslabs/aws-advanced-jdbc-wrapper/blob/main/examples/HibernateExample/src/main/java/software/amazon/utility/AuroraPostgreSQLDialect.java

We need to catch the exceptions the driver throws when failover occurs and transform them into new exceptions.

Your code will have to deal with them appropriately when they are thrown.

0 replies

karenc-bq · 2022-10-19T18:59:03Z

karenc-bq
Oct 19, 2022
Maintainer

Based on the logs it seems the driver has detected a network-related SQLException: software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.dealWithOriginalException(FailoverConnectionPlugin.java:590
, and has successfully failover to a new node. The driver does not carry over any previous session state configuration after failover, so the driver throws the Transaction Resolution Unknown Exception that you have been seeing to indicate a failover has occurred during a transaction.

It is hard to tell what caused the network-related SQLException that triggered the failover process in the first place from the logs.
I noticed your blocking-timeout-millis is set to 10000 in your datasource configurations The driver needs some additional time to set up the failover plugins when establishing a new connection, would you be able to increase this timeout value to 30s and see if you are still experiencing the same exception?

0 replies

rishabhdixit2689 · 2022-10-20T12:24:36Z

rishabhdixit2689
Oct 20, 2022

@pexa-araza How are you adding aws jdbc wrapper jar to the datasource defination in standalone-openshift.xml?

0 replies

pexa-araza · 2022-10-20T23:13:31Z

pexa-araza
Oct 20, 2022
Author

@rishabhdixit2689

See above for the data source defined in JBoss EAP. along with the datasources, you'll need to update the datarouces's driver as

                <driver name="postgresql" module="org.postgresql">
                    <driver-class>software.amazon.jdbc.Driver</driver-class>
                </driver>

also see attached screenshot of the modules

0 replies

SamuelChirra · 2022-10-21T13:57:38Z

SamuelChirra
Oct 21, 2022

Hi,

In our Spring boot application which is running on Java 1.8, we are getting the exception ->java.sql.SQLException: No suitable driver
Am I missing anything here?

Following is the config:

spring.datasource.url=jdbc:aws-wrapper:postgresql://${pg-hostname}:${pg-port}/${pg-sid}
spring.datasource.driver-class-name=software.amazon.jdbc.Driver
spring.datasource.driverClassName=software.amazon.jdbc.Driver

Added the dependency in pom.xml:

<dependency>
    <groupId>software.amazon.jdbc</groupId>
    <artifactId>aws-advanced-jdbc-wrapper</artifactId>
    <version>1.0.0</version>
</dependency>

Logs

 ERROR org.apache.tomcat.jdbc.pool.ConnectionPool - Unable to create initial connections of pool.
java.sql.SQLException: No suitable driver
	at java.sql.DriverManager.getDriver(DriverManager.java:315)
	at software.amazon.jdbc.Driver.connect(Driver.java:81)
	at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:319)
	at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:212)
	at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:736)
	at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:668)
	at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:483)
	at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:154)
	at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118)
	at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107)
	at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:341)

1 reply

karenc-bq Oct 21, 2022
Maintainer

Hi @SamuelChirra, sorry I am unable to reproduce the error locally.
Here are my settings:
pom.xml

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
</dependency>
<dependency>
  <groupId>software.amazon.jdbc</groupId>
  <artifactId>aws-advanced-jdbc-wrapper</artifactId>
  <version>1.0.0</version>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

application.properties

spring.datasource.url=jdbc:aws-wrapper:postgresql://cluster-endpoint:5432/mvp?wrapperPlugins=failover,efm
spring.datasource.driver-class-name=software.amazon.jdbc.Driver
spring.datasource.username=
spring.datasource.password=
logging.level.org.postgresql=DEBUG
logging.level.software.amazon.jdbc=TRACE

davecramer · 2022-10-21T15:36:09Z

davecramer
Oct 21, 2022
Maintainer

Hi @SamuelChirra Yes, you need to also add the PostgreSQL driver to the dependencies.

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.5.0</version>
</dependency>

3 replies

SamuelChirra Oct 21, 2022

Hi @davecramer, this is the existing application that has PostgreSQL driver. So the exception is after adding the PostgreSQL and aws-advanced-jdbc-wrapper dependencies. Let me know if any other configuration is missing.

Thanks in advance.

davecramer Oct 21, 2022
Maintainer

Interesting,

can you share the entire dependency file ?

SamuelChirra Oct 27, 2022

Hi,

Attached my entire pom.xml, looks like application is working in local but when we deploy the same to Development environment we are getting the below issue. Let me know if any configuration to be added.

ERROR org.apache.tomcat.jdbc.pool.ConnectionPool - Unable to create initial connections of pool. java.sql.SQLException: No suitable driver at java.sql.DriverManager.getDriver(DriverManager.java:315) at software.amazon.jdbc.Driver.connect(Driver.java:81) at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:319) at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:212)

pom.xml - dependency file.txt

Also attaching the DBConfig class - DBConfig.txt

pexa-araza · 2022-11-22T00:06:39Z

pexa-araza
Nov 22, 2022
Author

Sorry, it took us long to conduct the testing with AWS JDBC Wrapper after the above suggestion. We have now concluded the tests. Please find attached the test results for Failover testing. Unfortunately, the test failed compared to our previous test without the AWS JDBC driver. The application didn't recover from failover even after 30 minutes and the log was full of Transaction Resolution Unknown errors.

AWS JDBC Driver.docx

2 replies

karenc-bq Nov 22, 2022
Maintainer

@pexa-araza Thank you for posting the results and configuration file.
I have some follow up questions:

How are transactions being handled in your workflow?
Are you using JTA transactions by any chance?
Are you able to provide an example of your SQL queries and how long do they usually run for?

hsuamz Dec 13, 2022
Maintainer

@pexa-araza Thank you for posting the results and configuration file. I have some follow up questions:

How are transactions being handled in your workflow?

Are you using JTA transactions by any chance?

Are you able to provide an example of your SQL queries and how long do they usually run for?

@pexa-araza , following up on this. Are you able to provide some answers to these questions? We are having some challenges in reproducing this.

Thanks,

Matthew

davecramer · 2022-11-22T00:16:57Z

davecramer
Nov 22, 2022
Maintainer

@pexa-araza can you share the PostgreSQLValidConnectionChecker class ?

2 replies

pexa-araza Nov 22, 2022
Author

This class is JBoss Postgres specific but here is the GitHub location of this class

https://github.com/oloake/IronJacamar/blob/master/adapters/src/main/java/org/jboss/jca/adapters/jdbc/extensions/postgres/PostgreSQLValidConnectionChecker.java

fisco-unimatic Nov 24, 2023

This tries to execute an empty sql query:
stmt.execute("");
SqlMethodAnalyzer#parseMultiStatementQueries(String) returns an empty list.
SqlMethodAnalyzer#getFirstSqlStatement(String) throws an IndexOutOfBoundsException when it tries to get the first statement in that list.
I don't know whether this is related to the error you are seeing. I'm seeing it my project, perhaps because I have <validate-on-match>true</validate-on-match>, whereas you have false.
Edit: I still get it when I set validate-on-match to false.

davecramer · 2022-11-22T00:22:52Z

davecramer
Nov 22, 2022
Maintainer

thx, I actually meant PostgreSQLExceptionSorter but I found that one too.

Thanks for the information.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

JBoss EAP datasource migration to AWS JDBC Wrapper #217

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 14 comments 8 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

Select a reply

JBoss EAP datasource migration to AWS JDBC Wrapper #217

pexa-araza Oct 10, 2022

Replies: 14 comments · 8 replies

sergiyvamz Oct 11, 2022 Maintainer

pexa-araza Oct 17, 2022 Author

davecramer Oct 18, 2022 Maintainer

karenc-bq Oct 18, 2022 Maintainer

pexa-araza Oct 18, 2022 Author

davecramer Oct 19, 2022 Maintainer

karenc-bq Oct 19, 2022 Maintainer

rishabhdixit2689 Oct 20, 2022

pexa-araza Oct 20, 2022 Author

SamuelChirra Oct 21, 2022

karenc-bq Oct 21, 2022 Maintainer

davecramer Oct 21, 2022 Maintainer

SamuelChirra Oct 21, 2022

davecramer Oct 21, 2022 Maintainer

SamuelChirra Oct 27, 2022

pexa-araza Nov 22, 2022 Author

karenc-bq Nov 22, 2022 Maintainer

hsuamz Dec 13, 2022 Maintainer

davecramer Nov 22, 2022 Maintainer

pexa-araza Nov 22, 2022 Author

fisco-unimatic Nov 24, 2023

davecramer Nov 22, 2022 Maintainer

pexa-araza
Oct 10, 2022

Replies: 14 comments 8 replies

sergiyvamz
Oct 11, 2022
Maintainer

pexa-araza
Oct 17, 2022
Author

davecramer
Oct 18, 2022
Maintainer

karenc-bq
Oct 18, 2022
Maintainer

pexa-araza
Oct 18, 2022
Author

davecramer
Oct 19, 2022
Maintainer

karenc-bq
Oct 19, 2022
Maintainer

rishabhdixit2689
Oct 20, 2022

pexa-araza
Oct 20, 2022
Author

SamuelChirra
Oct 21, 2022

karenc-bq Oct 21, 2022
Maintainer

davecramer
Oct 21, 2022
Maintainer

davecramer Oct 21, 2022
Maintainer

pexa-araza
Nov 22, 2022
Author

karenc-bq Nov 22, 2022
Maintainer

hsuamz Dec 13, 2022
Maintainer

davecramer
Nov 22, 2022
Maintainer

pexa-araza Nov 22, 2022
Author

davecramer
Nov 22, 2022
Maintainer