Primary key sequences can get out of sync due to Hibernate's use of nextval

[jamoore]

After several successful tests:

select nextval('seq_session');
150
select id from session order by id desc limit 1;
7500

[jmoore]

This comes from any post-db-create use of select nextval(...) since Hibernate currently uses as hi-lo generator: org.hibernate.id.SequenceHiLoGenerator? which adds the results of nextval to the highest object id.

I've been meaning to look into the other id generators:

​http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#mapping-declaration-id-enhanced-optimizers

and will try to come to some conclusion. Note: the uses of nextval that need to be taken into account are:

$ find .  -name "*.java" -exec grep -i nextval {} /dev/null \;
./components/server/src/ome/security/basic/EventHandler.java:                        + "values (nextval('seq_eventlog'),?,?,?,?,?)", -35L, l
./components/server/src/ome/services/sessions/SessionManagerImpl.java:                        // select nextval('seq_session'),-35,
./components/server/src/ome/services/sessions/SessionManagerImpl.java:                        return jdbcOps.queryForLong("select nextval('seq_session')");

[jmoore]

Something like this:

@GenericGenerator(name = "seq_image",
        strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
        parameters = {
            @Parameter(name = "sequence_name", value = "seq_image"),
            @Parameter(name = "optimizer", value = "pooled")
        })

seems to do the trick.

[jmoore]

r3666 has the fix. Dev databases may be corrupted. Either run the section of the upgrade script with:

  select setval('seq_...', id) from table order by id desc limit 1

or drop and re-create.

[jmoore]

Reopening. After manually resetting all and using the new sequence generator, I'm still getting corruption:

# select id from event order by id desc limit 1;
   id    
---------
 2442003
(1 row)

omero4.0.1=# select last_value from seq_event;
 last_value 
------------
      48842
(1 row)

[jmoore]

Attaching an SQL script which will reset all enums until the sequence generators are functioning properly.

[jmoore]

Well, stepping through the code in SequenceStyleGenerator one sees this:

                if ( dialect.supportsSequences() && !forceTableUse ) {
                        if ( OptimizerFactory.POOL.equals( optimizationStrategy ) && !dialect.supportsPooledSequences() ) {
                                // TODO : may even be better to fall back to a pooled table strategy here so that the db stored values remain consistent...
                                optimizationStrategy = OptimizerFactory.HILO;
                        }

which would pretty clearly say that this can't work, despite documentation.

[jmoore]

See: ​http://opensource.atlassian.com/projects/hibernate/browse/HHH-3349

Most likely moving to the TableGenerator.

[jmoore]

Next version of the upgrade script. No longer using sequences.

[jmoore]

With r3722, r3746, r3762 now ome.model.* clasess use ome.util.TableIdGenerator. This may also be related to #1158 and weird thread semantics in atomikos. One way or the other, now fixed.