Hibernate 4.2.21 ORA-00001: unique constraint

Question

I'm working with Hibernate 4.2.21.Final and Oracle Database 12c 12.1.0.2.0 - 64bit Production, we have defined in our Jboss a JNDI for our DataSource, We have a strange problem in one of our Tables - only in this case, this problem it's not usual may be we have in total 2 cases during this two weeks:

When we want to save a Object in the DB, and we got this error:

2016-05-24 08:16:30,425 INFO
[org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl] (rommApp)
HHH000010: On release of batch it still contained JDBC statements

2016-05-24 08:16:30,427 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (rommApp) SQL
Error: 1, SQLState: 23000 2016-05-24 08:16:30,427 ERROR
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (rommApp)
ORA-00001: unique constraint (rommDBA.SYS_C006240) violated

This constraint it's a Primary Key, the problem seems to be we are using a primary key that it's in use, but if this fail should be fail more times, when I try to save the Object the second time works.

There is a good explanation about this random error?

Sequence:

--
-- ORDER_SEQ  (Sequence)
--
CREATE SEQUENCE rommDBA.ORDER_SEQ
  START WITH 1
  MAXVALUE 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  ORDER
  NOKEEP
  GLOBAL;

Table:

--
-- ORDERS  (Table)
--
CREATE TABLE ROMMDBA.ORDERS
(
  ORDER_ID                 NUMBER(19)           NOT NULL,
  ORDER_NAME                VARCHAR2(255 CHAR),
  ORDER_CREATEDBY          VARCHAR2(255 CHAR)   NOT NULL,
  ORDER_CREATEDON          TIMESTAMP(6)         NOT NULL,
)
TABLESPACE ROMM
RESULT_CACHE (MODE DEFAULT)
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
COMPRESS FOR OLTP
NOCACHE
NOPARALLEL
MONITORING;

Model Class:

@Id
    @SequenceGenerator(name = "OrderSequence", sequenceName = "order_seq", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="OrderSequence")    
    @Column(name = "order_id", unique = true, nullable = false)
    private Long id;

DAOImpl:

 public Order  addOrder(Order order) {
            Session session = SessionFactory.getSessionFactory().openSession();
            try {
                session.beginTransaction();
                session.save(order);
                session.getTransaction().commit();
            } catch (HibernateException e) {
                session.getTransaction().rollback();
                throw new HibernateException(e);
            } finally {
                session.close();
            }

            return order;
        }

EDIT, more config Info:

<!DOCTYPE hibernate-configuration SYSTEM "classpath://org/hibernate/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
   <session-factory>

      <property name="hibernate.jdbc.batch_size">30</property>
      <property name="hibernate.default_schema">rommApp</property>
      <mapping class="backend.model.romm.Order" />

   </session-factory>
</hibernate-configuration>

SessionFactory Config:

configuration.setProperty("hibernate.dialect", "org.hibernate.dialect.Oracle10gDialect");
configuration.setProperty("hibernate.connection.driver_class", "oracle.jdbc.driver.OracleDriver");
configuration.setProperty("hibernate.connection.datasource", "jdbc/rommApp");

Connection String:

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=OFF)(FAILOVER=ON)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=XXXXXX)(Port=XXXX))(ADDRESS=(PROTOCOL=TCP)(Host=XXXXXX)(Port=XXXX)))(CONNECT_DATA=(SERVICE_NAME=XXXXX)))

I m not expert in Oracle, I worked On it for just one week, and I had a similar — Mohamed Nabli, May 24 '16 at 08:17
I m not expert in Oracle, I worked On it for just one week, and I had a similar problem, try to change your generation type to Identity, which generate the Primary key just before the commit, after inserting your record in oracle instance, while the strategy Sequence Generates the Key on inserting the record in the instance... — Mohamed Nabli, May 24 '16 at 08:23
Whats is the result of the following queries? `SELECT MAX(order_id) FROM ROMMDBA.ORDERS;` and `SELECT order_seq.nextval FROM dual;` Is the maximum order_id greater than your order_seq.nextval? Then that's the problem. — STaefi, May 24 '16 at 08:55
@STaefi no it's not greater, it's correct the max it's 4 and the nextval is 5. I think the problem is somewhere in the Hibernate Sequence Generation, thanks for the Hint Mohamed Nabli, but it's very strange.. we have the same Hibernate config in the whole app and no problems, only here.., even the GenerationType by SEQUENCE it's a good practice to give the work to calculate the sequence on the DB engine — reizintolo, May 24 '16 at 16:25
@MohamedNabli in our Hibernate config: hibernate.id.new_generator_mappings= false, can this feature generate this strange behaviour? — reizintolo, May 24 '16 at 17:06
I am not really sure about that Sorry but if there is a way to change the config Just do it, all I know is the value of hibernate.id.new_generator_mappings is false by default in JBoss EAP 6. — Mohamed Nabli, May 24 '16 at 17:51
@MohamedNabli I saw in another thread that another possible problem can be not set the INCREMENT BY 1 in the sequence: http://stackoverflow.com/questions/9861416/hibernate-generates-negative-id-values-when-using-a-sequence/10653792#10653792, but would be good if somebody can give us a light on this Problem, test this is not easy, nowadays we have only problems in this table, in another table with the same config we load everyday 30.000 records without problem — reizintolo, May 24 '16 at 20:21

Hibernate 4.2.21 ORA-00001: unique constraint

0 Answers0