Bug 1195079
| Summary: | [GSS](6.4.z) "org.postgresql.util.PSQLException: Cannot change transaction isolation" at calling Connection#setTransactionIsolation() when enabling connection validation in EAP 6 | ||
|---|---|---|---|
| Product: | [JBoss] JBoss Enterprise Application Platform 6 | Reporter: | Masafumi Miura <mmiura> |
| Component: | JCA | Assignee: | Tomas Hofman <thofman> |
| Status: | CLOSED CURRENTRELEASE | QA Contact: | Jiří Bílek <jbilek> |
| Severity: | unspecified | Docs Contact: | |
| Priority: | unspecified | ||
| Version: | 6.1.0, 6.3.3 | CC: | bbaranow, bmaxwell, cdewolf, jawilson, jbilek, jolee, mmiura, msochure, ochaloup, rnetuka, rstancel, thofman |
| Target Milestone: | CR1 | Keywords: | Reopened |
| Target Release: | EAP 6.4.16 | ||
| Hardware: | Unspecified | ||
| OS: | Unspecified | ||
| Whiteboard: | |||
| Fixed In Version: | Doc Type: | Bug Fix | |
| Doc Text: | Story Points: | --- | |
| Clone Of: | Environment: | ||
| Last Closed: | 2017-06-22 09:23:07 UTC | Type: | Bug |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
| Embargoed: | |||
| Bug Depends On: | |||
| Bug Blocks: | 1434495, 1440733 | ||
|
Description
Masafumi Miura
2015-02-22 20:48:33 UTC
Works for me with postgresql-9.4-1200.jdbc41.jar Did you test with the actual same JSP (test.jsp) which I attached? I guess you did some modification... I tested with the following latest jdbc drivers, which are available from <https://jdbc.postgresql.org/download.html>, and I could reproduce the issue with postgresql-9.4-1200.jdbc41.jar and other jdbc drivers in my environment (EAP 6.3.3 + PostgreSQL 9.3.6 database server on Fedora 20): - postgresql-9.2-1004.jdbc4.jar - postgresql-9.2-1004.jdbc41.jar - postgresql-9.3-1103.jdbc4.jar - postgresql-9.3-1103.jdbc41.jar - postgresql-9.4-1200.jdbc4.jar - postgresql-9.4-1200.jdbc41.jar By the way, please note that https://access.redhat.com/articles/111663 says EAP 6 is actually tested with PostgreSQL 9.2 and JDBC4 Postgresql Driver. (I think this issue is not related to the version of PostgreSQL, though.) By the way, I found a workaround for this issue by coincidence.
I found that this issue did not occur when java.sql.Connection#getMetaData() was called (like the following) before calling db.setAutoCommit(false) at the line 25 in the attached test.jsp.
DatabaseMetaData dbmd = db.getMetaData();
System.out.println("===== Database info =====");
System.out.println("DatabaseProductName: " + dbmd.getDatabaseProductName() );
System.out.println("DatabaseProductVersion: " + dbmd.getDatabaseProductVersion() );
System.out.println("DatabaseMajorVersion: " + dbmd.getDatabaseMajorVersion() );
System.out.println("DatabaseMinorVersion: " + dbmd.getDatabaseMinorVersion() );
System.out.println("===== Driver info =====");
System.out.println("DriverName: " + dbmd.getDriverName() );
System.out.println("DriverVersion: " + dbmd.getDriverVersion() );
System.out.println("DriverMajorVersion: " + dbmd.getDriverMajorVersion() );
System.out.println("DriverMinorVersion: " + dbmd.getDriverMinorVersion() );
I just intended to print the version of Database and JDBC driver but it works like a charm.
This java.sql.Connection#getMetaData() is internally org.jboss.jcaadapters.jdbc.WrappedConnection() in ironjacamar and it calls "checkTransaction()". I thinks this method-call propagates auto-commit mode to underlyingAutoCommit then it ends JDBC tx as auto-commit mode is true at that time, so I think this is why it works like a charm.
Reproduced with postgresql jdbc driver 9.3.6 and ironjacamar 1.0.31.Final. The problem is that jdbc connection is left in autocommit=false mode from the previous request, in spite of application code setting autocommit back to true on WrappedConnection. It should be set back to true during cleanup phase anyway, but this is never propagated to jdbc connection, since autocommit is propagated only during prepareStatement() call. Because of that, when the application server is verifying jdbc connection for current request (by executing "select 1"), new transaction is started and calling setTransactionIsolation() later fails. Solution would be for JCA to propagate autocommit state to jdbc connection during cleanup phase. Created discussion here https://developer.jboss.org/message/920378 We've found another workaround which does not need code changes in their application. Setting "select 1;commit;" instead of "select 1" to <check-valid-connection-sql> does work. For example: <check-valid-connection-sql>select 1; commit;</check-valid-connection-sql> Upstream Issue: https://issues.jboss.org/browse/JBEAP-8789 Upstream PR: https://github.com/ironjacamar/ironjacamar/pull/604 Regression spotted, see https://issues.jboss.org/browse/JBEAP-9730?focusedCommentId=13382943&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-13382943 New PR: https://github.com/ironjacamar/ironjacamar/pull/628 Upstream Issue: https://issues.jboss.org/browse/JBEAP-8789 Verified with EAP 6.4.16.CP.CR1 Released on June 20 2017 as part of the EAP 6.4.16 maintenance release. |