You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Geir Magnusson Jr." <ge...@pobox.com> on 2008/01/03 06:08:45 UTC
weird problem w/ a mapping table
I can't figure out what I'm doing wrong.
I'm using joined inheritance and have as my base table InventoryItem
with Agent and Contributor as subclasses / tables.
I also have a mapping table called Show2Contributor :
mysql> describe Show2Contributor;
+---------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| showId | int(11) unsigned | NO | PRI | | |
| contributorId | int(11) unsigned | NO | PRI | | |
+---------------+------------------+------+-----+---------+-------+
where each Show then has some set of contributors, with each
contributor containing an agent (and a static thing called a RoleCode,
whic is irrelevant).
Now, I have a weird problem. I'm trying to add 3 Contributors to a
Show. 2 already are mapped (one will stay, one will not). Two have
to be created new and added....
To create the first new contributor, we happen toneed to add a new
agent :
2947 TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 16400155 INSERT INTO InventoryItem (uuid,
itemType, name, coId, publicId, damId, ownerId, creationDate,, ...
2948 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 11750977 SELECT LAST_INSERT_ID()
2949 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 10285791 INSERT INTO Agent (id, commonName) VALUES
(?, ?) [params=(int) 194591, (String) Geir Magnusson Jr.]
so there it creates a new Agent by making the InventoryItem first,
getting the autogen ID, and using that to add to the Agent table.
Then it makes a new contributor, using that Agent (id=194591) :
2959 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 14647551 INSERT INTO InventoryItem (uuid,
itemType, name, coId, publicId, damId, ownerId, creationDate, ...
2997 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 1112653 SELECT LAST_INSERT_ID()
2997 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 4592108 INSERT INTO Contributor (id, roleCode,
agentId) VALUES (?, ?, ?) [params=(int) 194592, (int) 109, (int) 194591]
Fine - so we have a new contributor, id = 194592, with the new agent
(id=194591)
Now, remove from the mapping table the Contributor that we don't need :
3094 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 15955745 DELETE FROM Show2Contributor WHERE showId
= ? [params=(int) 13163]
Now create another Contributor (using an agent we already have) :
3096 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 15187341 INSERT INTO InventoryItem (uuid,
itemType, name, coId, publicId, damId, ownerId, creationDate,...
3122 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 9887497 SELECT LAST_INSERT_ID()
3123 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 9788885 INSERT INTO Contributor (id, roleCode,
agentId) VALUES (?, ?, ?) [params=(int) 194593, (int) 109, (int) 194573]
So that's the 2nd new Contributor (id=194593)
To review - two new contributors : 194592 and 194593. The third is an
old, existing one (happens to be 13163)
Now, OpenJPA then updates a lastModDate for each of the three :
3125 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 12844136 UPDATE InventoryItem SET
lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
23:45:48.924, (int) 13163]
3127 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 13915734 UPDATE InventoryItem SET
lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
23:45:48.989, (int) 194591]
3128 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 10653126 UPDATE InventoryItem SET
lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
23:45:48.989, (int) 194592]
And now seems to screw up adding to the Show2Contributor table - it
adds one of them twice (194593), and skips one (194592).
3129 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 8097602 INSERT INTO Show2Contributor (showId,
contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194575]
3131 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 9028526 INSERT INTO Show2Contributor (showId,
contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194593]
3132 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
executing prepstmnt 11857123 INSERT INTO Show2Contributor (showId,
contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194593]
This leads to a contraint violation, and the whole thing rolls back.
Um.
Help? :) This happens for both a 1.1.0-SNAPSHOT as well as 1.0.1
release.
TIA
geir
Re: weird problem w/ a mapping table
Posted by "Geir Magnusson Jr." <ge...@pobox.com>.
I found another issue w/ one of these mapping table (or whatever the
right word is for it).
For a given show, I have two entries that map the show to a country
and date :
mysql> select * from ShowRelease4Country where showId = 13163;
+-------+--------+-------------+-------------+
| id | showId | countryCode | releaseYear |
+-------+--------+-------------+-------------+
| 1 | 13163 | 1 | 2005 |
| 25170 | 13163 | 38 | NULL |
+-------+--------+-------------+-------------+
2 rows in set (0.00 sec)
with a contraint on showId-CountrCode, and for an update, I wanted to
just delete all entries, and add new ones :
Code :
for (ShowRelease4Country s : show.getReleaseCountries()) {
s.setShow(null);
OMUtil.deleteShowRelease4Country(s);
}
for (ShowRelease4Country s : dataSet) {
OMUtil.persist(s);
s.setShow(show);
}
show.setReleaseCountries(dataSet);
The SQL logging showed the insert first, and then the deletes, which
caused MySQL to call it a constraint violation. It was all in the
same tx. (I *thought* that the order wouldn't matter?)
Shoving a flush() between the two loops solved it - the two deletes
happened, and then the insert happened. MySQL was happy w/ that.
Is this a JPA issue, or is MySQL misbehaving, or am I being a dodo
somehow?
geir
On Jan 3, 2008, at 10:33 AM, Geir Magnusson Jr. wrote:
>
> On Jan 3, 2008, at 1:34 AM, Patrick Linskey wrote:
>
>> Have you tried calling em.flush() in between the operations to try to
>> isolate the problem?
>
> em.flush() makes it go away. I also *think* but can't consistently
> demonstrate, that if I run in debug, and just before commit dork
> about looking at my set of Contributors, digging down into the Agent
> objects, the problem disappears.
>
>> Are you certain that the collections are being
>> set up correctly (i.e., that you're not somehow sharing the same
>> collection among multiple instances)?
>
> I believe so. This is single threaded, for my test there only is
> one pass through this, etc The code in full minus comments to keep
> things shorter :
>
> Set<Contributor> targetSet = new HashSet<Contributor>();
>
> for (Duple roleCodeDuple : roleCodeAgentList) {
>
> RoleCode roleCode = roleCodeDuple.roleCode;
> String agentString = roleCodeDuple.agentString;
>
> Agent agent = OMUtil.getAgent(agentString);
>
> if (agent == null) {
> agent = OMUtil.newAgent(agentString);
>
> if (agent == null) {
> throw new RuntimeException("Panic! couldn't create an
> Agent for " + agentString);
> }
> }
>
> Contributor c = OMUtil.findContributor(roleCode, agent);
>
> if (c == null) {
> c = OMUtil.newContributor(roleCode, agent);
> }
>
> targetSet.add(c);
> }
>
> show.setContributors(targetSet);
>
> That's it. If I put a flush before setContributors(), it works fine.
>
>
>> It might be useful to print out
>> the System.identityHashCode() of the collection instances just prior
>> to flush / commit.
>
> I did it right before setting, and they are distinct values.
>
> Thanks for the help.
>
> geir
>
>>
>>
>> -Patrick
>>
>> On Jan 2, 2008 9:48 PM, Geir Magnusson Jr. <ge...@pobox.com> wrote:
>>> Sorry - there are a few minor errors in my interpretation, which
>>> doesn't change my problem. Commments inline :
>>>
>>>
>>> On Jan 3, 2008, at 12:08 AM, Geir Magnusson Jr. wrote:
>>>
>>>> I can't figure out what I'm doing wrong.
>>>>
>>>> I'm using joined inheritance and have as my base table
>>>> InventoryItem
>>>> with Agent and Contributor as subclasses / tables.
>>>>
>>>> I also have a mapping table called Show2Contributor :
>>>>
>>>> mysql> describe Show2Contributor;
>>>> +---------------+------------------+------+-----+---------+-------+
>>>> | Field | Type | Null | Key | Default | Extra |
>>>> +---------------+------------------+------+-----+---------+-------+
>>>> | showId | int(11) unsigned | NO | PRI | | |
>>>> | contributorId | int(11) unsigned | NO | PRI | | |
>>>> +---------------+------------------+------+-----+---------+-------+
>>>>
>>>> where each Show then has some set of contributors, with each
>>>> contributor containing an agent (and a static thing called a
>>>> RoleCode, whic is irrelevant).
>>>>
>>>> Now, I have a weird problem. I'm trying to add 3 Contributors to a
>>>> Show. 2 already are mapped (one will stay, one will not). Two
>>>> have
>>>> to be created new and added....
>>>>
>>>> To create the first new contributor, we happen toneed to add a new
>>>> agent :
>>>>
>>>> 2947 TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>>> executing prepstmnt 16400155 INSERT INTO InventoryItem (uuid,
>>>> itemType, name, coId, publicId, damId, ownerId, creationDate,, ...
>>>> 2948 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 11750977 SELECT LAST_INSERT_ID()
>>>> 2949 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 10285791 INSERT INTO Agent (id, commonName)
>>>> VALUES (?, ?) [params=(int) 194591, (String) Geir Magnusson Jr.]
>>>>
>>>> so there it creates a new Agent by making the InventoryItem first,
>>>> getting the autogen ID, and using that to add to the Agent table.
>>>>
>>>> Then it makes a new contributor, using that Agent (id=194591) :
>>>>
>>>> 2959 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 14647551 INSERT INTO InventoryItem (uuid,
>>>> itemType, name, coId, publicId, damId, ownerId, creationDate, ...
>>>> 2997 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 1112653 SELECT LAST_INSERT_ID()
>>>> 2997 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 4592108 INSERT INTO Contributor (id, roleCode,
>>>> agentId) VALUES (?, ?, ?) [params=(int) 194592, (int) 109, (int)
>>>> 194591]
>>>>
>>>> Fine - so we have a new contributor, id = 194592, with the new
>>>> agent
>>>> (id=194591)
>>>>
>>>> Now, remove from the mapping table the Contributor that we don't
>>>> need :
>>>
>>> Sorry - clearly this is just dumping the old mapping entries for the
>>> show, id = 13163.
>>>
>>>>
>>>> 3094 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 15955745 DELETE FROM Show2Contributor WHERE
>>>> showId = ? [params=(int) 13163]
>>>>
>>>> Now create another Contributor (using an agent we already have) :
>>>>
>>>> 3096 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 15187341 INSERT INTO InventoryItem (uuid,
>>>> itemType, name, coId, publicId, damId, ownerId, creationDate,...
>>>> 3122 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 9887497 SELECT LAST_INSERT_ID()
>>>> 3123 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 9788885 INSERT INTO Contributor (id, roleCode,
>>>> agentId) VALUES (?, ?, ?) [params=(int) 194593, (int) 109, (int)
>>>> 194573]
>>>>
>>>> So that's the 2nd new Contributor (id=194593)
>>>>
>>>> To review - two new contributors : 194592 and 194593. The third is
>>>> an old, existing one (happens to be 13163)
>>>>
>>>> Now, OpenJPA then updates a lastModDate for each of the three :
>>>>
>>> This is an update of the show we're going to map to.
>>>
>>>
>>>> 3125 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 12844136 UPDATE InventoryItem SET
>>>> lastModificationDate = ? WHERE id = ? [params=(Timestamp)
>>>> 2008-01-02
>>>> 23:45:48.924, (int) 13163]
>>>
>>>
>>>>
>>>> 3127 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 13915734 UPDATE InventoryItem SET
>>>> lastModificationDate = ? WHERE id = ? [params=(Timestamp)
>>>> 2008-01-02
>>>> 23:45:48.989, (int) 194591]
>>>> 3128 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 10653126 UPDATE InventoryItem SET
>>>> lastModificationDate = ? WHERE id = ? [params=(Timestamp)
>>>> 2008-01-02
>>>> 23:45:48.989, (int) 194592]
>>>>
>>>> And now seems to screw up adding to the Show2Contributor table - it
>>>> adds one of them twice (194593), and skips one (194592).
>>>>
>>>> 3129 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 8097602 INSERT INTO Show2Contributor (showId,
>>>> contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194575]
>>>> 3131 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 9028526 INSERT INTO Show2Contributor (showId,
>>>> contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194593]
>>>> 3132 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn
>>>> 6321587>
>>>> executing prepstmnt 11857123 INSERT INTO Show2Contributor (showId,
>>>> contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194593]
>>>>
>>>> This leads to a contraint violation, and the whole thing rolls
>>>> back.
>>>>
>>>> Um.
>>>>
>>>> Help? :) This happens for both a 1.1.0-SNAPSHOT as well as 1.0.1
>>>> release.
>>>>
>>>> TIA
>>>>
>>>> geir
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>>
>> --
>> Patrick Linskey
>> 202 669 5907
>
Re: weird problem w/ a mapping table
Posted by "Geir Magnusson Jr." <ge...@pobox.com>.
On Jan 3, 2008, at 1:34 AM, Patrick Linskey wrote:
> Have you tried calling em.flush() in between the operations to try to
> isolate the problem?
em.flush() makes it go away. I also *think* but can't consistently
demonstrate, that if I run in debug, and just before commit dork about
looking at my set of Contributors, digging down into the Agent
objects, the problem disappears.
> Are you certain that the collections are being
> set up correctly (i.e., that you're not somehow sharing the same
> collection among multiple instances)?
I believe so. This is single threaded, for my test there only is one
pass through this, etc The code in full minus comments to keep things
shorter :
Set<Contributor> targetSet = new HashSet<Contributor>();
for (Duple roleCodeDuple : roleCodeAgentList) {
RoleCode roleCode = roleCodeDuple.roleCode;
String agentString = roleCodeDuple.agentString;
Agent agent = OMUtil.getAgent(agentString);
if (agent == null) {
agent = OMUtil.newAgent(agentString);
if (agent == null) {
throw new RuntimeException("Panic! couldn't create an
Agent for " + agentString);
}
}
Contributor c = OMUtil.findContributor(roleCode, agent);
if (c == null) {
c = OMUtil.newContributor(roleCode, agent);
}
targetSet.add(c);
}
show.setContributors(targetSet);
That's it. If I put a flush before setContributors(), it works fine.
> It might be useful to print out
> the System.identityHashCode() of the collection instances just prior
> to flush / commit.
I did it right before setting, and they are distinct values.
Thanks for the help.
geir
>
>
> -Patrick
>
> On Jan 2, 2008 9:48 PM, Geir Magnusson Jr. <ge...@pobox.com> wrote:
>> Sorry - there are a few minor errors in my interpretation, which
>> doesn't change my problem. Commments inline :
>>
>>
>> On Jan 3, 2008, at 12:08 AM, Geir Magnusson Jr. wrote:
>>
>>> I can't figure out what I'm doing wrong.
>>>
>>> I'm using joined inheritance and have as my base table InventoryItem
>>> with Agent and Contributor as subclasses / tables.
>>>
>>> I also have a mapping table called Show2Contributor :
>>>
>>> mysql> describe Show2Contributor;
>>> +---------------+------------------+------+-----+---------+-------+
>>> | Field | Type | Null | Key | Default | Extra |
>>> +---------------+------------------+------+-----+---------+-------+
>>> | showId | int(11) unsigned | NO | PRI | | |
>>> | contributorId | int(11) unsigned | NO | PRI | | |
>>> +---------------+------------------+------+-----+---------+-------+
>>>
>>> where each Show then has some set of contributors, with each
>>> contributor containing an agent (and a static thing called a
>>> RoleCode, whic is irrelevant).
>>>
>>> Now, I have a weird problem. I'm trying to add 3 Contributors to a
>>> Show. 2 already are mapped (one will stay, one will not). Two have
>>> to be created new and added....
>>>
>>> To create the first new contributor, we happen toneed to add a new
>>> agent :
>>>
>>> 2947 TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 16400155 INSERT INTO InventoryItem (uuid,
>>> itemType, name, coId, publicId, damId, ownerId, creationDate,, ...
>>> 2948 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 11750977 SELECT LAST_INSERT_ID()
>>> 2949 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 10285791 INSERT INTO Agent (id, commonName)
>>> VALUES (?, ?) [params=(int) 194591, (String) Geir Magnusson Jr.]
>>>
>>> so there it creates a new Agent by making the InventoryItem first,
>>> getting the autogen ID, and using that to add to the Agent table.
>>>
>>> Then it makes a new contributor, using that Agent (id=194591) :
>>>
>>> 2959 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 14647551 INSERT INTO InventoryItem (uuid,
>>> itemType, name, coId, publicId, damId, ownerId, creationDate, ...
>>> 2997 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 1112653 SELECT LAST_INSERT_ID()
>>> 2997 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 4592108 INSERT INTO Contributor (id, roleCode,
>>> agentId) VALUES (?, ?, ?) [params=(int) 194592, (int) 109, (int)
>>> 194591]
>>>
>>> Fine - so we have a new contributor, id = 194592, with the new agent
>>> (id=194591)
>>>
>>> Now, remove from the mapping table the Contributor that we don't
>>> need :
>>
>> Sorry - clearly this is just dumping the old mapping entries for the
>> show, id = 13163.
>>
>>>
>>> 3094 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 15955745 DELETE FROM Show2Contributor WHERE
>>> showId = ? [params=(int) 13163]
>>>
>>> Now create another Contributor (using an agent we already have) :
>>>
>>> 3096 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 15187341 INSERT INTO InventoryItem (uuid,
>>> itemType, name, coId, publicId, damId, ownerId, creationDate,...
>>> 3122 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 9887497 SELECT LAST_INSERT_ID()
>>> 3123 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 9788885 INSERT INTO Contributor (id, roleCode,
>>> agentId) VALUES (?, ?, ?) [params=(int) 194593, (int) 109, (int)
>>> 194573]
>>>
>>> So that's the 2nd new Contributor (id=194593)
>>>
>>> To review - two new contributors : 194592 and 194593. The third is
>>> an old, existing one (happens to be 13163)
>>>
>>> Now, OpenJPA then updates a lastModDate for each of the three :
>>>
>> This is an update of the show we're going to map to.
>>
>>
>>> 3125 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 12844136 UPDATE InventoryItem SET
>>> lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
>>> 23:45:48.924, (int) 13163]
>>
>>
>>>
>>> 3127 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 13915734 UPDATE InventoryItem SET
>>> lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
>>> 23:45:48.989, (int) 194591]
>>> 3128 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 10653126 UPDATE InventoryItem SET
>>> lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
>>> 23:45:48.989, (int) 194592]
>>>
>>> And now seems to screw up adding to the Show2Contributor table - it
>>> adds one of them twice (194593), and skips one (194592).
>>>
>>> 3129 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 8097602 INSERT INTO Show2Contributor (showId,
>>> contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194575]
>>> 3131 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 9028526 INSERT INTO Show2Contributor (showId,
>>> contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194593]
>>> 3132 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
>>> executing prepstmnt 11857123 INSERT INTO Show2Contributor (showId,
>>> contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194593]
>>>
>>> This leads to a contraint violation, and the whole thing rolls back.
>>>
>>> Um.
>>>
>>> Help? :) This happens for both a 1.1.0-SNAPSHOT as well as 1.0.1
>>> release.
>>>
>>> TIA
>>>
>>> geir
>>>
>>>
>>>
>>
>>
>
>
>
> --
> Patrick Linskey
> 202 669 5907
Re: weird problem w/ a mapping table
Posted by Patrick Linskey <pl...@gmail.com>.
Have you tried calling em.flush() in between the operations to try to
isolate the problem? Are you certain that the collections are being
set up correctly (i.e., that you're not somehow sharing the same
collection among multiple instances)? It might be useful to print out
the System.identityHashCode() of the collection instances just prior
to flush / commit.
-Patrick
On Jan 2, 2008 9:48 PM, Geir Magnusson Jr. <ge...@pobox.com> wrote:
> Sorry - there are a few minor errors in my interpretation, which
> doesn't change my problem. Commments inline :
>
>
> On Jan 3, 2008, at 12:08 AM, Geir Magnusson Jr. wrote:
>
> > I can't figure out what I'm doing wrong.
> >
> > I'm using joined inheritance and have as my base table InventoryItem
> > with Agent and Contributor as subclasses / tables.
> >
> > I also have a mapping table called Show2Contributor :
> >
> > mysql> describe Show2Contributor;
> > +---------------+------------------+------+-----+---------+-------+
> > | Field | Type | Null | Key | Default | Extra |
> > +---------------+------------------+------+-----+---------+-------+
> > | showId | int(11) unsigned | NO | PRI | | |
> > | contributorId | int(11) unsigned | NO | PRI | | |
> > +---------------+------------------+------+-----+---------+-------+
> >
> > where each Show then has some set of contributors, with each
> > contributor containing an agent (and a static thing called a
> > RoleCode, whic is irrelevant).
> >
> > Now, I have a weird problem. I'm trying to add 3 Contributors to a
> > Show. 2 already are mapped (one will stay, one will not). Two have
> > to be created new and added....
> >
> > To create the first new contributor, we happen toneed to add a new
> > agent :
> >
> > 2947 TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 16400155 INSERT INTO InventoryItem (uuid,
> > itemType, name, coId, publicId, damId, ownerId, creationDate,, ...
> > 2948 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 11750977 SELECT LAST_INSERT_ID()
> > 2949 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 10285791 INSERT INTO Agent (id, commonName)
> > VALUES (?, ?) [params=(int) 194591, (String) Geir Magnusson Jr.]
> >
> > so there it creates a new Agent by making the InventoryItem first,
> > getting the autogen ID, and using that to add to the Agent table.
> >
> > Then it makes a new contributor, using that Agent (id=194591) :
> >
> > 2959 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 14647551 INSERT INTO InventoryItem (uuid,
> > itemType, name, coId, publicId, damId, ownerId, creationDate, ...
> > 2997 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 1112653 SELECT LAST_INSERT_ID()
> > 2997 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 4592108 INSERT INTO Contributor (id, roleCode,
> > agentId) VALUES (?, ?, ?) [params=(int) 194592, (int) 109, (int)
> > 194591]
> >
> > Fine - so we have a new contributor, id = 194592, with the new agent
> > (id=194591)
> >
> > Now, remove from the mapping table the Contributor that we don't
> > need :
>
> Sorry - clearly this is just dumping the old mapping entries for the
> show, id = 13163.
>
> >
> > 3094 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 15955745 DELETE FROM Show2Contributor WHERE
> > showId = ? [params=(int) 13163]
> >
> > Now create another Contributor (using an agent we already have) :
> >
> > 3096 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 15187341 INSERT INTO InventoryItem (uuid,
> > itemType, name, coId, publicId, damId, ownerId, creationDate,...
> > 3122 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 9887497 SELECT LAST_INSERT_ID()
> > 3123 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 9788885 INSERT INTO Contributor (id, roleCode,
> > agentId) VALUES (?, ?, ?) [params=(int) 194593, (int) 109, (int)
> > 194573]
> >
> > So that's the 2nd new Contributor (id=194593)
> >
> > To review - two new contributors : 194592 and 194593. The third is
> > an old, existing one (happens to be 13163)
> >
> > Now, OpenJPA then updates a lastModDate for each of the three :
> >
> This is an update of the show we're going to map to.
>
>
> > 3125 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 12844136 UPDATE InventoryItem SET
> > lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
> > 23:45:48.924, (int) 13163]
>
>
> >
> > 3127 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 13915734 UPDATE InventoryItem SET
> > lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
> > 23:45:48.989, (int) 194591]
> > 3128 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 10653126 UPDATE InventoryItem SET
> > lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
> > 23:45:48.989, (int) 194592]
> >
> > And now seems to screw up adding to the Show2Contributor table - it
> > adds one of them twice (194593), and skips one (194592).
> >
> > 3129 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 8097602 INSERT INTO Show2Contributor (showId,
> > contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194575]
> > 3131 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 9028526 INSERT INTO Show2Contributor (showId,
> > contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194593]
> > 3132 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> > executing prepstmnt 11857123 INSERT INTO Show2Contributor (showId,
> > contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194593]
> >
> > This leads to a contraint violation, and the whole thing rolls back.
> >
> > Um.
> >
> > Help? :) This happens for both a 1.1.0-SNAPSHOT as well as 1.0.1
> > release.
> >
> > TIA
> >
> > geir
> >
> >
> >
>
>
--
Patrick Linskey
202 669 5907
Re: weird problem w/ a mapping table
Posted by "Geir Magnusson Jr." <ge...@pobox.com>.
Sorry - there are a few minor errors in my interpretation, which
doesn't change my problem. Commments inline :
On Jan 3, 2008, at 12:08 AM, Geir Magnusson Jr. wrote:
> I can't figure out what I'm doing wrong.
>
> I'm using joined inheritance and have as my base table InventoryItem
> with Agent and Contributor as subclasses / tables.
>
> I also have a mapping table called Show2Contributor :
>
> mysql> describe Show2Contributor;
> +---------------+------------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +---------------+------------------+------+-----+---------+-------+
> | showId | int(11) unsigned | NO | PRI | | |
> | contributorId | int(11) unsigned | NO | PRI | | |
> +---------------+------------------+------+-----+---------+-------+
>
> where each Show then has some set of contributors, with each
> contributor containing an agent (and a static thing called a
> RoleCode, whic is irrelevant).
>
> Now, I have a weird problem. I'm trying to add 3 Contributors to a
> Show. 2 already are mapped (one will stay, one will not). Two have
> to be created new and added....
>
> To create the first new contributor, we happen toneed to add a new
> agent :
>
> 2947 TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 16400155 INSERT INTO InventoryItem (uuid,
> itemType, name, coId, publicId, damId, ownerId, creationDate,, ...
> 2948 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 11750977 SELECT LAST_INSERT_ID()
> 2949 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 10285791 INSERT INTO Agent (id, commonName)
> VALUES (?, ?) [params=(int) 194591, (String) Geir Magnusson Jr.]
>
> so there it creates a new Agent by making the InventoryItem first,
> getting the autogen ID, and using that to add to the Agent table.
>
> Then it makes a new contributor, using that Agent (id=194591) :
>
> 2959 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 14647551 INSERT INTO InventoryItem (uuid,
> itemType, name, coId, publicId, damId, ownerId, creationDate, ...
> 2997 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 1112653 SELECT LAST_INSERT_ID()
> 2997 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 4592108 INSERT INTO Contributor (id, roleCode,
> agentId) VALUES (?, ?, ?) [params=(int) 194592, (int) 109, (int)
> 194591]
>
> Fine - so we have a new contributor, id = 194592, with the new agent
> (id=194591)
>
> Now, remove from the mapping table the Contributor that we don't
> need :
Sorry - clearly this is just dumping the old mapping entries for the
show, id = 13163.
>
> 3094 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 15955745 DELETE FROM Show2Contributor WHERE
> showId = ? [params=(int) 13163]
>
> Now create another Contributor (using an agent we already have) :
>
> 3096 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 15187341 INSERT INTO InventoryItem (uuid,
> itemType, name, coId, publicId, damId, ownerId, creationDate,...
> 3122 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 9887497 SELECT LAST_INSERT_ID()
> 3123 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 9788885 INSERT INTO Contributor (id, roleCode,
> agentId) VALUES (?, ?, ?) [params=(int) 194593, (int) 109, (int)
> 194573]
>
> So that's the 2nd new Contributor (id=194593)
>
> To review - two new contributors : 194592 and 194593. The third is
> an old, existing one (happens to be 13163)
>
> Now, OpenJPA then updates a lastModDate for each of the three :
>
This is an update of the show we're going to map to.
> 3125 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 12844136 UPDATE InventoryItem SET
> lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
> 23:45:48.924, (int) 13163]
>
> 3127 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 13915734 UPDATE InventoryItem SET
> lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
> 23:45:48.989, (int) 194591]
> 3128 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 10653126 UPDATE InventoryItem SET
> lastModificationDate = ? WHERE id = ? [params=(Timestamp) 2008-01-02
> 23:45:48.989, (int) 194592]
>
> And now seems to screw up adding to the Show2Contributor table - it
> adds one of them twice (194593), and skips one (194592).
>
> 3129 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 8097602 INSERT INTO Show2Contributor (showId,
> contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194575]
> 3131 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 9028526 INSERT INTO Show2Contributor (showId,
> contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194593]
> 3132 pu TRACE [main] openjpa.jdbc.SQL - <t 8736201, conn 6321587>
> executing prepstmnt 11857123 INSERT INTO Show2Contributor (showId,
> contributorId) VALUES (?, ?) [params=(int) 13163, (int) 194593]
>
> This leads to a contraint violation, and the whole thing rolls back.
>
> Um.
>
> Help? :) This happens for both a 1.1.0-SNAPSHOT as well as 1.0.1
> release.
>
> TIA
>
> geir
>
>
>