Uploaded image for project: 'JBoss Enterprise Application Platform 4 and 5'
  1. JBoss Enterprise Application Platform 4 and 5
  2. JBPAPP-3913

Oracle 11g R2 RAC - Test SequenceIdentityTest fails because first value of sequence is "2"

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • EAP 5.0.1.CR1, 4.2.0.GA_CP09, 4.3.0.GA_CP08
    • Hibernate
    • None
    • Oracle 11g R2 RAC with driver version 11.2.0.1.0

    • Release Notes
    • Workaround Exists
    • Hide

      Starting with Oracle 11g R2 (RAC), the behavior of the CREATE TABLE statement has changed, when creating a conventional table in a database created with the default options then the initial segment is not created until the first row is inserted into the table.
      see http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/tables002.htm#ADMIN13319 for more details.
      We found a potential downside to this change :
      The sequences created as default ( start with 1 increment by 1) are not staring with "1" when used in insert query script, Instead they are starting with 2 or 4, this can not be changed even explicitly tell Oracle that the first value is "1".
      In another words, when using Hibernate with Oracle 11g R2 (RAC), if the ID generator class is "sequence-identity", the first value is NOT 1.

      Two ways to work around this issue:
      1. Using another ID generator class.
      2. Disable deferred segment creation of Oracle by setting the initialization parameter DEFERRED_SEGMENT_CREATION to FALSE. The new clauses SEGMENT CREATION DEFERRED and SEGMENT CREATION IMMEDIATE are available for the CREATE TABLE statement. These clauses override the setting of the DEFERRED_SEGMENT_CREATION initialization parameter.

      Show
      Starting with Oracle 11g R2 (RAC), the behavior of the CREATE TABLE statement has changed, when creating a conventional table in a database created with the default options then the initial segment is not created until the first row is inserted into the table. see http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/tables002.htm#ADMIN13319 for more details. We found a potential downside to this change : The sequences created as default ( start with 1 increment by 1) are not staring with "1" when used in insert query script, Instead they are starting with 2 or 4, this can not be changed even explicitly tell Oracle that the first value is "1". In another words, when using Hibernate with Oracle 11g R2 (RAC), if the ID generator class is "sequence-identity", the first value is NOT 1. Two ways to work around this issue: 1. Using another ID generator class. 2. Disable deferred segment creation of Oracle by setting the initialization parameter DEFERRED_SEGMENT_CREATION to FALSE. The new clauses SEGMENT CREATION DEFERRED and SEGMENT CREATION IMMEDIATE are available for the CREATE TABLE statement. These clauses override the setting of the DEFERRED_SEGMENT_CREATION initialization parameter.
    • Hide
      In Oracle 11g R2 (both RAC and standalone), a sequence may start at 2 instead of 1. The root cause is still under analysis.
      There are currently two workarounds for this issue:
        
        <orderedlist>
          <listitem>
            <para>Use another ID generator class.</para>
          </listitem>
          <listitem>
            <para>Disable deferred segment creation of Oracle by setting the initialization parameter <parameter>DEFERRED_SEGMENT_CREATION
            </parameter> to <literal>FALSE</literal>. The new clauses <parameter>SEGMENT CREATION DEFERRED</parameter> and <parameter>SEGMENT
            CREATION IMMEDIATE</parameter> are available for the <parameter>CREATE TABLE</parameter> statement. These clauses override the
            setting of the <parameter>DEFERRED_SEGMENT_CREATION</parameter> initialization parameter.</para>
          </listitem>
        </orderedlist>
      Show
      In Oracle 11g R2 (both RAC and standalone), a sequence may start at 2 instead of 1. The root cause is still under analysis. There are currently two workarounds for this issue:      <orderedlist>     <listitem>       <para>Use another ID generator class.</para>     </listitem>     <listitem>       <para>Disable deferred segment creation of Oracle by setting the initialization parameter <parameter>DEFERRED_SEGMENT_CREATION       </parameter> to <literal>FALSE</literal>. The new clauses <parameter>SEGMENT CREATION DEFERRED</parameter> and <parameter>SEGMENT       CREATION IMMEDIATE</parameter> are available for the <parameter>CREATE TABLE</parameter> statement. These clauses override the       setting of the <parameter>DEFERRED_SEGMENT_CREATION</parameter> initialization parameter.</para>     </listitem>   </orderedlist>
    • Documented as Known Issue

      The test below fails because the first value for the sequence is "2". The next value is correctly "3". I tried changing the sequence definition, to explicitly tell Oracle that the first number is "1", to no effect. I'm still investigating why this is happening. It may be related to our Oracle in QA Lab setup. The same test works when using the same code/jdbc driver, but Oracle 11g R1 RAC.

      org.hibernate.test.generatedkeys.seqidentity.SequenceIdentityTest.testSequenceIdentityGenerator

            shaozliu Strong Liu(刘少壮) (Inactive)
            jpkroehling@redhat.com Juraci Paixão Kröhling (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved: