Posted: 28 Oct 2015 17:51 EDT Last activity: 19 Apr 2017 9:31 EDT
I am having an error when using an RDB-List or Obj-Browse method. Every other time I run the activity to access this table I get the following error:
(Pega 7.1.7) I am having an error when using an RDB-List or Obj-Browse method. Every other time I run the activity to access this table I get the following error:
com.pega.pegarules.pub.database.DatabaseException: Database-General Problem encountered when getting connection for database fdmsub 17273 99999 Could not commit with auto-commit set on From: (HB1FCCC0406A68E36E65C47464B3E57EA:18.104.22.168)
Caused by SQL Problems.
Problem #1, SQLState 99999, Error code 17273: java.sql.SQLException: Could not commit with auto-commit set on
I have the rest of the log error if needed.
As a note, this same process functions properly in Pega 6.
If any more information is needed let me know and I will respond.
***Updated by moderator: Lochan to add Categories***
I will answer as best I can but I am not sure what XA Driver is. If I miss anything specific let me know:
PegaRULES Build Information
Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
Here is the response from Pega Support that eventually fixed my issue:
"In your 6.x environment you are using the 126.96.36.199.0 version of the Oracle JDBC driver. In 7.x you are using the 188.8.131.52.0 version. There is a change in behavior with the 12.x version of the driver which I believe is causing this problem. I would like to test this theory out. In the 7.x environment could you either try using the older version of the driver or add this JVM argument (-Doracle.jdbc.autoCommitSpecCompliant=false) and then test this again and see if the behavior reverts to the working scenario."
We used the JVM argument and it fixed the error. Apparently my RDB_List query should not have been using the auto commit as it is not a global transaction. They are still trying to see why it was the case here.
Just want to clarify the resolution of this issue. This looks like a bug in the ojdbc7.jar version 184.108.40.206.0. I do not think it is related to whether this is part of a global transaction or not.
Caused by: java.sql.SQLException: Could not commit with auto-commit set on
... 124 more
If you look at this stack trace the Pega code has a database connection and is trying to setAutoCommit. For an RDB it would be setting it to true.
As we can see from the Oracle driver documentation and from the stack trace it is the Oracle driver that is actually issuing the commit statement.
Oracle JDBC 12.1 drivers comply with JDBC specification 4.1 and will:
throw SQLException when Connection.commit() or Connection.rollback() is invoked when auto-commit is true.
issue an implicit commit of the local transaction when Connection.setAutoCommit(boolean) is called and the mode is changed from false to true.
Per the documentation an implicit commit is called when setAutoCommit is changed from false to true. If they did the commit check when autocommit is false and before it is changed to true then this exception wouldn't be thrown. Either they are not issuing the commit until after it has been changed to true and/or are also issue the commit check for true to true or true to false transitions.
ie, false to false, true to true, true to false should all not be testing the commit
I wrote a simple Java program to confirm this. I have tried this with the ojdbc6.jar 220.127.116.11.0 version and also with the ojdbc7 18.104.22.168.0 and it works as expected so I think the issue is just with the ojdbc7.jar 22.214.171.124.0 version that you are using. The resolution would be to either keep the compliance check shutoff by adding this JVM argument (-Doracle.jdbc.autoCommitSpecCompliant=false) or to upgrade to the 126.96.36.199.0 version of the driver.