This discussion is archived
4 Replies Latest reply: Nov 21, 2012 3:38 PM by 576227 RSS

Sequence / Trigger increments by two before insert

576227 Newbie
Currently Being Moderated
Hello,

Lots of my integration tests are in error.

For example, the tests suite with the following beforeAnyTest method...

Not even running one test, having the following content in the beforeAnyTest method is enough to trigger the issue:

public void beforeAnyTest() throws Exception {
navbar0 = navbarDao.makePersistent(navbar0);
navbarDao.flush();
navbarLanguage0 = navbarLanguageDao.makePersistent(navbarLanguage0);
navbarLanguageDao.flush();
}

I also noticed one surprising thing with the first insert statement.

When Hibernate tries inserting the navbar with the statement:
insert into navbar (version, hide, id) values (?, ?, ?)
2012-11-17 15:14:43,600 TRACE [BasicBinder] binding parameter [1] as [INTEGER] - 0
2012-11-17 15:14:43,600 TRACE [BasicBinder] binding parameter [2] as [BIT] - false
2012-11-17 15:14:43,600 TRACE [BasicBinder] binding parameter [3] as [INTEGER] - 517
2012-11-17 15:14:43,600 DEBUG [AbstractBatcher] Executing batch size: 1
2012-11-17 15:14:43,602 DEBUG [Expectations] success of batch update unknown: 0

which is:
insert into navbar (version, hide, id) values (0, 0, 517);

Running the above sql statement on the command line sqlplus client shows:
SQL> insert into navbar (version, hide, id) values (0, 0, 517);

1 row created.

SQL> select * from navbar;

ID VERSION HIDE
---------- ---------- ----------
503 0 0
504 0 0
505 0 0
508 0 0
509 0 0
510 0 0
511 0 0
519 0 0

8 rows selected.

SQL>

The value inserted (519) is greater by 2 than the value (517) seen by Hibernate.

Oracle does the insert successfully and does not say anything, even if the id provided value (517) seen by Hibernate is not the one that ends up in the table (519).

It is then no wonder that the child insert later fails:
insert into navbar_language (version, language_code, navbar_id, id) values (0, 'en', 501, 583);

