CODEWITHSUNDEEP
javasqlcachingopenjpa
Alex
Alex

Reputation: 7491

How to fix OpenJPA Query SQL CACHE error?

We use OPENJPA with Tomcat operating with the legacy proprietary package org.apache.renamed.openjpa prepared by Elatic Path Software. We found that turning QuerySQLCache OFF is necessary for running some queries. For example, the following quite sophisticated query:

SELECT t0.CARRIER, t0.CODE, t0.DEFAULT_COST, t0.ENABLED, t0.GUID, t0.LAST_MODIFIED_DATE, t1.UIDPK, t1.TYPE, t2.UIDPK, t2.GUID, t2.NAME, t2.REGION_STR, t3.UIDPK, t4.UIDPK, t4.CATALOG_CODE, t4.DEFAULT_LOCALE, t4.MASTER, t4.NAME, t3.STORECODE, t3.CONTENT_ENCODING, t3.COUNTRY, t3.CREDIT_CARD_CVV2_ENABLED, t3.DEFAULT_CURRENCY, t3.DEFAULT_LOCALE, t3.DESCRIPTION, t3.DISPLAY_OUT_OF_STOCK, t3.EMAIL_SENDER_ADDRESS, t3.EMAIL_SENDER_NAME, t3.ENABLED, t3.NAME, t3.STORE_ADMIN_EMAIL, t3.STORE_FULL_CREDIT_CARDS, t3.STORE_STATE, t3.STORE_TYPE, t3.SUB_COUNTRY, t3.TIMEZONE, t3.URL, t5.OBJECT_UID, t5.UIDPK, t5.TYPE, t5.LOCALIZED_PROPERTY_KEY, t5.VALUE FROM TSHIPPINGSERVICELEVEL t0 LEFT OUTER JOIN TSHIPPINGCOSTCALCULATIONMETHOD t1 ON t0.SCCM_UID = t1.UIDPK LEFT OUTER JOIN TSHIPPINGREGION t2 ON t0.SHIPPING_REGION_UID = t2.UIDPK LEFT OUTER JOIN TSTORE t3 ON t0.STORE_UID = t3.UIDPK LEFT OUTER JOIN TLOCALIZEDPROPERTIES t5 ON t0.UIDPK = t5.OBJECT_UID AND ? = t5.TYPE LEFT OUTER JOIN TCATALOG t4 ON t3.CATALOG_UID = t4.UIDPK WHERE t0.UIDPK = ?

results in the error:

org.apache.renamed.openjpa.persistence.PersistenceException: No value specified for parameter 2

The error does not happen when the cache is OFF. Please, note that the origin of this query is from complicated fetch relationships written in a way specific for OPENJPA.
It starts from finding the Order containing Fetch group and shipments (interface ShipOrder). From the implementation of ShipOrder (PhysicalOrderShipmentImpl ) we fetch shippingServiceLevelInternal that results in the select statement for TSHIPPINGSERVICELEVEL (see some code below).

@FetchGroup(name = FetchGroupConstants.ORDER_SEARCH, attributes = { 
            @FetchAttribute(name = "orderNumber"),
            @FetchAttribute(name = "shipments"),
...
@DataCache(enabled = false)
public class OrderImpl extends AbstractListenableEntityImpl
private List<OrderShipment> shipments = new ArrayList<OrderShipment>();


public interface PhysicalOrderShipment extends OrderShipment
...
ShippingServiceLevel getShippingServiceLevel();

@FetchGroup(name = FetchGroupConstants.ORDER_DEFAULT, attributes = {
    @FetchAttribute(name = "shipmentAddressInternal"),
    @FetchAttribute(name = "shippingServiceLevelInternal")
}, fetchGroups = { FetchGroupConstants.DEFAULT  }, postLoad = true)
})
@DataCache(enabled = false)
public class PhysicalOrderShipmentImpl extends AbstractOrderShipmentImpl implements PhysicalOrderShipment {
...
/**
 * Gets the shipping service level.
 *
 * @return ShippingServiceLevel the shipping service level
 */
ShippingServiceLevel getShippingServiceLevel();

From the log I also see some QuerySQL Cache problems:

catalina.07080951.out:DEBUG Cache miss while looking up key "org.apache.renamed.openjpa.datacache.QueryKey@9202a145[query:[SELECT sr FROM ShippingRegionImpl sr ORDER BY sr.name],access path:[com.elasticpath.domain.shipping.impl.ShippingRegionImpl],subs:true,ignoreChanges:false,startRange:0,endRange:9223372036854775807,timeout:1800000]". catalina.07080951.out:DEBUG Put key "org.apache.renamed.openjpa.datacache.QueryKey@9202a145[query:[SELECT sr FROM ShippingRegionImpl sr ORDER BY sr.name],access path:[com.elasticpath.domain.shipping.impl.ShippingRegionImpl],subs:true,ignoreChanges:false,startRange:0,endRange:9223372036854775807,timeout:1800000]" into cache.

Is there any way to fix our application allowing us to turn on QuerySQLCache in OpenJPA? Sorry for not completely clear code example, the whole code will take a lot of space. Hints are appreciated.

Upvotes: 1

Views: 286

Answers (1)

Rick
Rick

Reputation: 3840

Is there any way to fix our application allowing us to turn on QuerySQLCache in OpenJPA?

Short answer, most likely not. There have been numerous bugs in the QuerySQLCache that have been fixed. Since you're using a repackaged version of OpenJPA, I don't suppose that it is possible for you to try a newer version of OpenJPA to see if the problem you've encountered has been fixed? If that fixes your problem, you'll need to work with Elastic Path to update the version of OpenJPA that they ship.

Upvotes: 1

Related Questions