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

Details

    • Bug
    • Resolution: Done
    • Major
    • EAP_EWP 5.1.0, 4.3.0.GA_CP09, 4.2.0.GA_CP10
    • 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

    Description

      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

      Attachments

        Issue Links

          blocks

          Task - Represents a small unit of work that is not end-user facing. JBPAPP-3591 Certify against Oracle 11g R2 (and RAC for EAP 5.0)

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Resolved

          Activity

            People

              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

              Dates

                Created:
                Updated:
                Resolved: