Correct way to prevent rollback of large transaction for simple primary key violations on a few of the many records?

[Aaron Daubman]

Greetings,

What is the correct way to allow MyBatis to commit a large batch insert operation where some of the values being inserted violate a primary key constraint?

e.g. primary key constraint violation should be ignored/logged and not cause the entire (very large) transaction to be rolled back.

Put another way, the data coming in may be 'dirty' in that some records are duplicated and can safely be ignored, while the many other valid records should still be preserved (committed).

Right now, I am sending a list with three objects, one of which has the same value for primary key field as a record already in the DB. 

This causes:

### Error committing transaction.  Cause: org.apache.ibatis.executor.BatchExecutorException: dataCore.batchInsertdataCore (batch index #1) failed. Cause: java.sql.BatchUpdateException: Duplicate entry '222' for key 'PRIMARY'

### Cause: org.apache.ibatis.executor.BatchExecutorException: dataCore.batchInsertdataCore (batch index #1) failed. Cause: java.sql.BatchUpdateException: Duplicate entry '222' for key 'PRIMARY'

The records that would have been inserted for the other two valid objects are 'lost' due to this one record failing.

Thanks once again,

     Aaron

[Aaron Daubman]

To add some further details:

I could simply switch to INSERT IGNORE, however, IIRC, this will _silently_ ignore primary key constraint violations, and I would still like to know about them. Is there a way to log/warn on them (as is done now through MyBatis) but NOT rollback the transaction as MyBatis does now for these errors?

On Sunday, April 8, 2012 1:09:48 AM UTC-4, Aaron Daubman wrote:

Greetings,

What is the correct way to allow MyBatis to commit a large batch insert operation where some of the values being inserted violate a primary key constraint?

e.g. primary key constraint violation should be ignored/logged and not cause the entire (very large) transaction to be rolled back.

Put another way, the data coming in may be 'dirty' in that some records are duplicated and can safely be ignored, while the many other valid records should still be preserved (committed).

Right now, I am sending a list with three objects, one of which has the same value for primary key field as a record already in the DB. 

This causes:

### Error committing transaction.  Cause: org.apache.ibatis.executor.BatchExecutorException: dataCore.batchInsertdataCore (batch index #1) failed. Cause: java.sql.BatchUpdateException: Duplicate entry '222' for key 'PRIMARY'

### Cause: org.apache.ibatis.executor.BatchExecutorException: dataCore.batchInsertdataCore (batch index #1) failed. Cause: java.sql.BatchUpdateException: Duplicate entry '222' for key 'PRIMARY'

The records that would have been inserted for the other two valid objects are 'lost' due to this one record failing.

Thanks once again,

     Aaron

[Jose María Zaragoza]

2012/4/8 Aaron Daubman <[hidden email]>:
> To add some further details:
>
> I could simply switch to INSERT IGNORE, however, IIRC, this will _silently_
> ignore primary key constraint violations, and I would still like to know
> about them. Is there a way to log/warn on them (as is done now through
> MyBatis) but NOT rollback the transaction as MyBatis does now for these
> errors?
>

Do you have tried to catch these exceptions and execute a commit()
when batch task is finished ?