search

eclipse-ee4j / eclipselink

Eclipselink project
https://eclipse.dev/eclipselink/
Other
198 stars 168 forks source link

EclipseLink does not throw EntityExistsException as expected for PostgreSQL and SQLServer #2170

Closed KyleAure closed 2 months ago

KyleAure commented 3 months ago

Describe the bug When attempting to persist an entity that already exists in the database we are using the entity manager persist method. We attempt to catch an EntityExistsException, but EclipseLink throws a org.eclipse.persistence.exceptions.DatabaseException This seems to be contrary to the Jakarta Persistence javadoc for the persist() method.

Entity:

public record Receipt(long purchaseId, String customer, float total) {}

Entity Manager Usage:

em.persist(receipt);
em.flush();
Exception Stack ``` Caused by: jakarta.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.2.v202306161219): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__WLPRecei__F32854EDD3466F3A'. Cannot insert duplicate key in object 'dbo.WLPReceiptEntity'. The duplicate key value is (1200). Error Code: 2627 Call: INSERT INTO WLPReceiptEntity (PURCHASEID, CUSTOMER, TOTAL) VALUES (?, ?, ?) bind => [3 parameters bound] Query: InsertObjectQuery(test.jakarta.data.web.ReceiptEntity@f0f03b7) at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush(EntityManagerImpl.java:967) at io.openliberty.data.internal.persistence.RepositoryImpl.insert(RepositoryImpl.java:606) at io.openliberty.data.internal.persistence.RepositoryImpl.invoke(RepositoryImpl.java:762) Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.2.v202306161219): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__WLPRecei__F32854EDD3466F3A'. Cannot insert duplicate key in object 'dbo.WLPReceiptEntity'. The duplicate key value is (1200). Error Code: 2627 Call: INSERT INTO WLPReceiptEntity (PURCHASEID, CUSTOMER, TOTAL) VALUES (?, ?, ?) bind => [3 parameters bound] Query: InsertObjectQuery(test.jakarta.data.web.ReceiptEntity@f0f03b7) at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:334) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:919) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:981) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:642) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:569) at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2048) at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:311) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:280) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:266) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:436) at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:193) at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:209) at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:514) at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:86) at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:97) at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:326) at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:61) at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:913) at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:812) at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:109) at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:86) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3025) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1841) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1823) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1773) at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:248) at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:147) at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4335) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1515) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithPreBuiltChangeSet(UnitOfWorkImpl.java:1661) at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.writeChanges(RepeatableWriteUnitOfWork.java:472) at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush(EntityManagerImpl.java:962) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__WLPRecei__F32854EDD3466F3A'. Cannot insert duplicate key in object 'dbo.WLPReceiptEntity'. The duplicate key value is (1200). at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:473) at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(WSJdbcPreparedStatement.java:522) ```

To Reproduce Steps/resources to reproduce the behavior:

Expected behavior I would expect the persist() method to throw an EntityExistsException

Additional context It seems what makes PostgreSQL and SQLServer unique is that both JDBC drivers fail to throw a SQLIntegrityConstraintViolationException as specified by the JDBC 4.1 specification. Both drivers have open issues for this:

But both of these drivers do not seem to be interested in implementing this exception. EclipseLink should handle this in a way that results in an EntityExistsException for consistent behavior between JDBC drivers.

AleksNo commented 3 months ago

Java records are not valid entities. So the results are implementation specific and may vary. Try classes instead of records.

KyleAure commented 3 months ago

@AleksNo Same thing happens with classes (I just put in the record for brevity)

AleksNo commented 3 months ago

OK, i have read the description of this Exception: https://docs.oracle.com/javaee%2F6%2Fapi%2F%2F/javax/persistence/EntityExistsException.html

"Thrown by the persistence provider when EntityManager.persist(Object) is called and the entity already exists."

I guess, it is meant, that if there is already a managed entity with an ID and you try to persist a detached entity with the same ID then the exception will be thrown. But if there is no managed entity (in the EntityManager context) with the ID and you try to persist a detached entity with this ID then Eclipselink creates an INSERT statement and throws it against the database. And it fails because the database server blocks it because you cannot insert a second row with the same ID.

So in your case: use EntityManager.merge(Object) instead.

rfelcman commented 3 months ago

Java records are not valid entities. So the results are implementation specific and may vary. Try classes instead of records.

It is part of Jakarta Persistence 3.2 https://deploy-preview-652--jakartaee-specifications.netlify.app/specifications/persistence/3.2/jakarta-persistence-spec-3.2-draft01#a487 2.6. Embeddable Classes

Improvement into EclipseLink 5.0 is in progress https://github.com/eclipse-ee4j/eclipselink/pull/2163

KyleAure commented 3 months ago

But if there is no managed entity (in the EntityManager context) with the ID and you try to persist a detached entity with this ID then Eclipselink creates an INSERT statement and throws it against the database. And it fails because the database server blocks it because you cannot insert a second row with the same ID.

@AleksNo When I run this same test against Derby, DB2, or Oracle an EntityExistsException is thrown.

For more context here is the full test (from a Jakarta Data level):

Receipt r = receipts.insert(new Receipt(1200L, "C0002-12-002", 102.20f));
try {
    receipts.insert(new Receipt(1200L, "C0002-10-002", 22.99f));
    fail("Inserted an entity with an Id that already exists.");
} catch (EntityExistsException x) {
    // expected
}

Where insert does em.persist(r); em.flush(); I'm not an expert in Jakarta Persistence but it seems wrong for some databases to throw EntityExistsException and others throw DatabaseException. Especially since persist(object) javadoc does not mention any other exceptions being thrown for this type of situation.

I'm working on the Jakarta Data implementation for Open Liberty and without consistent behavior from the underlying entity manager we cannot provide the correct exceptions from our layer.

KyleAure commented 2 months ago

When attempting to re-create this issue only using JPA API + Eclipselink I did find that in all cases Eclipselink does only throw EntityExistsException during em.persist() and PersistenceException during tx.commit()

Sorry for the misunderstanding I think my original testing showed a stack trace with line numbers that were offset from my current dev environment which resulted in me thinking we were missing a catch(EntityExistsException x) block.

Thanks for taking the time to review this issue.