[JRASERVER-26636] Database sequences can reach a certain size that prevents additional sequences from being created Created: 21/Dec/2011  Updated: 20/Feb/2017  Resolved: 26/May/2015

Status: Resolved
Project: JIRA Server (including JIRA Core)
Component/s: Infrastructure & Services - Application Lifecycle
Affects Version/s: 4.2.1, 4.4.5
Fix Version/s: 6.4.4

Type: Bug Priority: High
Reporter: Ruchi Tandon Assignee: Unassigned
Resolution: Obsolete Votes: 1
Labels: affects-server, slush
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Databases: MySQL


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.



 Comments   
Comment by David Currie [ 26/May/2015 ]

Tested this on JIRA 6.4.4 using MySQL and was not able to replicate, the sequences are correctly increased:

mysql> select * from SEQUENCE_VALUE_ITEM;
+-----------------------------+------------+
| SEQ_NAME                    | SEQ_ID     |
+-----------------------------+------------+
| Action                      | 2147483748 |
| ApplicationUser             | 2147483648 |
| AuditChangedValue           | 2147483648 |
| AuditItem                   | 2147483648 |
| AuditLog                    | 2147483648 |
| Avatar                      | 2147483648 |
| ChangeGroup                 | 2147483648 |
| ChangeItem                  | 2147483648 |
| ConfigurationContext        | 2147483648 |
| CustomField                 | 2147483648 |
| CustomFieldOption           | 2147483648 |
| CustomFieldValue            | 2147483748 |
| Directory                   | 2147483648 |
| EntityPropertyIndexDocument | 2147483648 |
| EventType                   | 2147483648 |
| ExternalEntity              | 2147483648 |
| Feature                     | 2147483648 |
| FieldConfigScheme           | 2147483648 |
| FieldConfigSchemeIssueType  | 2147483648 |
| FieldConfiguration          | 2147483648 |
| FieldLayout                 | 2147483648 |
| FieldLayoutItem             | 2147483648 |
| FieldScreen                 | 2147483648 |
| FieldScreenLayoutItem       | 2147483648 |
| FieldScreenScheme           | 2147483648 |
| FieldScreenSchemeItem       | 2147483648 |
| FieldScreenTab              | 2147483648 |
| GadgetUserPreference        | 2147483648 |
| GenericConfiguration        | 2147483648 |
| GlobalPermissionEntry       | 2147483648 |
| Group                       | 2147483648 |
| Issue                       | 2147483748 |
| IssueLink                   | 2147483648 |
| IssueLinkType               | 2147483648 |
| IssueType                   | 2147483648 |
| IssueTypeScreenScheme       | 2147483648 |
| IssueTypeScreenSchemeEntity | 2147483648 |
| ListenerConfig              | 2147483648 |
| ManagedConfigurationItem    | 2147483648 |
| Membership                  | 2147483648 |
| Notification                | 2147483648 |
| NotificationScheme          | 2147483648 |
| OSCurrentStep               | 2147483748 |
| OSGroup                     | 2147483648 |
| OSMembership                | 2147483648 |
| OSPropertyEntry             | 2147483748 |
| OSUser                      | 2147483648 |
| OSWorkflowEntry             | 2147483748 |
| OptionConfiguration         | 2147483648 |
| PermissionScheme            | 2147483648 |
| PluginVersion               | 2147483648 |
| PortalPage                  | 2147483648 |
| PortletConfiguration        | 2147483648 |
| Priority                    | 2147483648 |
| Project                     | 2147483648 |
| ProjectKey                  | 2147483648 |
| ProjectRole                 | 2147483648 |
| ProjectRoleActor            | 2147483648 |
| ReindexComponent            | 2147483648 |
| ReindexRequest              | 2147483648 |
| RememberMeToken             | 2147483648 |
| Resolution                  | 2147483648 |
| RunDetails                  | 2147483748 |
| SchemePermissions           | 2147483648 |
| SearchRequest               | 2147483648 |
| ServiceConfig               | 2147483648 |
| SharePermissions            | 2147483648 |
| Status                      | 2147483648 |
| TaskIdSequence              | 2147483648 |
| UpgradeHistory              | 2147483648 |
| UpgradeVersionHistory       | 2147483648 |
| User                        | 2147483648 |
| UserAttribute               | 2147483648 |
| UserHistoryItem             | 2147483748 |
| Version                     | 2147483648 |
| Workflow                    | 2147483648 |
| WorkflowScheme              | 2147483648 |
| WorkflowSchemeEntity        | 2147483648 |
+-----------------------------+------------+
78 rows in set (0.00 sec)

Test was to create a new issue and comment on it, we can see the below are successfully incremented:

mysql> select * from SEQUENCE_VALUE_ITEM where SEQ_ID != 2147483648;
+------------------+------------+
| SEQ_NAME         | SEQ_ID     |
+------------------+------------+
| Action           | 2147483748 |
| CustomFieldValue | 2147483748 |
| Issue            | 2147483748 |
| OSCurrentStep    | 2147483748 |
| OSPropertyEntry  | 2147483748 |
| OSWorkflowEntry  | 2147483748 |
| RunDetails       | 2147483748 |
| UserHistoryItem  | 2147483748 |
+------------------+------------+
8 rows in set (0.00 sec)
Comment by Matt Doar [ServiceRocket] [ 19/Nov/2015 ]

