4

I am running a JBoss EAP 7 server with an Oracle 11g DB, and Hibernate for JPA. I have noticed something weird. When I first create the database and start the server, everything works fine. I send requests from the client and the server persists the data in the DB.

If I restart the server and try to do the same, I get a unique constraint violation exception for every request:

java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SCHEMA.SYS_C0010299) violated

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3694)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)

I checked the constraint in sqlplus with the query below. (I ran the query as the system user, not the same user as the server, if that matters).

SELECT A.TABLE_NAME,C.TABLE_NAME,COLUMN_NAME FROM ALL_CONS_COLUMNS A
JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_NAME = 'SYS_C0010299';

It seems to happen on the primary key of one of my tables. That primary key is generated with a sequence.

@Id
@Column(name="ID_COL")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "SEQ_NAME_GEN")
@SequenceGenerator(name = "SEQ_NAME_GEN", sequenceName = "SEQ_NAME")
private Long id;

If I create a fresh new DB, the application again works fine at first, until I restart the server. Why does this happen?

This is the relation of the entity class to another entity class:

// other class
@OneToMany(cascade=CascadeType.ALL, mappedBy="otherClass")
@MapKey(name = "mapKey")
private Map<MapKey, ConstraintViolationEntityClass>
    map;

// problematic class (ConstraintViolationEntityClass)
@Column(name = "MAP_KEY")
@Enumerated(EnumType.ORDINAL)
private EnumType enumType;
@ManyToOne
@JoinColumn(name = "OTHER_CLASS_ID", nullable = false)
private OtherClass otherClass;

And this is the SQL code I used to create the table for the ConstraintViolationEntityClass:

create table schema.ConstraintViolationEntityTable (
  id_col number(10) not null primary key,
  map_key number(2) not null,
  other_class_id number(10) not null,
  constraint other_class_fk foreign key (other_class_id) references schema.other_class(id)
);

This is my persistence.xml:

<persistence-unit name="unit1" transaction-type="JTA">
  <jta-data-source>java:jboss/OracleDS</jta-data-source>
  <exclude-unlisted-classes>false</exclude-unlisted-classes>
  <properties>
    <property name="hibernate.transaction.jta.platform" value="org.hibernate.service.jta.platform.internal.JBossStandAloneJtaPlatform"/>
    <property name="hibernate.show_sql" value="true" />
    <property name="hibernate.format_sql" value="true" />
    <property name="hibernate.hbm2ddl.auto" value="validate" />
  </properties>
</persistence-unit>

For some reason, some of the primary keys of the rows inserted by the successful requests are negative. And checking dba_sequences, the last_number of the sequence is 43, even though the table only has 24 rows in it (12 rows added per client request)

devil0150
  • 1,350
  • 3
  • 13
  • 36
  • is this a many-to-many table, I seem to remember that sometimes Hibernate would try to insert twice in that scenario. Maybe check dba_sequences, make sure your sequence is not getting recreated or something strange like that – Peter M Apr 09 '18 at 19:25
  • Could you share your hibernate properties? – Rohit Apr 09 '18 at 19:38
  • 1
    @PeterM I added some more info in the description about the only relation of the table. I checked the sequence before and after starting the server, and it didn't change. @ Rohit I will add the persistence.xml in the post – devil0150 Apr 09 '18 at 19:52
  • I found this post, it might help -- https://stackoverflow.com/questions/12745751/hibernate-sequencegenerator-and-allocationsize – Peter M Apr 09 '18 at 20:08
  • looks like maybe Hibernate is caching the sequence but not updating the oracle. When you look at the last_number in dba_sequences, is it less than what you have in the table? – Peter M Apr 09 '18 at 20:09
  • @PeterM for some reason sqlplus shows negative values for most of the IDs, and for some of the foreign keys to the other table. The increment by is ommited, so it defaults to 1. Should I set it to 50? – devil0150 Apr 09 '18 at 20:14
  • 1
    I think @PeterM is correct. The `SequenceGenerator` default allocation size is 50. What is your sequence increment size? – Rohit Apr 09 '18 at 20:14
  • Since your `persistence.xml` uses `hibernate.hbm2ddl.auto=validate`, how are you generating the db? Is it possible that the sequence is created in a different way than if `hibernate.hbm2ddl.auto=create-drop` was used? – crizzis Apr 09 '18 at 20:31
  • what is your "starts_with" for your sequence in dba_sequences, wierd that you would have negative values for your id – Peter M Apr 09 '18 at 20:59
  • @crizzis I am creating the db manually, with an sql script. the sequences are created with `create sequence SEQ_NAME start with 1;` How can I check if that is different from what hibernate wants? – devil0150 Apr 10 '18 at 19:26
  • Well, the fastest way would be to let hbm2ddl do its job and look up the sequence definition in `all_sequences`. you could also try to use the schema generation tool as described here: https://access.redhat.com/documentation/en-us/jboss_enterprise_application_platform/5/html/hibernate_core_reference_guide/toolsetguide – crizzis Apr 10 '18 at 19:40
  • @crizzis I tried it with `create-drop` and saw the sql statements that hibernate uses to create the sequences. The only difference was the `increment by 50`. I modified my own script to use that and now it's working. Can you or PeterM write this as an answer? If you have an explanation of why this would cause a constraint violation and negative IDs, please include it. – devil0150 Apr 10 '18 at 20:10
  • I'd say @PeterM deserves the credit. As he correctly pointed out, the default allocation size is 50. You can look it up in the javadoc for `@SequenceGenerator`: https://docs.oracle.com/javaee/7/api/javax/persistence/SequenceGenerator.html – crizzis Apr 11 '18 at 09:02

1 Answers1

2

As stated in the answer that PeterM linked to, the default allocation size for sequence generators is 50, which is the root cause of the problem, since you defined the sequence with an increment of 1. I'll just comment on the negative values issue.

An allocation size of 50 (set in SequenceGenerator.allocationSize) means that Hibernate will:

  • create the sequence with INCREMENT BY 50 (if you let it)
  • grab the next value n from the sequence
  • start allocating ids from n-50 till n
  • repeat the two steps above once it runs out of numbers

Since you've made the sequence increment by 1, it's easy to see where the negative values come from (and why constraint violations will follow). If you tried inserting more than 50 rows, you'd run into constraint violations without having to restart the server.

crizzis
  • 9,978
  • 2
  • 28
  • 47