openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Boblitz John <John.Bobl...@BERTSCHI.com>
Subject AW: Order of SQL Statements
Date Wed, 13 Jun 2012 06:58:48 GMT
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:kwsutter@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.Boblitz@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:kwsutter@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.Boblitz@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
> > > >
> > > >
> > > >
> > >
> >
> 
Mime
View raw message