[Shamlessly copying post from stack overflow below]

Hibernate 2.0 legacy code that has a single table using a hi/lo generation technique. I'm not sure why we did this, probably it was the second table we made using Hibernate and it worked and whatever. Underlying database is mysql 5.0.

Anyway, pertinent component of the .hbm.xml file:

<generator class="hilo">
<param name="column">workorder_history_id</param>
</generator>

We have a .jar file based process that communicates with the database that has been running successfully every three minutes for roughly nine years. This afternoon just before five we started seeing errors with 'duplicate entry for key' errors in the .logs and the exception emails started popping. The DAO and table referenced are for the table that utilizes the hi/lo generation technique.

When I look at the duplicate key value provided in the .log file and look for that primary key record in my table of interest, it is colliding with the some of the earliest records in said table; i.e., our first history records have a primary key value of 135626802 [dated 11/3/2005], and the jar file process is trying to write out values in this range: 135659521. Note: There are nearly 12 million records in the database with a very wide range of pk values going from -2,147,483,647 back up to the other end of the spectrum.

We also have a web app using the same configuration file that does not seem to be having any problems; he has been writing to the same table like a champ without any troubles. His range of 'recent' values *does* look to have some close overlap to the hits I'm getting in the .log file (i.e., in the 1356##### range), which doesn't make much sense to me; maybe the values generated from that JVM are (for now) hitting a blank spot of values soon to bonk. (?)

Finally, when I look in the hibernate_unique_key table, which has a single column, which maps to what is in the .hbm.xml file above, it has an amazingly smaller value; 135218. This leads me to believe the table isn't being updated, but both processes have successfully been appending to this table regularly for years. This may be a function of the fact that I don't really understand how the hi/lo component is supposed to work. (?)

So, my confusions are five fold:

1) Why start seeing this problem suddenly, when the process has been running constantly for nearly a decade?

2) Why would a web app with the same configuration fail to have any problems generating primary keys into this table, but a app driven externally from the web container start having these problems?

3) How on Earth is the .jar file driven process starting to come up with values in the range where we started nine years ago?

4) Why does the hibernate_unique_key table have such weird data in it?

5) How can I fix this problem?

I know this is a big dump of information but I'm kind of at a loss as to what is happening. Any insight is greatly appreciated.

Update: Bounced the application server and now see the same problem there. Critical issue. Please, anyone with ideas. Help.

Secondary update.

OK. It looks as if the generator has created large chunks of dead space in my primary key values; i.e., I have < 12M rows, but my maximum pk value in the table is > 2.14B, and the exact value is within spitting distance of the maximum size of an integer field in mysql.

Curiously, the max 2.14B value in pk for the table in question has a timestamp in 2011, at which point, my values start up again at -2.14B later on that same day.

Further, it appears that every time my .jar file driven process fires up and touches the database, he increments his counter by ~ 32K, and if I bounce the application server, another 32K gets added to the pile.

Is it really possible that I was soooo lucky that I survived three years of this thing hitting gaps and just started getting unlucky?