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

masber · February 23, 2019, 4:34pm

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

vlad · February 23, 2019, 6:54pm

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.

masber · February 23, 2019, 11:33pm

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

vlad · February 24, 2019, 6:39am

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.

Topic		Replies	Views
How to prevent Hibernate to making a column with unique = true automatically Hibernate ORM	12	4887	September 5, 2022
Duplicate key when using hibernate sequence generator Hibernate ORM	0	1655	April 1, 2019
org.hibernate.MappingException: property [id.companyId] not found on entity Hibernate ORM	6	2652	May 19, 2021
Cannot persist a new record due to IDENTITY_INSERT is set to OFF Hibernate ORM	4	12669	March 23, 2023
Hibernate 6 RowId issue Hibernate ORM	2	277	August 8, 2023

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

Related Topics