Hi John, Thanks for the information. It was helpful in understanding your scenario. Unfortunately, I think you have found a bug... At first I thought it was due to your UniqueConstraint configuration. You had included the equipmentId in your UniqueConstraint. But, since every Axle has the same equipmentId, I thought maybe this is what the code was tripping over. If you could post the complete stack trace with the error, then that would confirm what column is having the issue.
My next guess was that maybe you were not batching your prepared statements. This is turned on by default for DB2 and Oracle. So, if you were using a non-batching database, maybe the statements were just being executed in the wrong order. Neither of these ideas panned out as being the culprit. I can easily reproduce the duplicate key exception by modifying one of our JUnits. The unfortunate part is that this looks to be quite tricky to correct. Although the documentation clearly states the intention of this UniqueConstraint with the ordering of the SQL statements, it doesn't look like this is being taken into account... There is much processing related to PK and FK constraints, but not the unique constraint... It looks like a JIRA is required. If you create a JIRA [1], I'd be happy to add my two cents worth concerning the JUnit test. Kevin [1] http://openjpa.apache.org/found-a-bug.html On Tue, Jun 12, 2012 at 3:44 AM, Boblitz John <john.bobl...@bertschi.com>wrote: > Hi Kevin, > > > Q1: Where is the duplicate key exception coming from? Which > > of the SQL statements is producing this exception? > Just after the set of SQL statements below (generated by commit()) the > exception is thrown > On the first Axle update. > > > Q2: The reason for the question is that Delete seems to be > > processing on uniqueid of 413966, but I don't see any Updates > > being processed on that key... > > Axle 413966 is not updated (see below) > > > Q3: So, are you expecting Axle with uniqueid of 413966 to be > > deleted before the updates? Or, not at all? Or, is this > > uniqueid key in error and you are expecting some other Axle > > to be deleted? > > Here is the Scenario: > > Start with > > Equipment #413908 > > Axle 1 #413966 > Axle 2 #498801 > Axle 3 #413965 > Axle 4 #504805 > > User removes Axle 1 from the Collection (on screen) > > What "should" happen: > > Since the reference bewteen Axle 1 and equipment is set to null - Axel > 1(413966) should be > Removed from the Database. > > Axle 2 (498801) should become Axle 1 > Axle 3 (431965) should become Axle 2 > Axle 4 (504805) should become Axle 3 > > > IOW - I think the delete should be done before the updates - and I assumed > that JPA would > Organise the SQLs based on the Unique Key Constraint accordingly, or at > least that is > what I thought this passage meant: > > 13.2. Unique Constraints > ... > "The unique constraints you define are used during table > creation to generate the proper database constraints, and may also be used > at runtime to order INSERT, UPDATE , and DELETE > statements. For example, suppose there is a unique constraint on the > columns of field F. In the same transaction, you remove an > object A and persist a new object B, both with the same F value. The JPA > runtime must ensure that the SQL deleting A is sent to > the database before the SQL inserting B to avoid a unique constraint > violation." > > Most likely, I am just missing some link somewhere as I can't imagine that > this is a bug that went > unnoticed .... > > > Thanks a lot for taking the time to look into this! > > > John > > > > -----Ursprüngliche Nachricht----- > > Von: Kevin Sutter [mailto:kwsut...@gmail.com] > > Gesendet: Montag, 11. Juni 2012 22:04 > > An: users@openjpa.apache.org > > Betreff: Re: Order of SQL Statements > > > > Hi John, > > Q1: Where is the duplicate key exception coming from? Which > > of the SQL statements is producing this exception? > > > > Q2: The reason for the question is that Delete seems to be > > processing on uniqueid of 413966, but I don't see any Updates > > being processed on that key... > > > > Q3: So, are you expecting Axle with uniqueid of 413966 to be > > deleted before the updates? Or, not at all? Or, is this > > uniqueid key in error and you are expecting some other Axle > > to be deleted? > > > > Just trying to understand the scenario and expected results. Thanks. > > > > Kevin > > > > > > On Mon, Jun 11, 2012 at 10:29 AM, Boblitz John > > <john.bobl...@bertschi.com>wrote: > > > > > Hello, > > > > > > after correcting the issues arrising from the InverseManager, and > > > adding annotations to remove unlinked Entities > > (@ElementDependent) I > > > get an error due to duplicate key. > > > > > > The goal is: > > > > > > I have up to 5 Axles and the User can delete any one of > > them and the > > > others should move up (Axle 3 becomes Axle 2 ...) When I > > delete the > > > first Axel, I get an exception. > > > > > > I get all the axles back from the GUI and proceed to move them from > > > the Data Object to my Entity: > > > > > > TreeSet<AxleDTO> axleDtos = new TreeSet<AxleDTO>(new > > > Comparator<AxleDTO>() { > > > > > > @Override > > > public int compare(AxleDTO pO1, AxleDTO pO2) { if > > (pO1.getAxleNumber() > > > < pO2.getAxleNumber()) { return -1; } else if > > (pO1.getAxleNumber() == > > > pO2.getAxleNumber()) { return 0; } else { return 1; } } }); > > > > > > axleDtos.addAll(pData.getAxles()); > > > if (axleDtos != null && !axleDtos.isEmpty()) { short i = 1; > > Set<Axle> > > > axles = new HashSet<Axle>(); AxleMgr axleMgr = new > > AxleMgr(mPm); for > > > (AxleDTO axleDto : axleDtos) { Axle axle = new Axle(); > > > axleMgr.toEntity(axleDto, axle); if (!axle.isActive()) { > > > axle.setEquipmentId(null); } else { axle.setAxleNumber(i++); > > > axle.setEquipmentId(pEntity); axles.add(axle); } } > > > pEntity.setAxles(axles); } > > > > > > > > > @Entity > > > @Table(schema = "galaxy11", name = "Equipment") public > > class Equipment > > > extends BaseEntity { > > > > > > @JsonManagedReference > > > @OneToMany(cascade = CascadeType.ALL, mappedBy = > > "equipmentId", fetch > > > = > > > FetchType.EAGER) > > > @ElementDependent > > > private Set<Axle> axles = new HashSet<Axle>(); > > > > > > @Entity > > > @Table(schema = "galaxy11", name = "Axle", uniqueConstraints= > > > @UniqueConstraint(name="uq_Axle_equipmentId_axleNumber", > > columnNames = > > > { "equipmentId", "axleNumber" }) > > > ) > > > public class Axle extends BaseEntity { > > > > > > @Basic > > > @Column(columnDefinition = "int2") > > > private short axleNumber; > > > > > > @JsonBackReference > > > @ManyToOne(fetch = FetchType.EAGER) > > > @JoinColumn(name = "equipmentId", columnDefinition = > > "int8", nullable > > > = > > > false) > > > private Equipment equipmentId; > > > > > > > > > > > > It all seems to work as I expect, except for the order of > > the statements! > > > The trace shows that the delete of the Axle is done AFTER > > the updates: > > > > > > > > > > > > UPDATE galaxy11.Equipment > > > SET modified = ?, versionid = ?, length = ?, height = ?, > > wheelbase = > > > ?, tareweight = ?, grossweight = ? > > > WHERE uniqueid = ? AND versionid = ? > > > [params=(Timestamp) 2012-06-11 13:47:18.21, (int) 33, (BigDecimal) > > > 6.2, > > > (BigDecimal) 3.1, (BigDecimal) 3.8, (BigDecimal) 6.7, > > (BigDecimal) 18, > > > (long) 413908, (int) 32] > > > - <t 656806424, conn 972792495> [27 ms] spent > > > > > > - <t 656806424, conn 972792495> executing prepstmnt > > 684003742 UPDATE > > > galaxy11.Axle SET modified = ?, versionid = ?, equipmentid = ?, > > > axlenumber = ? > > > WHERE uniqueid = ? AND versionid = ? > > > [params=(Timestamp) 2012-06-11 13:47:18.23, (int) 15, (long) 413908, > > > (short) 2, (long) 413965, (int) 14] > > > - <t 656806424, conn 972792495> [23 ms] spent > > > > > > - <t 656806424, conn 972792495> executing prepstmnt 10578812 UPDATE > > > galaxy11.Axle SET modified = ?, versionid = ?, equipmentid = ?, > > > axlenumber = ? > > > WHERE uniqueid = ? AND versionid = ? > > > [params=(Timestamp) 2012-06-11 13:47:18.24, (int) 2, (long) 413908, > > > (short) 3, (long) 504805, (int) 1] > > > - <t 656806424, conn 972792495> [21 ms] spent > > > > > > - <t 656806424, conn 972792495> executing prepstmnt > > 1704434230 UPDATE > > > galaxy11.Axle SET modified = ?, versionid = ?, equipmentid = ?, > > > axlenumber = ? > > > WHERE uniqueid = ? AND versionid = ? > > > [params=(Timestamp) 2012-06-11 13:47:18.24, (int) 13, (long) 413908, > > > (short) 1, (long) 498801, (int) 12] > > > - <t 656806424, conn 972792495> [22 ms] spent > > > > > > - <t 656806424, conn 972792495> executing prepstmnt > > 1141736277 UPDATE > > > galaxy11.Motor SET modified = ?, versionid = ? > > > WHERE uniqueid = ? AND versionid = ? > > > [params=(Timestamp) 2012-06-11 13:47:18.24, (int) 28, > > (long) 414058, > > > (int) 27] > > > - <t 656806424, conn 972792495> [21 ms] spent > > > > > > - <t 656806424, conn 972792495> executing prepstmnt > > 1395221594 DELETE > > > FROM galaxy11.Axle WHERE uniqueid = ? AND versionid = ? > > > [params=(long) 413966, (int) 14] > > > > > > > > > I am certain I am missing something quite minor, but after running > > > interactive debugs between The GUI and the persistence for > > the last 48 > > > hrs, I think I am missing the obvious. > > > > > > Anyone out there see where I am going astray? > > > > > > > > > Thanks, > > > > > > John > > > > > > > > > > > >
