Hibernate using sequence generator and sequence in Oracle
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.
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.
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.