How to duplicate input errors from Hibernate, is MySQL the culprit?

Question

I am working on a database application that is mostly read-only, but there is one table that records the user's movement in the application and has a large number of records. For every few thousand entries, we see several exceptions in the error log:

[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 corresponding table has the following scheme:

 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 XML mapping:

 <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, although unlikely, that several instances of our webapp can immediately be written to the database, since we are version numbers in our webapp context for the smooth release of new versions of applications. Clients with an old version of the application cached in their web browser will gain access to the old versions of the server, which we are deploying in a few weeks.

In any case, I'm not sure if this is a problem, but I suspect there is a synchronization problem between MySQL and Hibernate here. Will my generator change the sequence, seqhilo or hilo? In addition, if you can provide an example of setting up such a generator in MySQL, it will be very useful, since most resources on the Internet are simply copied from extremely modest examples in the Hibernate manual.

Answer 1

The increment is certainly bad, if you have more than one process writing to the same table, you will definitely have collisions.

Since this is the MySQL we are talking about, the easiest way is to use 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 modify 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 with the next available identifier so that the sequence does not reset to 1.