Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Secondary Table Cascade Delete issue(Cascade delete)
Secondary Table Cascade Delete issue [message #901080] Thu, 09 August 2012 16:08 Go to next message
Kevin H is currently offline Kevin HFriend
Messages: 7
Registered: November 2010
Junior Member
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;

Report message to a moderator

Re: Secondary Table Cascade Delete issue [message #901593 is a reply to message #901080] Mon, 13 August 2012 15:03 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
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

Report message to a moderator

Re: Secondary Table Cascade Delete issue [message #901793 is a reply to message #901593] Tue, 14 August 2012 15:28 Go to previous messageGo to next message
Kevin H is currently offline Kevin HFriend
Messages: 7
Registered: November 2010
Junior Member
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?

Report message to a moderator

Re: Secondary Table Cascade Delete issue [message #901815 is a reply to message #901793] Tue, 14 August 2012 16:42 Go to previous message
Kevin H is currently offline Kevin HFriend
Messages: 7
Registered: November 2010
Junior Member
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

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 15:08:36 GMT 2024

Powered by FUDForum. Page generated in 0.03025 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly