Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » MySQLIntegrityConstraintViolationException when updating @ManyToMany @JoinTable @OrderColumn
MySQLIntegrityConstraintViolationException when updating @ManyToMany @JoinTable @OrderColumn [message #822311] Fri, 16 March 2012 13:05 Go to next message
Karsten Wutzke is currently offline Karsten WutzkeFriend
Messages: 112
Registered: July 2009
Senior Member
I have a many-to-many relationship between `Prequalification` and `Company` entities (`Partnership`). The DDL for the three tables is:
CREATE TABLE Prequalifications
(
  id              INTEGER      NOT NULL IDENTITY,
  user_id         INTEGER      NOT NULL,
  name            VARCHAR(100) NOT NULL,
  
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES Users (id)
);
    
CREATE TABLE Partnerships
(
  prequalification_id INTEGER            NOT NULL,
  company_id          INTEGER            NOT NULL,
  ordinal_nbr         SMALLINT DEFAULT 0 NOT NULL,
  
  PRIMARY KEY (prequalification_id, company_id),
  FOREIGN KEY (prequalification_id) REFERENCES Prequalifications (id),
  FOREIGN KEY (company_id) REFERENCES Companies (id),
  UNIQUE (prequalification_id, ordinal_nbr)
);
    
CREATE TABLE Companies
(
  id       INTEGER     NOT NULL,
  dnd_type VARCHAR(10) NOT NULL,
  
  PRIMARY KEY (id),
  FOREIGN KEY (id) REFERENCES Organizations (id) -- just an inherited ID, never mind!
);

Please note the unique constraint on `Partnerships`'s PQ ID and ordinal number: there can be only one position of a company per PQ.

This got mapped in `Prequalification` as `@ManyToMany` + `@JoinTable` including an `@OrderColumn` for the order of the companies per PQ (`Partnerships`'s order column is ordinal_nbr):
@Entity
@Table(name = "Prequalifications")
public class Prequalification implements Serializable
{
    ...

    @ManyToMany
    @JoinTable(name = "Partnerships", joinColumns = @JoinColumn(name = "prequalification_id", referencedColumnName = "id", insertable = false, updatable = false), inverseJoinColumns = @JoinColumn(name = "company_id", referencedColumnName = "id", insertable = false, updatable = false))
    @OrderColumn(name = "ordinal_nbr", nullable = false)
    private List<Company> companies;

    ...
}

Here's the update method which is called from the GUI. Note, that the list on the GUI only includes external companies and that the internal company is (supposed to be) at index zero always:
@ManagedBean
@ViewScoped
public class PqHome implements DropListener, Serializable
{
    ...
    
    private Prequalification pq;
    private Integer userId;
    private List<Company> participatingCompanies; // xetters omitted!
    
    ...
    
    public void update()
    {
        // assign new owner
        pq.setUser(userService.findSingleUser(userId));
        
        Company internal = companyManager.getInternalCompany();
        
        // find internal company
        int index = participatingCompanies.indexOf(internal);
        
        // if internal company missing or at non-zero index: we need to repair this
        if ( index != 0 )
        {
            // if internal exists at some wrong place, remove it
            if ( index > 0 )
            {
                participatingCompanies.remove(index);
            }
            
            // (re-)add at index zero
            participatingCompanies.add(0, internal);
        }		
        
        pq.setCompanies(participatingCompanies);
        
        // update and get *new* merged instance
        pq = pqService.update(pq);
        
        participatingCompanies = null;
        init(); // some not so important (re-)initialization...
    }

    ...
}

In the client JSF page the field `participatingCompanies` is used like:
<rich:pickList value="#{pqHome.participatingCompanies}"
               var="company"
               converter="#{companyConverter}"
               orderable="true"
               sourceCaption="Available companies"
               targetCaption="Selected companies">
  <f:selectItems value="#{companyManager.externalCompanies}" />
  <rich:column>#{company.name}</rich:column>
</rich:pickList>

Don't be intimidated RichFaces component. The list referencing `#{pqHome.participatingCompanies}` just contains optional (external) companies:

index.php/fa/7564/0/

When I hit the update button (not shown), the `update` method on the `PqHome` bean is called. When executing the code on GlassFish 3.1.2 using EclipseLink 2.3.2 the following exception is thrown:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '2-2' for key 'partnerships_multi_uq'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
    at com.mysql.jdbc.jdbc2.optional.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:875)
    at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:831)
    ... 128 more

