Details
-
Bug
-
Resolution: Obsolete
-
High
-
4.2.1, 4.4.5
-
Databases: MySQL
-
4.02
-
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
- Install JIRA with a MySQL database.
- Shutdown JIRA.
- Log into MySQL.
- Perform the below query:
UPDATE SEQUENCE_VALUE_ITEM SET SEQ_ID = 2147483648;
This will update all sequences to that sequence ID. - 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.