Uploaded image for project: 'JIRA Server (including JIRA Core)'
  1. JIRA Server (including JIRA Core)
  2. JRASERVER-26636

Database sequences can reach a certain size that prevents additional sequences from being created

    Details

    • Occurrence Factor:
      10%

      Description

      Summary

      In certain situations it is possible for OfBiz sequences to reach a certain size that prevents additional sequences from being created.

      Steps to Reproduce

      1. Install JIRA with a MySQL database.
      2. Shutdown JIRA.
      3. Log into MySQL.
      4. Perform the below query:
        UPDATE SEQUENCE_VALUE_ITEM SET SEQ_ID = 2147483648;
        

        This will update all sequences to that sequence ID.

      5. Start JIRA.

      Expected Results

      JIRA will continue to operate as normal.

      Actual Results

      Either of the following:

      • JIRA will be locked
      • You'll be unable to login, due to errors in inserting into propertyentry table.

      The below errors can be found in the Catalina log:

      2011-12-21 16:02:37,239 http-5555-2 WARN anonymous 962x10x1 1hglwcn 0:0:0:0:0:0:0:1%0 /rest/gadget/1.0/login [ofbiz.core.entity.SequenceUtil] [SequenceUtil.SequenceBank.fillBank] SQL Exception while executing the following:
      SELECT SEQ_ID FROM SEQUENCE_VALUE_ITEM WHERE SEQ_NAME='OSPropertyEntry'
      Error was:
      2011-12-21 16:02:37,319 http-5555-2 WARN anonymous 962x10x1 1hglwcn 0:0:0:0:0:0:0:1%0 /rest/gadget/1.0/login [ofbiz.core.entity.SequenceUtil] '2.147483648E9' in column '1' is outside valid range for the datatype INTEGER.
      2011-12-21 16:02:37,319 http-5555-2 ERROR anonymous 962x10x1 1hglwcn 0:0:0:0:0:0:0:1%0 /rest/gadget/1.0/login [ofbiz.core.entity.SequenceUtil] [SequenceUtil.SequenceBank.getNextSeqId] Fill bank failed, returning null
      2011-12-21 16:02:37,322 http-5555-2 ERROR anonymous 962x10x1 1hglwcn 0:0:0:0:0:0:0:1%0 /rest/gadget/1.0/login [module.propertyset.ofbiz.OFBizPropertySet] Error setting value in PropertySet
      org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:OSPropertyEntry][id,null][entityId,10000][propertyKey,login.previousLoginMillis][type,5][entityName,OSUser] (SQL Exception while executing the following:INSERT INTO propertyentry (ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype) VALUES (?, ?, ?, ?, ?) (Column 'ID' cannot be null))
      	at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:149)
      	at org.ofbiz.core.entity.GenericDAO.singleStore(GenericDAO.java:268)
      	at org.ofbiz.core.entity.GenericDAO.storeAll(GenericDAO.java:311)
      	at org.ofbiz.core.entity.GenericHelperDAO.storeAll(GenericHelperDAO.java:221)
      	at org.ofbiz.core.entity.GenericDelegator.storeAll(GenericDelegator.java:1482)
      	at org.ofbiz.core.entity.GenericDelegator.storeAll(GenericDelegator.java:1428)
      	at com.opensymphony.module.propertyset.ofbiz.OFBizPropertySet.setImpl(OFBizPropertySet.java:182)
      	at com.opensymphony.module.propertyset.AbstractPropertySet.set(AbstractPropertySet.java:570)
      	at com.opensymphony.module.propertyset.AbstractPropertySet.setString(AbstractPropertySet.java:368)
      	at com.atlassian.jira.propertyset.JiraCachingPropertySet.setString(JiraCachingPropertySet.java:615)
      

      Notes

      According to entitymodel.xml column SEQ_ID in table SEQUENCE_VALUE_ITEM defined with numeric type

          <entity entity-name="SequenceValueItem" table-name="SEQUENCE_VALUE_ITEM" package-name="">
              <field name="seqName" type="id-long-ne"/>
              <field name="seqId" type="numeric"/>
              <prim-key field="seqName"/>
          </entity>
      

      Which for Mysql defined (in fieldtype-mysql.xml) as decimal(18,0) and mapped to a Java Long:

          <field-type-def type="numeric" sql-type="DECIMAL(18,0)" java-type="Long"><validate name="isSignedLong" /></field-type-def>
      

      In SequenceUtil in ofbcore-jira-entity-2.1.7.jar in lines 205, 237 called method ResultSet.getInt, instead of ResultSet.getLong. This causes total JIRA unavailability after one of Sequence (in our case OSPropertyEntry) surpasses the border of the upper signed integer limit. This limit is Integer.MAX_VALUE, 2147483647.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              rtandon@atlassian.com Ruchi Tandon
            • Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: