Eclipse Community Forums: EclipseLink » Secondary Table Cascade Delete issue

Home » Eclipse Projects » EclipseLink » Secondary Table Cascade Delete issue(Cascade delete)

Secondary Table Cascade Delete issue [message #901080]

Thu, 09 August 2012 12:08

Eclipse User

Running into an issue with a Cascade delete not happening on a SecondaryTable on a delete of domain object-

This i

[8/9/12 11:01:21:744 CDT] 0000002d SystemErr R Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: [SQL0532] Delete prevented by referential constraint Q_WBQUOTE_CP_LO00003_LOCAT00002_00001 in WBQUOTE.
Error Code: -532
Call: DELETE FROM CP_LOCATION WHERE (LOCATION_ID = ?)
bind => [849330]
Query: DeleteObjectQuery(com.ggg.jpa.domain.CpLocationOld@3cc52cf)
[8/9/12 11:01:21:744 CDT] 0000002d SystemErr R at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
[8/9/12 11:01:21:744 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:851)
[8/9/12 11:01:21:744 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:913)
[8/9/12 11:01:21:744 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:594)
[8/9/12 11:01:21:744 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:537)
[8/9/12 11:01:21:744 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1800)
[8/9/12 11:01:21:744 CDT] 0000002d SystemErr R at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:286)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.deleteObject(DatasourceCallQueryMechanism.java:184)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.queries.StatementQueryMechanism.deleteObject(StatementQueryMechanism.java:101)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.queries.DeleteObjectQuery.executeDatabaseQuery(DeleteObjectQuery.java:214)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:852)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:751)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.queries.DeleteObjectQuery.executeInUnitOfWorkObjectLevelModifyQuery(DeleteObjectQuery.java:115)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2875)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1602)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1584)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1535)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:334)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:288)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1422)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:634)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1509)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:266)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1147)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:84)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R ... 15 more
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R Caused by: java.sql.SQLException: [SQL0532] Delete prevented by referential constraint Q_WBQUOTE_CP_LO00003_LOCAT00002_00001 in WBQUOTE.
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at com.ibm.as400.access.JDError.throwSQLException(JDError.java:703)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at com.ibm.as400.access.JDError.throwSQLException(JDError.java:669)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at com.ibm.as400.access.AS400JDBCStatement.commonExecute(AS400JDBCStatement.java:1025)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at com.ibm.as400.access.AS400JDBCPreparedStatement.executeUpdate(AS400JDBCPreparedStatement.java:1526)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteUpdate(WSJdbcPreparedStatement.java:1185)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(WSJdbcPreparedStatement.java:802)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:842)
[8/9/12 11:01:21:760 CDT] 0000002d SystemErr R ... 42 more

The constraint error that is referenced is the CP_LOCATION to CP_LOCATION_PKG relationship.

The domain object is -
/**
* CpLocation entity.
*
*/
@Entity
@Table(name = "CP_LOCATION")
@CascadeOnDelete
@Cache(alwaysRefresh = true)
@SecondaryTable(name = "CP_LOCATION_PKG")
@Converter(name = "boolean", converterClass = com.ggg.jpa.domain.mappings.converters.BooleanYNConverter.class)
public class CpLocation extends BaseDomain implements Comparable<CpLocation> {

// Fields

/**
*
*/
@Transient
private static final long serialVersionUID = -6110946965102823627L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "LOCATION_ID", unique = true, nullable = false, precision = 11, scale = 0)
private Long locationId;

@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "TRANSACTION_ID", nullable = false)
private CpTransaction cpTransaction;

@Column(name = "LOCATION_NUMBER", nullable = false)
private Integer displayNumber = Integer.valueOf(0); // same as location number

@Column(name = "LOCATION_STATE", nullable = false, length = 2)
private String locationState;

@Column(name = "BUILDINGS_SAME_ADDRESS", length = 1)
@Convert("boolean")
private Boolean buildingsSameAddress = Boolean.FALSE;

@Embedded
private CpActionCode cpActionCode;

@OneToMany(cascade = CascadeType.ALL, mappedBy = "cpLocation")
@PrivateOwned
private Set<CpBuilding> cpBuildings = new HashSet<CpBuilding>(0);

@OneToMany(cascade = CascadeType.ALL, mappedBy = "cpLocation")
@PrivateOwned
private Set<CpClassification> cpClassifications = new TreeSet<CpClassification>();

// bi-directional many-to-many association to CpLobs
@OneToMany(cascade = CascadeType.ALL, mappedBy = "cpLocation")
private Set<CpLobLocation> cpLobLocations = new HashSet<CpLobLocation>(0);

@OneToMany(cascade = CascadeType.ALL, mappedBy = "cpLocation")
@PrivateOwned
private Set<CpLocationInterestLink> cpLocationInterestLinks = new HashSet<CpLocationInterestLink>();

//Certain Additional Interest Name Types are premium bearing.
//For these, we are creating a coverage on the premise
@OneToMany(cascade = CascadeType.ALL, mappedBy = "cpLocation")
@PrivateOwned
private Set<CpLocationCoverage> cpLocationCoverages = new HashSet<CpLocationCoverage>(0);

@Column(name = "EARTHQUAKE_ZONE", length = 10, table = "CP_LOCATION_PKG")
private String earthquakeZone;

@Column(name = "FIRE_PROTECTION_FD", table = "CP_LOCATION_PKG")
private int fireProtectionFd;

