Notice: In order to edit this ticket you need to be either: a Product Owner, The owner or the reporter of the ticket, or, in case of a Task not yet assigned, a team_member"

Bug #1176 (closed)

Opened 12 years ago

Closed 12 years ago

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

Reported by: jamoore Owned by: jamoore
Priority: blocker Cc: cblackburn, cxallan
Sprint: n.a.
Total Remaining Time: n.a.

Description

After several successful tests:

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

Attachments (2)

sequence_fix.sql (8.4 KB) - added by jmoore 12 years ago.
Attaching an SQL script which will reset all enums until the sequence generators are functioning properly.
seq.sql (10.4 KB) - added by jmoore 12 years ago.
Next version of the upgrade script. No longer using sequences.

Download all attachments as: .zip

Change History (11)

comment:1 Changed 12 years ago by 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')");

comment:2 Changed 12 years ago by 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.

comment:3 Changed 12 years ago by jmoore

  • Resolution set to fixed
  • Status changed from new to closed

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.

comment:4 Changed 12 years ago by jmoore

  • Cc callan added
  • Component changed from Security to ORM
  • Resolution fixed deleted
  • Status changed from closed to reopened

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)

Changed 12 years ago by jmoore

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

comment:5 Changed 12 years ago by jmoore

  • Priority changed from critical to blocker

comment:6 Changed 12 years ago by 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.

comment:7 Changed 12 years ago by jmoore

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

Most likely moving to the TableGenerator.

comment:8 Changed 12 years ago by jmoore

  • Summary changed from Session primary keys can get out of sync to Primary key sequences can get out of sync due to Hibernate's use of nextval

Changed 12 years ago by jmoore

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

comment:9 Changed 12 years ago by jmoore

  • Resolution set to fixed
  • Status changed from reopened to closed

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.

Note: See TracTickets for help on using tickets. You may also have a look at Agilo extensions to the ticket.

1.3.13-PRO © 2008-2011 Agilo Software all rights reserved (this page was served in: 0.82718 sec.)

We're Hiring!