Home » Eclipse Projects » EclipseLink » MySQLIntegrityConstraintViolationException when updating @ManyToMany @JoinTable @OrderColumn
| MySQLIntegrityConstraintViolationException when updating @ManyToMany @JoinTable @OrderColumn [message #822311] |
Fri, 16 March 2012 13:05  |
 Karsten Wutzke
Messages: 124
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:
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 #822653 is a reply to message #822382] |
Sat, 17 March 2012 03:32  |
Karsten Wutzke
Messages: 124
Registered: July 2009 |
Senior Member |
|
|
Chris Delahunt wrote on Fri, 16 March 2012 10:43Can 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:43While 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 |
|
| |
Goto Forum:
Current Time: Fri Apr 19 23:59:32 GMT 2024
Powered by FUDForum. Page generated in 0.03043 seconds |
] 
Search
Help
Register
Login
Home
