Getting duplicate entry errors from Hibernate, is MySQL to blame?

Question

I am working on a database application which is mostly read-only, but there is one table which records user movement in the app and has a large number of writes to it. For every few thousand writes, we see a few exceptions in the error log like so:

[WARN][2009-07-30 11:09:20,083][org.hibernate.util.JDBCExceptionReporter] SQL Error: 1062, SQLState: 23000
[ERROR][2009-07-30 11:09:20,083][org.hibernate.util.JDBCExceptionReporter] Duplicate entry '17011' for key 1
[ERROR][2009-07-30 11:09:20,083][org.hibernate.event.def.AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
  at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)

The table in question has the following schema:

CREATE TABLE IF NOT EXISTS `my_table` (
  `id` int(11) NOT NULL,
  `data1` int(11) NOT NULL,
  `data2` int(11) NOT NULL,
  `timestamp` datetime default NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

And the corresponding Hibernate mapping XML:

<hibernate-mapping>
  <class name="mycorp.MyClass" table="my_table">
    <id name="id" column="id" type="java.lang.Integer">
      <generator class="increment"/>
    </id>
    <property name="data1" column="data1" type="java.lang.Integer"/>
    <property name="data2" column="data2" type="java.lang.Integer"/>
    <property name="timestamp" column="timestamp" type="java.util.Date"/>
  </class>
</hibernate-mapping>

It is possible, though unlikely, that multiple instances of our webapp could be writing to the database at once, since we version numbers in our webapp context to seamlessly release new versions of the applications. Clients with the old version of the application cached in their web browser would thus access the old versions of the server, which we undeploy after a few weeks.

Anyways, I'm not convinced that this is the problem, but I'm suspicious that there is some synchronization issue between MySQL and Hibernate at hand here. Would changing my generator to sequence, seqhilo or hilo help? Also, if you can provide an example of setting up such a generator in MySQL, that would be very helpful, as most of the resources online are simply copy-pasted from the woefully minimalistic examples in the Hibernate manual.

Answer 1

Increment is definitely bad if you have more than one process writing to the same table - you're bound to have collisions.

Since it is MySQL we're talking about, the easiest thing to use would be identity. In your Hibernate mapping:

<generator class="identity"/>

In your MySQL script:

CREATE TABLE IF NOT EXISTS `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data1` int(11) NOT NULL,
  `data2` int(11) NOT NULL,
  `timestamp` datetime default NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

To alter an existing table:

ALTER TABLE `my_table`
  CHANGE COLUMN `id` `id` int(11) NOT NULL AUTO_INCREMENT=$NEW_VALUE$;

where $NEW_VALUE$ should be replaced by the next available id so that sequence does not reset to 1.