Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » PersistenceException upon flush() - constraint violation(PersistenceException during em.flush() - Abort due to constraint violation (column ID is not unique))
PersistenceException upon flush() - constraint violation [message #949196] Thu, 18 October 2012 15:55 Go to next message
Julie Schenk is currently offline Julie Schenk
Messages: 18
Registered: August 2012
Junior Member
Hello.

My question at the end is: I found a solution - does it seem like an acceptable solution?

Issue:
Profile.java has the following:
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column
private long id;
...

and is included by Plant.java
@OneToOne(cascade = CascadeType.PERSIST)
private Profile profile;

A Profile must exist before a Plant can be created with one (enforced by the code). Therefore, when the Profile is persisted by JPA I receive this error:

2012-10-17 16:48:30,337 WARN [AWT-EventQueue-0] - -- UnitOfWork(24490984)
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: [SQLITE_CONSTRAINT] Abort due to constraint violation (column ID is not unique)
Error Code: 0
Call: INSERT INTO PROFILE (ID, NAME, CONVERGENCECRITERION, INITIALENRICHMENT, POWER, POWEREXPONENT, TRAMPBURNUP, XE133CLEANUPRATE) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
bind => [8 parameters bound]
Query: InsertObjectQuery(Westinghouse)
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:851)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:913)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:594)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:537)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1800)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:286)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:342)
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:162)
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:177)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:471)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(Datab
.... etc.

I found (after considerable digging and trying various solutions) a link that suggested I remove the 'not null' from my primary key ID in Profile. Since the post was not 100% clear to me I am wondering if anyone else has heard of this and/or if it makes sense.

Here is a piece of the post, and by the way, it solved my issue!

While checking every thing from scratch found that thee where condition enforced on the column of table NOT NULL, like id was PRIMAY KEY AUTO INCREMENT NOT NULL. This was the cause of the issue , as I wanted the SQLite to generate id for me but also had said you require an id field for an insertion to complete. The same problem was with some other column.
So I decided to keep some guideline for dealing with SQLite and iPhone connections.
1. Never create primary key column of table with both the restrictions ON (TUE) , that is AUTO INCREMENT NOT NULL. This rule can ignored, if you want to manage your own primary key creation.

Feedback appreciated.

This only seems to happen for the integer-type PK ID fields in my case- probably b/c of the @GeneratedValue annotation.
Re: PersistenceException upon flush() - constraint violation [message #949213 is a reply to message #949196] Thu, 18 October 2012 16:16 Go to previous messageGo to next message
Julie Schenk is currently offline Julie Schenk
Messages: 18
Registered: August 2012
Junior Member
It turns out that did NOT resolve the issue at all. I have the same problem. Am I missing some fundamental understanding of something? I know I have to have the CascadeType and yet when it persists I have a problem b/c the Profile already exists and I get a duplicate entry error. I don't want to insert a new Profile...
Re: PersistenceException upon flush() - constraint violation [message #949261 is a reply to message #949213] Thu, 18 October 2012 17:17 Go to previous messageGo to next message
Julie Schenk is currently offline Julie Schenk
Messages: 18
Registered: August 2012
Junior Member
I found another post where someone did something like this:

try {
entityManager.flush();
} catch (PersistenceException pe) {
logger.debug(pe.getLocalizedMessage());
}
try {
entityManager.getTransaction().commit();
} catch (RollbackException rbe) {
logger.debug(rbe.getLocalizedMessage());
}

That seems odd to me- why the "extra" insert?????
Re: PersistenceException upon flush() - constraint violation [message #950031 is a reply to message #949261] Fri, 19 October 2012 11:07 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1017
Registered: July 2009
Senior Member
I'm not sure where you read that 'not null' should be removed, but I don't think it is applicable here - the constraint having a problem is the unique constraint, and a primary key shouldn't be null anyway.

My guess based on the reference having only cascade persist on it is that you are referencing an existing but detached Profile entity in a new Entity and calling persist on it. This causes the persist to cascade to the detached Profile, and results in it being inserted. If you do not want it inserted, use a managed instance: find it from the context and use it in the reference instead of your detached instance.

If there are changes in the Profile you want to merge to the database as well, you might use the merge api on Profile instead of find, and use the returned managed copy in the profile reference. Or you can set the cascade merge option on the relationship and use merge instead of persist. I prefer using managed instances where possible instead of relying on cascading merge over relationships, but you have options that might fit with your application's design.

Best Regards,
Chris
Re: PersistenceException upon flush() - constraint violation [message #950376 is a reply to message #950031] Fri, 19 October 2012 17:24 Go to previous messageGo to next message
Julie Schenk is currently offline Julie Schenk
Messages: 18
Registered: August 2012
Junior Member
I will investigate and report back. Thank you for your reply.
Re: PersistenceException upon flush() - constraint violation [message #954153 is a reply to message #950031] Mon, 22 October 2012 16:48 Go to previous messageGo to next message
Julie Schenk is currently offline Julie Schenk
Messages: 18
Registered: August 2012
Junior Member
Agree that I am referencing an existing but detached Profile entity in a new Plant Entity and calling persist on it.

Setting cascade=CASCADE_TYPE.MERGE on Profile seems to handle the situation:
@OneToOne(cascade = CascadeType.MERGE)
private Profile profile;

I'm certainly willing to use a managed instance but I"m not sure I can get it from the context. Sometimes the user is opening the application up and just creating a new Plant and the Profile already exists. It's not as if the application was running and I closed or cleared the EntityManager.

In this suggestion: "use a managed instance, find it from the context and use it in the reference instead of your detached instance" I'm not clear on if that means to actually make a call like em.find().?. If so, at that juncture I only have the Plant object not the Profile so I"m not sure I can do that. If not, I'd appreciate any additional details.

Thanks.
Re: PersistenceException upon flush() - constraint violation [message #956755 is a reply to message #954153] Wed, 24 October 2012 15:35 Go to previous messageGo to next message
Julie Schenk is currently offline Julie Schenk
Messages: 18
Registered: August 2012
Junior Member
As it turns out, CascadeType.MERGE does not resolve my problem b/c now PERSIST is not satisfied. I cannot use CascadeType.ALL b/c I do not want REMOVE.

These new internal (non-portable?) DatabaseException and PersistenceException exceptions are new to EclipseLink 2.4.0 as far as I can tell.

Any additional information will be greatly appreciated.
Re: PersistenceException upon flush() - constraint violation [message #957679 is a reply to message #956755] Thu, 25 October 2012 08:05 Go to previous message
Julie Schenk is currently offline Julie Schenk
Messages: 18
Registered: August 2012
Junior Member
I was wrong- has nothing to do with the EclipseLink version.

I think I found the issue. I was calling em.clear() after an em.remove() which is why my profile was detached. When I remove the em.clear() call, the exceptions are gone. I believe Chris had it correct from the start and I just took a long time to figure out how it was all working in my code.

The Entity Object Life Cycle diagram I've attached helped me a lot too.

Sorry for all the posts.

Previous Topic:XmlReadTransformer only for a read-only mapping
Next Topic:Exception when using transformer on a accessor
Goto Forum:
  


Current Time: Thu Aug 21 16:14:45 EDT 2014

Powered by FUDForum. Page generated in 0.02128 seconds