Hello Keven, "Unfortunately, I think you have found a bug... "
I hate it when that happens! I have opened a Jira[1] If you need any additional information / traces / etc... I will gladly provide them. Thanks for your help - hopefully sometime after my current project is finished, I will have some time to contribute back to the project. John [1] https://issues.apache.org/jira/browse/OPENJPA-2211 > -----Ursprüngliche Nachricht----- > Von: Kevin Sutter [mailto:kwsut...@gmail.com] > Gesendet: Mittwoch, 13. Juni 2012 00:50 > An: users@openjpa.apache.org > Betreff: Re: Order of SQL Statements > > 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 > > > > > > > > > > > > > > > > > >