@Column(name = "FIRE_PROTECTION_HYDRANT", table = "CP_LOCATION_PKG")
private int fireProtectionHydrant;

@Column(name = "PREMIUM_ACTUAL", precision = 9, scale = 2, table = "CP_LOCATION_PKG")
private Long premiumActual = Long.valueOf(0L);

@Column(name = "PROTECTION_CLASS", length = 2, table = "CP_LOCATION_PKG")
private String protectionClass;

@Column(name = "CLASS_CODE", length = 6, table = "CP_LOCATION_PKG")
private String classCode;

@Column(name = "PROTECTION_CLASS_OVERRIDE", length = 1, table = "CP_LOCATION_PKG")
@Convert("boolean")
private Boolean protectionClassOverride;

@Column(name = "TERR_MULT", length = 10, table = "CP_LOCATION_PKG")
private String territorialMultiplier;

@Column(length = 10, table = "CP_LOCATION_PKG")
private String territory;

@Column(name = "TERRITORY_BCEG", length = 10, table = "CP_LOCATION_PKG")
private String territoryBceg;

@Column(name = "TERRITORY_PREMOP", length = 10, table = "CP_LOCATION_PKG")
private String territoryPremOp;

@Column(name = "TERRITORY_TIER", length = 10, table = "CP_LOCATION_PKG")
private String territoryTier;

@Column(name = "COUNTY_CODE", length = 4)
private String countyCode;

@Column(name = "COUNTY_NAME", length = 45)
private String countyName;

// bi-directional many-to-one association to CpLobState
@ManyToOne()
@JoinColumn(name = "LOB_STATE_ID", table = "CP_LOCATION_PKG")
private CpLobState cpLobState;

Re: Secondary Table Cascade Delete issue [message #901593 is a reply to message #901080]

Mon, 13 August 2012 11:03

Eclipse User

Hello,

Can you delay constraint processing or remove the constraint temporarily and post the log for the transaction with logging turned to finest to show the commit order? This might help determine why it is attempting the delete out of order. Please also show how you are removing the CpLocation - it the delete cascading to it, or is it the entity that is being removed directly?

Best Regards,
Chris

Re: Secondary Table Cascade Delete issue [message #901793 is a reply to message #901593]

Tue, 14 August 2012 11:28

Eclipse User

I'm not sure how to "delay constraint processing". Is that a setting or is that something I need to do in my code?

I know in most cases you want me to try and isolate this as much as possible. Unfortunately this is a rather large complex data tree. Not trying to be obstinate but it would take me some time to strip it down to just the relationships that seem to be failing.

This has been in production working correctly for close to two years now, and for some reason a small number of these seem to be failing here, like 1% of all transactions. I have done some other testing with this tree and I can update data in the Secondary Table fine, but even then the delete fails(hoping for some lazy loading issue). I tried adding DescriptorEventListener to the CpLocation class then putting breakpoints in the aboutToDelete, and preDelete methods to see if I could inspect the class, but it is not breaking in those methods. I have also put breakpoints in the CascadeOnDelete interface and I can see that cpLocation is getting marked as a Cascading Delete item.

Is there something else I could try to figure out what is going on?

Re: Secondary Table Cascade Delete issue [message #901815 is a reply to message #901793]

Tue, 14 August 2012 12:42

Eclipse User

Done some more debugging...
So this is somewhat convoluted.. but to make this work with my current database structure, I had to add some "duplicate" domain objects.

So I have a domain object that represents what the old code needs it to look like and I have another domain object that represents what the new code base uses.

High level -
CpTransation {

@OneToMany(cascade = CascadeType.ALL, mappedBy = "cpTransaction")
@PrivateOwned
private Set<CpLocation> cpLocations = new HashSet<CpLocation>(0);

@OneToMany(cascade = CascadeType.ALL, mappedBy = "cpTransaction")
private Set<CpLocationOld> cpLocationsOld = new HashSet<CpLocationOld>(0);

}

@Entity
@Table(name = "CP_LOCATION")
@CascadeOnDelete
@Cache(alwaysRefresh = true)
@SecondaryTable(name = "CP_LOCATION_PKG")
@Converter(name = "boolean", converterClass = com.gmrc.jpa.domain.mappings.converters.BooleanYNConverter.class)
public class CpLocation extends BaseDomain implements Comparable<CpLocation>, DescriptorEventListener {

@Entity
@Table(name = "CP_LOCATION")
@Cache(alwaysRefresh = true)
@Converter(name = "boolean", converterClass = com.gmrc.jpa.domain.mappings.converters.BooleanYNConverter.class)
public class CpLocationOld extends BaseDomain implements Comparable<CpLocationOld> {

During the delete of the CpTransaction, I had previously cleared the list of CpLocationOld items that had a matching cpLocation item.

This seemed to work fine in most cases. But for some reason this seems to be repopulating the list of cpLocationOld items. I have inspected the list prior to the
this.em.remove(entity); and there are no items.

So I guess I have two approaches that would work best - either put some "filter" so that the cpLocationOld domain objects don't get populated at all if there is no corresponding record in the Secondary table, or keep the cpLocationOld objects from getting repopulated after I clear them on the delete.

Let me know if you need more clarification or if I am off base here.

Thanks

Previous Topic:	Open porject Groovy error
Next Topic:	Noob Problem: ClientSession.getAccessors() returns null

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Jul 23 09:59:49 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter