Hibernate using sequence generator and sequence in Oracle

Question

I have the following sequence:

[as seen now in Toad]:

CREATE SEQUENCE LOG_ID_SEQ
  START WITH 787585
  MAXVALUE 1000000000000000000000000000
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER
/

I have the following table sequence generator:

@SequenceGenerator(name="LOG_ID_SEQ", sequenceName="LOG_ID_SEQ")
@Id
@Column(name = "log_id", nullable = false)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="LOG_ID_SEQ")
Long id;

The highest value of log_id is currently 39379151

Now the weird problem: the client created a dump of the poduction database and imported it in the test database. When I tested the application I got a ORA-00001 unique constraint error on this table.

When I imported the same dump and test the application on my machine I do not get that error??

How is this possible with Hibernate? I have no idea where or what to look for.

[UPDATED]: To be accurate: after I imported the dump into a new schema locally the last sequence value in the dump was 39354002. Without resetting the sequence my next value is 39379151.

wrschneider · Accepted Answer · 2011-11-30 15:10:27Z

1

You're getting values back from your sequence that collide with data in your table.

max(log_id) = 39,379,151, which is higher than your sequence "start with" value = 787,585.

Re-create your sequence with "start with" higher than max(log_id) and you should be all set.

The error may not be consistent because you may not be using every sequence value, so it's possible that on occasion inserts may succeed if you get a value that falls in a gap between existing rows.

answered Nov 30, 2011 at 15:10

wrschneider

19k17 gold badges109 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike · Accepted Answer · 2011-11-30 15:11:20Z

0

If the current highest value of log_id is 39379151, but you're re-creating the LOG_ID_SEQ in a new schema/database with a starting value of 787585, then the next new row inserted will have a log_id value that already exists. You should probably alter your CREATE SEQUENCE statement to reflect the updated new max value for log_id.

answered Nov 30, 2011 at 15:11

Mike

8,0925 gold badges38 silver badges44 bronze badges

1 Comment

A.W. Over a year ago

On my local setup I get a new log_id value of 39379152 without resetting the sequence.

Collectives™ on Stack Overflow

Hibernate using sequence generator and sequence in Oracle

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related