Hibernate primary key violation on two server instances

Question

I have an application which is running on two servers and both these servers access the same database via Hibernate. The issue here is when these two applications process large set of data and trying to insert to the same table. There is a primary key violation happens. And from the things I have noticed, hibernate seems to buffer some number of primary key ids. eg. If server A insert data into id 100 and at the same time server B will be inserting to id 200 or so. I guess this issue is related to above mentioned scenario. Following is my hibernate properties

        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/dbtest?autoReconnect=true</property>

    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">password</property>
    <!--        <property name="hibernate.show_sql">true</property>-->
    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <!-- 
    <property name="hibernate.hbm2ddl.auto">update</property>
     -->
    <property name="hibernate.use_sql_comments">true</property>
    <property name="hibernate.format_sql">true</property>
    <property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>
    <property name="hibernate.connection.isolation">2</property>
    <property name="hibernate.default_schema">dbtest</property>
    <property name="hibernate.jdbc.batch_size">50</property>
    <!--c3po connection pooling  -->
    <property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.timeout">300</property>
    <property name="hibernate.c3p0.max_statements">50</property>
    <property name="hibernate.c3p0.idle_test_period">3000</property>

This is the entity which is failing

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Sep 3, 2014 4:38:39 PM by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
    <class name="com.test.dto.AdditionalContacts" table="additional_contacts"
           entity-name="additionalcontacts">
        <id name="contactId" type="java.lang.Integer" column="contactId">
            <generator class="increment" />
        </id>
        <property name="contactGuid" type="java.lang.String">
            <column name="contactGuid" />
        </property>
        <property name="contactName" type="java.lang.String">
            <column name="contactName" />
        </property>
        <property name="salutation" type="java.lang.String">
            <column name="salutation" />
        </property>
        <property name="EmailAddress" type="java.lang.String">
            <column name="email" />
        </property>
        <property name="includeInEmails" type="java.lang.Boolean">
            <column name="includeInEmails" />
        </property>
        <property name="Website" type="java.lang.String">
            <column name="website" />
        </property>
        <property name="city" type="java.lang.String">
            <column name="city" />
        </property>
        <property name="state" type="java.lang.String">
            <column name="state" />
        </property>
        <property name="postCode" type="java.lang.String">
            <column name="postCode" />
        </property>
        <property name="country" type="java.lang.String">
            <column name="country" />
        </property>
        <property name="street" type="java.lang.String">
            <column name="street" />
        </property>
        <property name="streetLine1" type="java.lang.String">
            <column name="streetLine1" />
        </property>
        <property name="streetLine2" type="java.lang.String">
            <column name="streetLine2" />
        </property>
        <property name="streetLine3" type="java.lang.String">
            <column name="streetLine3" />
        </property>
        <property name="streetLine4" type="java.lang.String">
            <column name="streetLine4" />
        </property>
        <property name="phone1" type="java.lang.String">
            <column name="phone1" />
        </property>
        <property name="phone2" type="java.lang.String">
            <column name="phone2" />
        </property>
        <property name="phone3" type="java.lang.String">
            <column name="phone3" />
        </property>
        <property name="fax" type="java.lang.String">
            <column name="fax" />
        </property>
        <property name="location" type="java.lang.String">
            <column name="location" />
        </property>
        <property access="field" column="createDate" insert="true"
                  name="createDate" update="false" />
        <property access="field" column="modifyDate" insert="true"
                  name="modifyDate" update="true" />
    </class>
</hibernate-mapping>

And following is the entity which uses above entity

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Sep 3, 2014 4:38:39 PM by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
    <class name="com.test.dto.Contacts" table="contact"
           entity-name="contact">
        <id name="cGuid" type="java.lang.String">
            <column name="contactGuid" />
        </id>
        <property name="org" type="java.lang.Integer">
            <column name="org" />
        </property>
        <property name="Name" type="java.lang.String">
            <column name="name" />
        </property>
        <property name="FirstName" type="java.lang.String">
            <column name="firstName" />
        </property>
        <property name="LastName" type="java.lang.String">
            <column name="lastName" />
        </property>
        <property name="abn" type="java.lang.String">
            <column name="abn" />
        </property>
        <property name="active" type="java.lang.Boolean">
            <column name="active" />
        </property>
        <property access="field" column="createDate" insert="true"
                  name="createDate" update="false" />
        <property access="field" column="modifyDate" insert="true"
                  name="modifyDate" update="true" />

        <bag name="additionalContactList" table="additional_contact" lazy="false" inverse="true"
             cascade="all">
            <key>
                <column name="contactGuid" not-null="true" />
            </key>
            <one-to-many entity-name="additionalcontacts"
                         class="com.test.dto.AdditionalContact" />
        </bag>
    </class>
</hibernate-mapping>

This one is failing on

cascade="all"

Please add the Hibernate mapping for the object you are trying to insert — Adam, Dec 08 '16 at 02:15
so you mean in your mySql table `additional_contacts`, the id column `contactId` is defined as `AUTO_INCREMENT`? — diufanman, Dec 08 '16 at 03:08
It seems not make sense to me, if you use auto_increment in mySql will get the primary key violation. So I think it is the problem of the id generation strategy in your config file ``, may be you try this ``, please also refer to http://stackoverflow.com/questions/16373015/native-generator-class-in-hibernate — diufanman, Dec 08 '16 at 03:19

score 2 · Accepted Answer · answered Dec 08 '16 at 03:28

You need to use AUTO_INCREMENT. This way MySQL will manage the creation of IDs, so the servers won't have to synchronize. Change the DB column to be AUTO_INCREMENT. In Hibernate change the generator type to native.

Other alternatives would be to use UUID as the primary key.

Newer versions of Hibernate have other generators that can be used that are better than native.

Hibernate primary key violation on two server instances

1 Answers1