Why is there this difference of 2 between the id value seen by Hibernate and the one inserted in the table ?
  • 1. Re: Sequence / Trigger increments by two before insert
    576227 Newbie
    Currently Being Moderated
    The table structure and sequence / trigger:

    create table admin (
    id number(10) not null,
    version number(10) not null,
    firstname varchar2(255) not null,
    lastname varchar2(255) not null,
    login varchar2(50) not null,
    constraint admin_login_u1 unique (login),
    password varchar2(100) not null,
    password_salt varchar2(50),
    super_admin number(1) not null check (super_admin in (0, 1)),
    preference_admin number(1) not null check (preference_admin in (0, 1)),
    address varchar2(255),
    zip_code varchar2(10),
    city varchar2(255),
    country varchar2(255),
    email varchar2(255),
    profile clob,
    constraint admin_pk primary key (id)
    );
    create sequence sq_id_admin increment by 1 start with 1 nomaxvalue nocycle cache 10;
    create or replace trigger tr_id_inc_admin
    before insert
    on admin
    for each row
    declare
    begin
    select sq_id_admin.nextval into :new.id from dual;
    end;
    /
  • 2. Re: Sequence / Trigger increments by two before insert
    clcarter Expert
    Currently Being Moderated
    Where's your 517 coming from?

    Why specify an ID for an insert when a trigger is supposed to do it for you?

    Increment by two? Is it possible there are two triggers on the table, each one getting a new sequence number?

    select trigger_name, trigger_type, triggering_event
    from user_triggers
    where table_name = 'ADMIN';

    Edited by: clcarter on Nov 19, 2012 12:13 PM
    add user_triggers sql
  • 3. Re: Sequence / Trigger increments by two before insert
    Justin Cave Oracle ACE
    Currently Being Moderated
    Is Hibernate configured to get the ID value from the sequence? If not, how does Hibernate know to bind a value of 517?

    The way your trigger is defined, whatever ID gets specified during the INSERT will be ignored and a new value will be read from the sequence. If you are using an ORM that expects to be able to generate the values without using a trigger, it is entirely possible (and somewhat likely) that Hibernate would first do a
    SELECT sq_id_admin.nextval
      FROM dual
    in order to get the value 517, bind that value in the INSERT, then have your trigger fire, ignore the 517, generate the next value from the sequence which would be 518 if you have a single-user single-node environment with no reboots and where the sequence never ages out of the shared pool which would then be used in the INSERT into the table. That would account for skipping one value, though, but not two. As clcarter points out, you may also have multiple triggers on the table.

    If you want to configure Hibernate to generate the ID from a sequence, you likely want to configure your trigger to assign an ID only if the INSERT statement did not provide one, e.g
    IF( :new.id IS NULL )
    THEN
      SELECT sq_id_admin.nextval
        INTO :new.id
        FROM dual
    END IF;
    Justin
  • 4. Re: Sequence / Trigger increments by two before insert
    576227 Newbie
    Currently Being Moderated
    Hello,

    Sorry for my late reply, I hadn't seen yours as I didn't get any email notification.

    I run the test against the following two tables:

    create table navbar (
    id number(10) not null,
    version number(10) not null,
    hide number(1) not null check (hide in (0, 1)),
    constraint navbar_pk primary key (id)
    );
    create sequence sq_id_navbar increment by 1 start with 1 nomaxvalue nocycle cache 10;
    create or replace trigger tr_id_inc_navbar
    before insert
    on navbar
    for each row
    declare
    begin
    if (:new.id is null)
    then
    select sq_id_navbar.nextval into :new.id from dual;
    end if;
    end;
    /

    create table navbar_language (
    id number(10) not null,
    version number(10) not null,
    language_code varchar2(2),
    navbar_id number(10) not null,
    constraint navbar_language_pk primary key (id),
    constraint navbar_language_fk1 foreign key (navbar_id) references navbar (id)
    );
    create sequence sq_id_navbar_language increment by 1 start with 1 nomaxvalue nocycle cache 10;
    create or replace trigger tr_id_inc_navbar_language
    before insert
    on navbar_language
    for each row
    declare
    begin
    if (:new.id is null)
    then
    select sq_id_navbar_language.nextval into :new.id from dual;
    end if;
    end;
    /

    For additional information, although I suppose the issue is not really related to Hibernate as I can reproduce it in the above sqlplus statements, here is the Hibernate mapping:

    <class name="com.thalasoft.learnintouch.core.domain.Navbar" table="navbar" dynamic-insert="true" dynamic-update="true">
    <id name="id" type="java.lang.Integer">
    <column name="id" />
    <generator class="native"><param name="sequence">sq_id_navbar</param></generator>
    </id>
    <version name="version" type="int">
    <column name="version" not-null="true" />
    </version>
    <property name="hide" type="boolean">
    <column name="hide" not-null="true" />
    </property>
    </class>

    <class name="com.thalasoft.learnintouch.core.domain.NavbarLanguage" table="navbar_language" dynamic-insert="true" dynamic-update="true">
    <id name="id" type="java.lang.Integer">
    <column name="id" />
    <generator class="native"><param name="sequence">sq_id_navbar_language</param></generator>
    </id>
    <version name="version" type="int">
    <column name="version" not-null="true" />
    </version>
    <property name="languageCode" type="string">
    <column name="language_code" length="2" />
    </property>
    <many-to-one name="navbar" class="com.thalasoft.learnintouch.core.domain.Navbar" cascade="all">
    <column name="navbar_id" not-null="true" />
    </many-to-one>
    </class>

    @clcarter
    I should have given you more context. Indeed, Hibernate is getting the value from the table at some point, it's not a hard coded one. Also, there was only one trigger and I could see it with the command:

    SQL> select trigger_name, trigger_type, triggering_event, table_name from user_triggers where table_name = 'NAVBAR_LANGUAGE';

    TRIGGER_NAME          TRIGGER_TYPE
    ------------------------------ ----------------
    TRIGGERING_EVENT
    --------------------------------------------------------------------------------
    TABLE_NAME
    ------------------------------
    TR_ID_INC_NAVBAR_LANGUAGE BEFORE EACH ROW
    INSERT
    NAVBAR_LANGUAGE


    @Justin
    Hibernate seems to be doing exactly that you suggested, namely a select to first retrieve the id to be used, as the console log shows. In fact the console log shows two select statements in a row.. is that why there is an increment by two on the id ?

    Here is the console log:

    2012-11-22 01:09:16,992 DEBUG [AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
    2012-11-22 01:09:16,992 DEBUG [SQL]
    select
    sq_id_navbar_language.nextval
    from
    dual
    Hibernate:
    select
    sq_id_navbar_language.nextval
    from
    dual
    2012-11-22 01:09:16,993 DEBUG [SequenceGenerator] Sequence identifier generated: BasicHolder[java.lang.Integer[624]]
    2012-11-22 01:09:16,993 DEBUG [AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
    2012-11-22 01:09:16,993 DEBUG [AbstractSaveEventListener] generated identifier: 624, using strategy: org.hibernate.id.SequenceGenerator
    2012-11-22 01:09:16,994 DEBUG [AnnotationTransactionAttributeSource] Adding transactional method 'findWithNavbarAndNoLanguage' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''
    2012-11-22 01:09:16,994 DEBUG [AnnotationTransactionAttributeSource] Adding transactional method 'findWithNavbarAndNoLanguage' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''
    2012-11-22 01:09:16,994 DEBUG [DefaultListableBeanFactory] Returning cached instance of singleton bean 'transactionManager'
    2012-11-22 01:09:16,994 DEBUG [DefaultListableBeanFactory] Returning cached instance of singleton bean 'transactionManager'
    2012-11-22 01:09:16,994 DEBUG [HibernateTransactionManager] Found thread-bound Session [org.hibernate.impl.SessionImpl@f6fa2] for Hibernate transaction
    2012-11-22 01:09:16,994 DEBUG [HibernateTransactionManager] Found thread-bound Session [org.hibernate.impl.SessionImpl@f6fa2] for Hibernate transaction
    2012-11-22 01:09:16,995 DEBUG [HibernateTransactionManager] Participating in existing transaction
    2012-11-22 01:09:16,995 DEBUG [HibernateTransactionManager] Participating in existing transaction
    2012-11-22 01:09:16,997 DEBUG [AbstractFlushingEventListener] processing flush-time cascades
    2012-11-22 01:09:16,998 DEBUG [AbstractFlushingEventListener] dirty checking collections
    2012-11-22 01:09:16,998 DEBUG [AbstractFlushingEventListener] Flushed: 5 insertions, 0 updates, 0 deletions to 5 objects
    2012-11-22 01:09:16,998 DEBUG [AbstractFlushingEventListener] Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
    2012-11-22 01:09:16,998 DEBUG [Printer] listing entities:
    2012-11-22 01:09:16,999 DEBUG [Printer] com.thalasoft.learnintouch.core.domain.NavbarLanguage{id=623, languageCode=se, navbar=com.thalasoft.learnintouch.core.domain.Navbar#530, version=0}
    2012-11-22 01:09:16,999 DEBUG [Printer] com.thalasoft.learnintouch.core.domain.NavbarLanguage{id=624, languageCode=null, navbar=com.thalasoft.learnintouch.core.domain.Navbar#530, version=0}
    2012-11-22 01:09:16,999 DEBUG [Printer] com.thalasoft.learnintouch.core.domain.Navbar{id=530, hide=false, version=0}
    2012-11-22 01:09:16,999 DEBUG [Printer] com.thalasoft.learnintouch.core.domain.NavbarLanguage{id=621, languageCode=en, navbar=com.thalasoft.learnintouch.core.domain.Navbar#530, version=0}
    2012-11-22 01:09:16,999 DEBUG [Printer] com.thalasoft.learnintouch.core.domain.NavbarLanguage{id=622, languageCode=fr, navbar=com.thalasoft.learnintouch.core.domain.Navbar#530, version=0}
    2012-11-22 01:09:16,999 DEBUG [ActionQueue] changes must be flushed to space: navbar_language
    2012-11-22 01:09:17,000 DEBUG [AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
    2012-11-22 01:09:17,000 DEBUG [SQL]
    insert
    into
    navbar
    (version, hide, id)
    values
    (?, ?, ?)
    Hibernate:
    insert
    into
    navbar
    (version, hide, id)
    values
    (?, ?, ?)
    2012-11-22 01:09:17,000 TRACE [BasicBinder] binding parameter [1] as [INTEGER] - 0
    2012-11-22 01:09:17,000 TRACE [BasicBinder] binding parameter [2] as [BIT] - false
    2012-11-22 01:09:17,001 TRACE [BasicBinder] binding parameter [3] as [INTEGER] - 530
    2012-11-22 01:09:17,001 DEBUG [AbstractBatcher] Executing batch size: 1
    2012-11-22 01:09:17,002 DEBUG [Expectations] success of batch update unknown: 0

    Following your comment, I deleted the table, the sequence and the trigger to recreate it all with a trigger containing your if then clause.

    It seems to have somehow solved the issue as I don't have the constraint violation exception any longer, but only a failed test.

    Thanks for that very smart and pinpoint support !

    Edited by: user573224 on Nov 21, 2012 3:33 PM

    Edited by: user573224 on Nov 21, 2012 3:35 PM

    Edited by: user573224 on Nov 21, 2012 3:37 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points