Re: weird problem w/ a mapping table

[Geir Magnusson Jr.]

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. <[EMAIL PROTECTED]> 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