JPA + Oracle JDBC on Java 9+ loses sub-second precision for @Version - FISH-854

[RedeemerSK]

Description

---

If running on Java 9 or later, Oracle JDBC driver can not be placed to $domain_root/lib/ext as told by all HOW-TOs, since this directory is not on (extension class-loader's) classpath anymore - java.ext.dirs launching param is not used as it was removed in Java 9.

Placing the driver JAR to $domain_root/lib makes EclipseLink JPA module to select incorrect database platform implementation class, which results in losing sub-second precision for JPA entity's temporal field annotated with @javax.persistence.Version.

Expected Outcome

High-Level: Value set to column for temporal field (of JPA Entity) annotated with @Version should have sub-second precision in recent Oracle DB. Low-Level: EclipseLink's module should pick correct database platform implementation class for recent Oracle DB. Root-Level: It should be possible to place Oracle JDBC driver JAR somewhere so that it's classes can be loaded by EclipseLink's modules properly.

On Java 8 when java.ext.dirs is used, classes in Oracle JDBC driver packages are reachable to org.eclipse.persistence.oracle.jar module (contains custom eclipselink platforms) by means of system (root) OSGi bundle exporting packages as made by glassfish-oracle-jdbc-driver-packages.jar fragment bundle + configured org.osgi.framework.bootdelegation for those packages.

Current Outcome

High-Level: Value set to column for temporal field (of JPA Entity) annotated with @Version does not have sub-second precision in recent Oracle DB. Low-Level: EclipseLink's module picks wrong database platform implementation class for recent Oracle DB. Root-Level: It is not possible to place Oracle JDBC driver JAR somewhere so that it's classes can be loaded by EclipseLink's modules properly.

For versioning, JPA calls getTimestampQuery() of database platform implementation class. Oracle platform implementation classes in org.eclipse.persistence.platform.database.oracle package (in org.eclipse.persistence.oracle.jar) make SELECT SYSTIMESTAMP FROM DUAL, while "fallback" platform implementation classes in org.eclipse.persistence.platform.database package (in org.eclipse.persistence.core.jar) make SELECT SYSDATE FROM DUAL. I assume proper oracle platform selection was always the reason why all HOW-TOs for Glassfish/Payara always told to put Oracle JDBC driver to /lib/ext directory.

With JDBC driver JAR placed in $domain_root/lib, classes from it are no longer loadable for classes in org.eclipse.persistence.platform.database.oracle package (in org.eclipse.persistence.oracle.jar), thus eclipselink falls back to using those in org.eclipse.persistence.platform.database package (in org.eclipse.persistence.core.jar) that do not import any classes from JDBC driver.

[edited @ 19.04.2020]

Steps to reproduce

1 -** Have some recent Oracle DB to use (sample app expects localhost:1521, sid: orclcdb ) 2 -** Create necessary things in DB (see 'etc/db_schema.sql' file in sample app) 3 -** Download Oracle JDBC driver JAR, copy it to $payara_install/glassfish/domains/domain1/lib 4 -** Start the domain1 using Java 9 or later 5 -** Deploy 'payara5-on-java11-jpa-test' sample application (see below) 6 -** See server log for message loged by the application (WARN level): "[payara5-on-java11-jpa-test] NANO_OF_SECOND of @Version field of persisted entity: XXXXX"

[edited @ 19.04.2020]

Samples

https://drive.google.com/open?id=1WjWQYKtYoWiixL1SCV2uGUkfa2ErXCuQ

Context

Was evaluating possibility of switching to latest Payara 5 running on Java 11 (or 14).

A quick workaround that I made and will use for now is new OSGi bundle - extension fragment for system bundle (Fragment-Host: system.bundle; extension:=framework) that contains contents (classes & resources) of Oracle JDBC driver JAR file. Basically make the classes loadable directly by OSGi system bundle's class-loader.

Environment

• Payara Version: 5.201
• Edition: Full
• JDK Version: OpenJDK 11.0.2
• Operating System: Windows
• Database: Oracle 12c

[marcreichman]

I'm not seeing this specific issue, but I am seeing a related issue in accessing the user_scheduler_jobs data via JPA. This was something I could do in JDK 8 / Payara 5.183 with the oracle jar in domain1/lib/ext moving it from just domain1/lib. Since in JDK 11 / Payara 5.201 the ext dir is not serving the same purpose, I cannot call this code correctly.

What is the recommended approach to access this extended JDBC ability previously served by the extension mechanism?

@RedeemerSK Can you explain your workaround, how to create it and have it deployed in the domain?

[RedeemerSK]

Download the following JAR file glassfish-oracle-jdbc-driver-packages-java9-fix.jar and add only 'oracle' directory from inside of ojdbc JAR of your choosing to the downloaded JAR archive so the downloaded JAR file will contain 2 archived directories: 'META-INF' that was there and 'oracle' from ojdbc JAR.

Then copy the prepared JAR file into $payar_root/glassfish/modules. (NOTE: this means it will affect all domains)

What makes it to work is aforementioned Fragment-Host: system.bundle; extension:=framework in MANIFEST.MF of the glassfish-oracle-jdbc-driver-packages-java9-fix.jar which basically attaches content of this bundle (oracle classes) to the OSGi system bundle, thus making classes loadable by EclipseLink OSGi bundle wired to system bundle.

If you want to change contents of the JAR module in $payar_root/glassfish/modules once it was already used by Payara (GF) runtime, you need to clear the domain's 'osgi-cache' folder.

EDIT

Or if you don't mind changing existing modules in Payara's installation, then putting 'oracle' directory (from inside ojdbc JAR) into the existing $payar_root/glassfish/modules/glassfish-oracle-jdbc-driver-packages.jar module should also do the trick.

[fturizo]

@RedeemerSK, I'm afraid that since this a complex issue that depends on an Oracle database we can't provide assistance without a self-contained reproducer. Can you provide a complete reproducer with the database configuration as well? The test scenario is missing the configuration of the jdbc/oracle data source (specific Oracle Database JDBC driver, connection-pool settings, etc.)

My recommendation is that you setup a simple Docker compose scenario that starts the Oracle database in a container, setups a separate container with the server and deploys the application.

Once we have the complete reproducer we can proceed to review it in detail.

[RedeemerSK]

@fturizo I believe this does not really depend on exact version of Oracle DB, assuming you will use something at least somehow recent, meaning Oracle 8 (released in 1997) or above. I believe same holds true for exact version of JDBC driver. Also DB configuration I strongly believe does not need to be altered from whatever the default is.

Now the reason I submitted this issue is not really to get support or patch for the issue as I've already designed a workaround that will do it for me. It's to make Payara dev team, in the first place, to be aware of this behavior that may negatively affect all customers using Payara on Java 9+ with Oracle DB. Also for other devs that may experience strange/unexpected behavior searching online for possible explanations and/or solutions.

That being said, I'll try to create suggested reproducer for Payara dev team, if the issue will not get reproduced & confirmed in the meantime. However, since it's not a showstopper for me, and due to me not having experience with docker so far, it may and will take time.

Maybe me elaborating on impacts caused by this issue may help this issue get more attention: The thing that matters to me - losing sub-second precision for @Version JPA fields, means that JPA's optimistic locking mechanism will not recognize concurrent modifications of same DB row(s) by multiple transactions done within same second. That means Eclipselink will not throw OptimisticLockException when it should. This can and most probably will break DB data consistency in any application that relies on optimistic locking in JPA.

Not only this, but all other additional functionalities that are implemented in platform classes residing in org.eclipse.persistence.platform.database.oracle package (from org.eclipse.persistence.oracle.jar) will not work / be used. My guess is that there may be things other than this @Version handling which, when not used, may have significant impact (read "break") on applications.

(I've edited original post to fix some inaccurate info about platform classes and reflect latest changes I did to the sample app - it now creates connection pool & jdbc resource for itself)

[fturizo]

@RedeemerSK, many thanks for the comprehensive explanation. We agree that the best solution is to fix the underlying issue in Payara Server 5 when using JDK 11 since it guarantees proper optimistic locking when using the configuration you mentioned. Unfortunately, to assist in this manner, we cannot divest our resources in preparing a complex reproducer without the full set of details, since this doesn't guarantee that we will encounter the same issue. For complex testing scenarios like this, we require users to provide us with a complete self-contained reproducer that we can use to escalate the issue to our engineering team.

For the moment we'll wait a bit more and see if you can prepare the reproducer then.

[RedeemerSK]

Hello again @fturizo Sorry for long silence, but better late then never, right ?

Here's Docker compose scenario to verify both correct and incorrect behavior for Java 8 and Java 11 respectively.

https://github.com/RedeemerSK/payara5-oracle_jpa_version_test/

Steps:

1. Accept Oracle's TOS for non-commercial use of Oracle Database Enterprise Edition docker image from Docker hub
2. Run the docker-compose up in the {payara5-oracle_jpa_version_test-checkout_root}/compose folder
3. Wait until compose builds & starts containers for 3 images - one for Oracle DB, one for Payara 5.201 running on Java 8 and one for Payara 5.201 running on Java 11
4. Wait until Oracle DB is fully initialized and operational - will take minutes, so be patient ...
5. Wait for Payara instances get started and auto-deploy test application
6. Check log messages from the test app starting with '[payara5-on-java11-jpa-test]' (one being SEVERE to spot easily)

Oracle DB image is based on official docker image for Oracle Database Enterprise Edition for which you need to first accept TOS on Docker hub before being able to download & use it (for free for non-commercial purposes). Then of course you local docker installation have to be logged-in to the docker hub account with which you accepted TOS.

Payara 5.201 on Java 11 is modified https://github.com/payara/docker-payaraserver-full. Sometimes only temporarily I experienced some troubles building it coming from gpg utility having trouble connecting to keyserver (line 51 in Dockerfile). Try to remove double quotes from keyserver URL if that happens for you, that worked for me.

Repo also contain test webapp source code just to see what app does, no need to build it. After app is started, it does persist one instance of JPA entity into DB and print if the precision for @Version field was lost or not (watch for SEVERE log message starting with '[payara5-on-java11-jpa-test]'). You can also send GET HTTP to servlet (deployed to '/' context path) that will do the same thing.

[RedeemerSK]

Haven't tested it yet, but it's possible my above workaround could be made slightly better by taking advantage of Bundle-ClassPath OSGi manifest header (https://docs.osgi.org/specification/osgi.core/7.0.0/framework.module.html#i2654895) and just include chosen oracle JDBC jar as it is inside a bundle.

~~Other than that, as of now I'm not aware of any out-of-the-box way (like copy somewhere and voila, it works) to have custom, as in not known in advance, standalone non-OSGi-fied JAR to be considered by Felix / any OSGi runtime.~~

EDIT: Actually if this whole OSGi approach to loading oracle classes wanted to be kept, then maybe something similar to "wrap" protocol of karaf (= just felix + goodies) could be used to make oracle jdbc jar into osgi bundle. But, looks like there are better ways to fix it (see comment below)

[RedeemerSK]

I found probably the least intrusive workaround: Moving org.eclipse.persistence.oracle.jar from glassfish/modules to $domain_root/lib together with having ojdbc jar also in that lib folder will make them to be loaded by the same classloader, thus visible to each other.

From what I can tell, org.eclipse.persistence.oracle.jar should be fine with running outside of osgi runtime since it can still access all it's imports that are exported "from osgi universe".

[For future versions of gf/pyr] And since oracle classes are no longer access by osgi modules via osgi system bundle, glassfish-oracle-jdbc-driver-packages.jar is not necessary anymore. And I believe also the eclipselink.bootdelegation=oracle.sql, oracle.sql.* line in glassfish/config/osgi.properties will not be needed in the future.

EDIT: Updated Docker compose scenario to use latest official Payara 2020.5 images and added image/container with Payara on JDK 11 with above workaround applied confirming it's effectiveness.

[MeroRai]

Hi @RedeemerSK,

Thank you for providing us with very detailed reproducer. We were able to reproduce this issue on our side. I have raised an internal issue FISH-854 to track this issue.