com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '5' for key 'PRIMARY'

Question

Firtst of all, I added ten records into my databases manually. Then I ran the project, it went wrong like this: Duplicate entry '1' for key 'PRIMARY'. Then I refreshed the page nine times, it shown from "Duplicate entry '2' for key 'PRIMARY'" to "Duplicate entry '10' for key 'PRIMARY". Finally, the project became normal, which means I can insert item into the database from the eleven record on. What's wrong?

I use the hibernate in verison 5.10 final. And I will show my code block in the following:

My confusion is that the table created by the project did not set for auto_increatement. as following: pictures this is a picture

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">

        <property name="dataSource" ref="dataSource"/>
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"/>
        </property>

        <property name="packagesToScan" value="com"/>

        <property name="jpaProperties">
            <props>
                <prop key="hibernate.implicit_naming_strategy">legacy-jpa</prop>

                <prop key="hibernate.format_sql">true</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>
                <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</prop>

                <prop key="hibernate.cache.use_query_cache">true</prop>
                <prop key="hibernate.cache.use_second_level_cache">true</prop>
                <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
            </props>
        </property>

        <property name="sharedCacheMode" value="ENABLE_SELECTIVE"/>
    </bean>

import org.hibernate.annotations.GenericGenerator;
import org.springframework.format.annotation.DateTimeFormat;

import javax.persistence.*;
import java.util.Date;


@Table(name = "sssp_empoyee")
@Entity
public class Employee {

    private Integer id;
    private String lastName;
    private String email;

    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date birth;
    private Date crateTime;
    private Department department;

    @GeneratedValue(generator = "system-uuid")
    @GenericGenerator(name = "system-uuid", strategy = "increment")
    @Id
    public Integer getId() {

        return id;
    }


@Transactional
public void saveEmployee(Employee employee) {

    employee.setCrateTime(new Date());

    respository.saveAndFlush(employee);
}

error info:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException    : Duplicate entry '5' for key 'PRIMARY'
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
sun.reflect.DelegatingConstructorAccessorImpl.__newInstance(DelegatingConstructorAccessorImpl.java:45)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java)
java.lang.reflect.Constructor.newInstance(Constructor.java:423)
com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
com.mysql.jdbc.Util.getInstance(Util.java:381)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2046)
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1964)
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1949)
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:147)
org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2921)
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3421)
org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89)
org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:560)
org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:434)
org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)
org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1295)
org.hibernate.jpa.spi.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:1300)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.DelegatingMethodAccessorImpl.__invoke(DelegatingMethodAccessorImpl.java:43)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java)
java.lang.reflect.Method.invoke(Method.java:498)
org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344)
com.sun.proxy.$Proxy35.flush(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.DelegatingMethodAccessorImpl.__invoke(DelegatingMethodAccessorImpl.java:43)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java)
java.lang.reflect.Method.invoke(Method.java:498)

I reckon your id generator always starts at 0 again when you restart you project, could that be? — Herr Derb, Apr 13 '16 at 08:15
The original datas which I add into database manually are begin from 1. It seems that the project has its own ids, but not according to the database's id. I have set the id auto increatment. I don't know how to resolve this issue. — JSO, Apr 13 '16 at 08:23

score 0 · Answer 1 · edited May 23 '17 at 12:24

0

@GeneratedValue(strategy=GenerationType.AUTO)

I think you need to use strategy as AUTO

Check this SO answer

edited May 23 '17 at 12:24

Community

1
1

answered Apr 13 '16 at 08:30

Ankit Deshpande

3,476
1
29
42

Thank you! yes, I use this method very fisrt, then it went wrong, so I changed for that. Finally, I found that the tables created by project did not set AUTO_Increatement, its checkbox is false. – JSO Apr 13 '16 at 08:41

score 0 · Answer 2 · edited Jan 19 '17 at 10:45

0

Use @GeneratedValue(strategy = GenerationType.IDENTITY)

edited Jan 19 '17 at 10:45

naXa stands with Ukraine

35,493
19
190
259

answered Apr 13 '16 at 09:25

KayV

12,987
11
98
148

score 0 · Answer 3 · edited Jul 08 '17 at 16:53

0

Modify your table structure, add auto_increment to the id of the column in your table, using the query below:

ALTER TABLE document MODIFY COLUMN document_id INT auto_increment

edited Jul 08 '17 at 16:53

moondaisy

4,303
6
41
70

answered Jul 08 '17 at 14:16

Gautam

21
1
1
3

score -1 · Answer 4 · edited Jan 19 '17 at 10:44

MySql works On setting like this:

@GeneratedValue(strategy = GenerationType.IDENTITY)

then it will create a table with the function of auto_increment. But the default setting @GeneratedValue won't set increment for tables. So that't the key.

Now no matter how many original records in my database, it won't go wrong with the error info of "Duplicate entry ...." when I insert a new entry into database.

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '5' for key 'PRIMARY'

4 Answers4