Author: Michelle McCausland

Example support ticket - SUPPORT-490

When I try to advance my ticket 994997 from "Resource Planning in Progress" to "Errigal Development in Progress" it kicks me back to "Resource Planning in Progress".  

Attempting to recreate this issue showed the following in the logs:

2017-08-28 17:13:06,492 [http-bio-8083-exec-164] INFO  ticketer.Ticket  - Scheduling Ticket 994997 Auto-escalate (Errigal Development in Progress-1) in 6048000000 ms.
2017-08-28 17:13:06,529 [http-bio-8083-exec-164] ERROR util.JDBCExceptionReporter  - Data truncation: Out of range value for column 'repeatable_interval' at row 1
2017-08-28 17:13:06,529 [http-bio-8083-exec-164] ERROR util.JDBCExceptionReporter  - Data truncation: Out of range value for column 'repeatable_interval' at row 1
2017-08-28 17:13:06,548 [http-bio-8083-exec-164] ERROR StackTrace  - Full Stack Trace:
org.springframework.dao.DataIntegrityViolationException: could not insert: [com.errigal.ticketer.PersistedTask]; SQL [insert into persisted_task (closure_string, description, execute_date, executed, repeatable, repeatable_interval) values (?, ?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.DataException: could not insert: [com.errigal.ticketer.PersistedTask]
        at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:648)
        at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
        at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
        at org.codehaus.groovy.grails.orm.hibernate.metaclass.SavePersistentMethod.performSave(SavePersistentMethod.java:56)
        at org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractSavePersistentMethod.doInvokeInternal(AbstractSavePersistentMethod.java:215)
        at org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractDynamicPersistentMethod.invoke(AbstractDynamicPersistentMethod.java:63)
        at org.codehaus.groovy.grails.commons.metaclass.DynamicMethodInvocation$invoke.call(Unknown Source)
        at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)
        at org.codehaus.groovy.grails.commons.metaclass.DynamicMethodInvocation$invoke.call(Unknown Source)
        at org.codehaus.groovy.grails.orm.hibernate.HibernateGormInstanceApi.save(HibernateGormEnhancer.groovy:876)
        at com.errigal.ticketer.PersistedTask.save(PersistedTask.groovy)
        at com.errigal.ticketer.PersistedTask$save.call(Unknown Source)
        at com.errigal.ticketer.Ticket.scheduleAutoEscalate(Ticket.groovy:1958)
        at com.errigal.ticketer.Ticket$scheduleAutoEscalate.callCurrent(Unknown Source)
        at com.errigal.ticketer.Ticket.updateState(Ticket.groovy:1714)
        at com.errigal.ticketer.Ticket.this$2$updateState(Ticket.groovy)

Googling the following phrase “Data truncation: Out of range value for column” revealed the issue was to do with the column size.

In the Ticket.groovy code the PersistedTask section converts time to milliseconds in order to schedule its updates.

As you can see from the logs above the value was 6048000000ms!

Opening up the workflow to the state in question confirmed this:

10 weeks = 6048000000 ms

DESC persisted_task;

+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| id                  | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| closure_string      | varchar(8192) | NO   |     | NULL    |                |
| description         | varchar(255)  | NO   |     | NULL    |                |
| execute_date        | datetime      | NO   | MUL | NULL    |                |
| executed            | bit(1)        | NO   |     | NULL    |                |
| repeatable          | bit(1)        | YES  |     | NULL    |                |
| repeatable_interval | int(11)       | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+

The repeat_interval column's data type should be changed to a larger data type to accommodate the large value.

In order to verify this, the escalation time was set to 3 weeks instead of 10. This corrected the issue.

As it is not a viable option to just reduce the time, the column needed to be changed.

The following query was run to accommodate this change:

ALTER TABLE persisted_task MODIFY COLUMN repeatable_interval bigint;

DESC persisted_task;

+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| id                  | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| closure_string      | varchar(8192) | NO   |     | NULL    |                |
| description         | varchar(255)  | NO   |     | NULL    |                |
| execute_date        | datetime      | NO   | MUL | NULL    |                |
| executed            | bit(1)        | NO   |     | NULL    |                |
| repeatable          | bit(1)        | YES  |     | NULL    |                |
| repeatable_interval | bigint(20)    | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+

Increasing the escalate period back to 10 weeks and testing verified this corrected the issue!