"Duplicate entry ... for key PRIMARY" when inserting both "Krakow" and "Kraków"

Question

I have an Entity LocationsCoordinates for saving coordinates of locations in a database with unique location and country (making a composite primary key) which is defined as below:

@Entity
@Table(name = "LOCATIONS_COORDINATES")
@IdClass(LocationsCoordinatesPK.class)
public class LocationsCoordinates  implements Serializable {

    private static final long serialVersionUID = -4580217081464519853L;

    @Id
    @Column(name = "LOCATION")
    String location;

    @Id
    @Column(name = "COUNTRY")
    String country;

    @Column(name = "LATITUDE")
    Double latitude;

    @Column(name = "LONGITUDE")
    Double longitude;

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    public Double getLatitude() {
        return latitude;
    }

    public void setLatitude(Double latitude) {
        this.latitude = latitude;
    }

    public Double getLongitude() {
        return longitude;
    }

    public void setLongitude(Double longitude) {
        this.longitude = longitude;
    }

}

--

public class LocationsCoordinatesPK implements Serializable 
{   
    private static final long serialVersionUID = -4675306358956719620L;

    String location;

    String country;

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }
}

while batch insert in jpa of locations in utf-8 character set with coordinates, for example

public class SampleDAO {

    protected EntityManager entityManager;

    @PersistenceContext
    public void setEntityManager(EntityManager entityManager) {
        this. entityManager = entityManager;
    }

public void saveCoordinates(){
ArrayList<LocationsCoordinates> coordinates = new ArrayList<LocationsCoordinates>();

LocationsCoordinates coordinate = new LocationsCoordinates();
coordinate.setLocation("Krakow");
coordinate.setCountry("Poland");
coordinate.setLatitude(25.25817);
coordinate.setLongitude(55.30472);
coordinates.add(coordinate);

coordinate = new LocationsCoordinates();
coordinate.setLocation("Kraków");
coordinate.setCountry("Poland");
coordinate.setLatitude(25.25817);
coordinate.setLongitude(55.30472);
coordinates.add(coordinate);
save(coordinates);
}

@Transactional
public <LocationsCoordinates> List<LocationsCoordinates > save(List<LocationsCoordinates > list) {
    List<LocationsCoordinates > returnList = new ArrayList<LocationsCoordinates >();
        for(LocationsCoordinates  ob : list){
            returnList.add(entityManager.merge(ob));
        }
        return returnList;
    }
}
}

On calling save function, I am getting the following exception

2013-10-09 19:42:41,892 [ERROR ][pool-1-thread-1] JDBCExceptionReporter - Duplicate entry 'Poland-KrakÃ³w' for key 'PRIMARY'
2013-10-09 19:42:41,984 [ERROR ][pool-1-thread-1] TaskUtils$LoggingErrorHandler - Unexpected error occurred in scheduled task.
org.springframework.dao.DataIntegrityViolationException: could not insert: [com.reppify.core.dto.LocationsCoordinates]; SQL [insert into LOCATIONS_COORDINATES (LATITUDE, LONGITUDE, COUNTRY, LOCATION) values (?, ?, ?, ?)]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not insert: [com.reppify.core.dto.LocationsCoordinates]
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:643)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:104)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:516)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:392)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy28.save(Unknown Source)
    at com.reppify.accenture.scoring.ScoreConnections.scoreConnections(ScoreConnections.java:308)
    at com.reppify.parser.job.accenture.JobLoader.synchronizeJob(JobLoader.java:155)
    at com.reppify.parser.job.accenture.JobLoader.startJobLoader(JobLoader.java:51)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:64)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:53)
    at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: org.hibernate.exception.ConstraintViolationException: could not insert: [com.reppify.core.dto.LocationsCoordinates]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:96)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2454)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2874)
    at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383)
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)
    at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:76)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:512)
    ... 25 more
Caused by: java.sql.BatchUpdateException: Duplicate entry 'Poland-KrakÃ³w' for key 'PRIMARY'
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2024)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
    at org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:56)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2434)
    ... 37 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'Poland-KrakÃ³w' for key 'PRIMARY'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    at com.mysql.jdbc.Util.getInstance(Util.java:382)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1976)
    ... 41 more

As "Krakow" and "Kraków" are different string values, so these are not be considered duplicate but as we can see in exception these are considered as same value. please help.

Is the key column an 'NVARCHAR'? – StuartLC Oct 10 '13 at 06:31 — StuartLC, Oct 10 '13 at 06:31
No, the keys column's datatype is VARCHAR(255) – Rohit Srivastava Oct 10 '13 at 06:36 — Rohit Srivastava, Oct 10 '13 at 06:36

score 6 · Accepted Answer · edited May 23 '17 at 12:08

6

Try to use ~~utf8_general_ci~~utf8_bin as collation for the column in MySQL.

This doesn't seem to be a Java problem, but a MySQL problem.

Find more about collations here

This is the correct CREATE TABLE

CREATE TABLE `locations_coordinates` (
  `COUNTRY` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `LOCATION` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `LATITUDE` double DEFAULT NULL,
  `LONGITUDE` double DEFAULT NULL,
  PRIMARY KEY (`COUNTRY`,`LOCATION`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

edited May 23 '17 at 12:08

Community

1
1

answered Oct 10 '13 at 06:30

Stefan

2,028
2
36
53

I tried utf8_general_ci as collation on the table but it also gives same error. – Rohit Srivastava Oct 10 '13 at 06:34
1

@RohitSrivastava: Did you use it as collation for that specific field? Give us the `CREATE TABLE` please. – Stefan Oct 10 '13 at 07:00
Yeah I used utf8_general_ci collation for location and country. – Rohit Srivastava Oct 10 '13 at 07:26
create table statement:- CREATE TABLE `locations_coordinates` ( `COUNTRY` varchar(255) NOT NULL, `LOCATION` varchar(255) NOT NULL, `LATITUDE` double DEFAULT NULL, `LONGITUDE` double DEFAULT NULL, PRIMARY KEY (`COUNTRY`,`LOCATION`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 – Rohit Srivastava Oct 10 '13 at 07:26
Thanks, tried it and found out that you must use `utf8_bin`, not `utf8_general_ci` - Updated answer with working `CREATE TABLE` – Stefan Oct 10 '13 at 07:42
1

Note that without `utf8_bin` even letter casing will be ignored, making `Krakow` and `KRAKOW` causing the same error too. – Arjan Oct 10 '13 at 07:55
@Arjan: Yes, this is right. Maybe Rohit could process the words before inserting, for example doing all uppercase to avoid that case. – Stefan Oct 10 '13 at 08:42
As for processing the entries before inserting: I'd say @Rohit's data model doesn't *truly* reflect the real world, as in the real world the duplicate entry is actually correct. ;-) – Arjan Oct 10 '13 at 08:57

"Duplicate entry ... for key PRIMARY" when inserting both "Krakow" and "Kraków"

1 Answers1