This exception is duplicated several times in the log. Other PQ's have the same problem, just involve other unique column combinations, e.g. '1-1'.

What's wrong with the update code? It works without problems on Hibernate.

Thanks
Karsten

[Updated on: Fri, 16 March 2012 13:53]

Report message to a moderator

Re: MySQLIntegrityConstraintViolationException when updating @ManyToMany @JoinTable @OrderColumn [message #822382 is a reply to message #822311] Fri, 16 March 2012 14:43 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
Can you turn on logging and parameter logging, delay constraint processing until the end of the transaction (or turn them off) and show the statements EclipseLink logs for the transacation? This will show if it is an ordering problem or something to do with replacing the collection in the pq entity rather than modifying the collection directly.
While it may not be related, is there a reason wny you have marked the join columns as insertable = false, updatable = false in the ManyToMany? Does this join table never get inserted into?

What I'm not sure of is how you would expect this to work. Since you have marked some fields as insertable/updatable=false, it means they cannot be deleted and re-added in. So that leaves the only way to change ordering is by updating the order column. There isn't much of a way to do that without overlapping order values at some point, so you may have to remove the UNIQUE constraint or process constraints only at the end of the transaction.

Best Regards,
Chris
Re: MySQLIntegrityConstraintViolationException when updating @ManyToMany @JoinTable @OrderColumn [message #822653 is a reply to message #822382] Sat, 17 March 2012 03:32 Go to previous messageGo to next message
Karsten Wutzke is currently offline Karsten WutzkeFriend
Messages: 112
Registered: July 2009
Senior Member
Chris Delahunt wrote on Fri, 16 March 2012 10:43
Can you turn on logging and parameter logging, delay constraint processing until the end of the transaction (or turn them off) and show the statements EclipseLink logs for the transacation? This will show if it is an ordering problem or something to do with replacing the collection in the pq entity rather than modifying the collection directly.

How exactly do you do this? Is this in GlassFish or EclipseLink? I'm new to GlassFish and haven't done much debugging using EclipseLink. The only options I know are

<property name="eclipselink.logging.level" value="FINE" />
<property name="eclipselink.logging.level.sql" value="FINE" />


in the persistence.xml.

Chris Delahunt wrote on Fri, 16 March 2012 10:43
While it may not be related, is there a reason wny you have marked the join columns as insertable = false, updatable = false in the ManyToMany? Does this join table never get inserted into?

What I'm not sure of is how you would expect this to work. Since you have marked some fields as insertable/updatable=false, it means they cannot be deleted and re-added in. So that leaves the only way to change ordering is by updating the order column. There isn't much of a way to do that without overlapping order values at some point, so you may have to remove the UNIQUE constraint or process constraints only at the end of the transaction.

I see your points about the mappings, I'm not sure if they were meant that way. Looking at them now they don't make much sense to me. However, this example clearly makes the join table columns read-only: http://en.wikibooks.org/wiki/Java_Persistence/ManyToMany#Example_join_table_association_object_annotations.

Retrying this without insertable = false, updatable = false made no difference. The question is which one is right...

In any case, I'd have to go the logging way to figure out the update problem. Where/how do I perform the configs you mentioned?

Karsten

[Updated on: Mon, 19 March 2012 11:02]

Report message to a moderator

Re: MySQLIntegrityConstraintViolationException when updating @ManyToMany @JoinTable @OrderColumn [message #824285 is a reply to message #822653] Mon, 19 March 2012 13:48 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
The example you posted iis mapping an Entity to the join table, and is showing a ManyToOne where the foreign key happens to also be the primary key with its own writable basic mapping. So it needs to set the joincolumn as insertable = false, updatable = false or there would be two mappings using the EMPLOYEEID field.

Logging is described here: http://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging
I believe glassfish uses java.util.logging so you may need to set the default logger to use EclipseLink log level settings.

Best Regards,
Chris

[Updated on: Mon, 19 March 2012 13:52]

Report message to a moderator

Previous Topic:OptimisticLocking selected columns gets last updated values
Next Topic:missing commit
Goto Forum:
  


Current Time: Fri Dec 19 11:46:10 GMT 2014

Powered by FUDForum. Page generated in 0.02497 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software