I would expect this still exists. It may depend on the OS on which MySQL is running, or version of MySQL. Also I don't see how your test shows that the seq_ids have been incremented. You seem to be searching for ones that have not changed.

Comment by David Currie [ 19/Nov/2015 ]

This is true, I just tested this on JIRA 7 and wasn't able to repro. Test was:

  1. Stop JIRA.
  2. Update sequences:
    mysql> select * from sequence_value_item;
    +-----------------------------+------------+
    | SEQ_NAME                    | SEQ_ID     |
    +-----------------------------+------------+
    | ApplicationUser             | 2147483648 |
    | AuditChangedValue           | 2147483648 |
    | AuditItem                   | 2147483648 |
    | AuditLog                    | 2147483648 |
    | Avatar                      | 2147483648 |
    | ClusteredJob                | 2147483748 |
    | ConfigurationContext        | 2147483648 |
    | CustomField                 | 2147483648 |
    | CustomFieldOption           | 2147483648 |
    | DraftWorkflow               | 2147483648 |
    | EntityProperty              | 2147483648 |
    | EntityPropertyIndexDocument | 2147483648 |
    | EventType                   | 2147483648 |
    | Feature                     | 2147483648 |
    | FieldConfigScheme           | 2147483648 |
    | FieldConfigSchemeIssueType  | 2147483648 |
    | FieldConfiguration          | 2147483648 |
    | FieldLayout                 | 2147483648 |
    | FieldLayoutItem             | 2147483648 |
    | FieldScreen                 | 2147483648 |
    | FieldScreenLayoutItem       | 2147483648 |
    | FieldScreenScheme           | 2147483648 |
    | FieldScreenSchemeItem       | 2147483648 |
    | FieldScreenTab              | 2147483648 |
    | GadgetUserPreference        | 2147483648 |
    | GenericConfiguration        | 2147483648 |
    | GlobalPermissionEntry       | 2147483648 |
    | Group                       | 2147483648 |
    | Issue                       | 2147483648 |
    | IssueLinkType               | 2147483648 |
    | IssueType                   | 2147483648 |
    | IssueTypeScreenScheme       | 2147483648 |
    | IssueTypeScreenSchemeEntity | 2147483648 |
    | LicenseRoleDefault          | 2147483648 |
    | LicenseRoleGroup            | 2147483648 |
    | ListenerConfig              | 2147483648 |
    | ManagedConfigurationItem    | 2147483648 |
    | Membership                  | 2147483648 |
    | Notification                | 2147483648 |
    | NotificationScheme          | 2147483648 |
    | OAuthConsumer               | 2147483648 |
    | OptionConfiguration         | 2147483648 |
    | OSCurrentStep               | 2147483648 |
    | OSPropertyEntry             | 2147483748 |
    | OSWorkflowEntry             | 2147483648 |
    | PluginVersion               | 2147483648 |
    | PortalPage                  | 2147483648 |
    | PortletConfiguration        | 2147483648 |
    | Priority                    | 2147483648 |
    | ProductLicense              | 2147483648 |
    | Project                     | 2147483648 |
    | ProjectKey                  | 2147483648 |
    | ProjectRole                 | 2147483648 |
    | ProjectRoleActor            | 2147483648 |
    | ReindexComponent            | 2147483648 |
    | ReindexRequest              | 2147483648 |
    | RememberMeToken             | 2147483648 |
    | Resolution                  | 2147483648 |
    | RunDetails                  | 2147483748 |
    | SchemePermissions           | 2147483648 |
    | SearchRequest               | 2147483648 |
    | ServiceConfig               | 2147483648 |
    | SharePermissions            | 2147483648 |
    | Status                      | 2147483648 |
    | TaskIdSequence              | 2147483648 |
    | UpgradeHistory              | 2147483648 |
    | UpgradeVersionHistory       | 2147483648 |
    | User                        | 2147483648 |
    | UserAttribute               | 2147483648 |
    | UserHistoryItem             | 2147483648 |
    | Workflow                    | 2147483648 |
    | WorkflowScheme              | 2147483648 |
    | WorkflowSchemeEntity        | 2147483648 |
    +-----------------------------+------------+
    73 rows in set (0.00 sec)
    
  3. Start JIRA.
  4. Create a new custom field.
  5. Check logs and sequences:
    mysql> select * from sequence_value_item where seq_id > 2147483648;
    +----------------------------+------------+
    | SEQ_NAME                   | SEQ_ID     |
    +----------------------------+------------+
    | AuditChangedValue          | 2147483748 |
    | AuditLog                   | 2147483748 |
    | ClusteredJob               | 2147483748 |
    | ConfigurationContext       | 2147483748 |
    | CustomField                | 2147483748 |
    | CustomFieldOption          | 2147483748 |
    | FieldConfigScheme          | 2147483748 |
    | FieldConfigSchemeIssueType | 2147483748 |
    | FieldConfiguration         | 2147483748 |
    | OSPropertyEntry            | 2147483748 |
    | RunDetails                 | 2147483748 |
    +----------------------------+------------+
    

Worked fine to me - if you can repro please give us exact steps and we can investigate further.

Generated at Thu Apr 19 13:54:08 UTC 2018 using JIRA 7.9.0-m0002#79000-sha1:c2c604d204a01a7a5c2a35ed342adc394ebbb6b0.