How to do an UPSERT with Hibernate and MySQL and avoid throwing "Duplicate entry for key" issues

Dear Hibernate community,

I have a code like this

List<?> checkRmaShipperConstraint = em.createNativeQuery("SELECT * from ereturn where rma = :rma and shipper = :shipper", Tuple.class)
    .setParameter("rma", newEr.getRma())
    .setParameter("shipper", newEr.getShipper().getId())
    .getResultList();
	        	
if (checkRmaShipperConstraint.size() > 0){
    logger.severe('unique_index VIOLATED');
    System.exit(1)
}
	        	
try {
	        		
    em.persist(newEr);
	        		
}catch(Exception ex) {
    // Here I get an Exception --> java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'XXXXXXXXX-YYYYYYYY' for key 'unique_index'
}

And this is the hibernate logs:

03:16:17,713 DEBUG [org.hibernate.SQL] - 
    SELECT
        * 
    from
        ereturn 
    where
        rma = ? 
        and shipper = ?
Hibernate: 
    SELECT
        * 
    from
        ereturn 
    where
        rma = ? 
        and shipper = ?
03:16:17,713 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [2] as [BIGINT] - [148375]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [1] as [VARCHAR] - [NZR1767568]
03:16:17,714 DEBUG [org.hibernate.SQL] - 
    insert 
    into
        ereturn
        (barcode, carrier, carrier_label_request_id, carrier_shipment_id, consignee, creationtime, destination, destinationHub, disabled, dispatchedDate, failedReturnPOBoxPrivateBag, globalId, groupName, height, invoice, length, modifiedtime, notes, pickupDateTime, pickupDateTimeOffset, returnMethod, rma, scanDateTime, scanDateTimeDestHub, shipper, sourceHub, trackingNumber, width) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: 
    insert 
    into
        ereturn
        (barcode, carrier, carrier_label_request_id, carrier_shipment_id, consignee, creationtime, destination, destinationHub, disabled, dispatchedDate, failedReturnPOBoxPrivateBag, globalId, groupName, height, invoice, length, modifiedtime, notes, pickupDateTime, pickupDateTimeOffset, returnMethod, rma, scanDateTime, scanDateTimeDestHub, shipper, sourceHub, trackingNumber, width) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [1] as [VARCHAR] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [2] as [BIGINT] - [148380]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [3] as [VARCHAR] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [4] as [VARCHAR] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [5] as [BIGINT] - [298757]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [6] as [TIMESTAMP] - [2017-05-17T22:56:06]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [7] as [BIGINT] - [148375]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [8] as [BIGINT] - [216219]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [9] as [BOOLEAN] - [false]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [10] as [DATE] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [11] as [VARCHAR] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [12] as [VARCHAR] - [NZR1767568]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [13] as [VARCHAR] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [14] as [DOUBLE] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [15] as [VARCHAR] - [261238592]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [16] as [DOUBLE] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [17] as [TIMESTAMP] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [18] as [VARCHAR] - [460]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [19] as [TIMESTAMP] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [20] as [INTEGER] - [null]
03:16:17,714 TRACE [org.hibernate.type.EnumType] - Binding [DROPBOX] to parameter: [21]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [22] as [VARCHAR] - [NZR1767568]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [23] as [TIMESTAMP] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [24] as [TIMESTAMP] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [25] as [BIGINT] - [148375]
03:16:17,715 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [26] as [BIGINT] - [216218]
03:16:17,715 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [27] as [VARCHAR] - [null]
03:16:17,715 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [28] as [DOUBLE] - [null]
03:16:17,740 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - SQL Error: 1062, SQLState: 23000
03:16:17,740 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - Duplicate entry 'NZR1767568-148375' for key 'unique_index'

The code gives an error due to ‘unique_index’ constraint violation. I don’t understand how I got into this situation as I am checking that the new element does not exists by the select query before persist.

Any hint?

thank you very much

Most likely you need to do an UPSERT, which works atomically, unlike the SELECT-CHECK-INSERT anti-pattern.

Check out this article for more details.

Hi Vlad,

thank you very much for your answer, it really helped me to understand things a little bit more but still have a couple of questions.

  • Yes my code belongs to an ETL or batching process

  • My environment only has one client (which runs the ETL) and one server (web server/tomcat and database).

  • The client do calls to the server through a http request inside a simple loop

  • I am guessing I am not doing multi-threading yet my problem looks very similar.

  • Also I am using native queries to read if element already exists in the database (this should be accurate right?) but still persist fails due to a unique constraint violation.

  • I also tried nativequery instead of persist but the problem did not go away.

  • application managed entitymanager

I am just trying to understand a little bit deeper and I feel I still miss a few things

thank you very much

If you’re bootstrapping Hibernate natively, not via JPA, the native SQL queries won’t trigger a flush.

Check out this article for more details and solutions as well.