Workflow Arithmetic overflow.

replied on April 22, 2016

The procedure is listed below. I don't think that the issue is with SQL itself, but rather an issue with Workflow using an int32 vs int64 for the expected return type of that procedure call.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[insert_executing_activity]
   @internal_id       bigint,
   @activity_name       nvarchar(256),
   @context_id           uniqueidentifier,
   @parent_context_id   uniqueidentifier,
   @status_time       datetime = null
as
   begin
       set nocount on

       declare @activity_id bigint
       insert into search_activity (search_id, activity_name, context_id, parent_context_id, start_time, end_time)
       values (@internal_id, @activity_name, @context_id, @parent_context_id, @status_time, GETUTCDATE());

       select @activity_id = SCOPE_IDENTITY()
       return @activity_id
       done:
   end

0 0

View 4 previous replies

replied on April 22, 2016

Ok, we figured out why this happens, but it's not an easy fix. Could you contact your LF reseller and open a support case so we can investigate how it happened, please? Depending on the data in the table we may have an easier solution.

Please run the following query and add the results to the support case too.

SELECT TOP 1 [activity_id]
 FROM [dbo].[search_activity]
ORDER BY [activity_id]

0 0

replied on April 22, 2016

I will go ahead and open a support ticket. What appeared to resolve the spam of errors was resetting the auto increment seed on the search_activities table, not sure how long that will last for us, but it appears to have silenced the errors and we have not had any adverse issues since that time. (This was shortly after I made my initial post)

Below is our min and max activity_id on the search_activity table.

SELECT TOP 1 [activity_id]
FROM [dbo].[search_activity]
ORDER BY [activity_id]

activity_id
min: 6,612,594

max: 2,475,533,320

On a side note, all of our workflows are built without any wait, or parent conditions, so in worst case scenarios we re-create the workflow database and import the latest version of our workflows. While it isn't an ideal situation, it helps protect us from the abnormal issues such as these. We have an extreme amount of volume come through our repositories, as you can imagine with a search_activity id that high.

Thanks for your help!

0 0

SELECTED ANSWER

replied on April 22, 2016

Yes, reseeding the table is the easy solution, but figuring out why you've run over 2 billion activities is probably going to help us find a better solution. Assuming about 4 years since Workflow was released with this table structure, that's averaging 500 million activities a year, which is more than one 1 million a day. It's possible that's a real number, but it's more likely that you have some sort of infinite loop. Tech Support can help diagnose that.

Did you re-seed the identity at 1 and it's now at 6 million (within less than a week)?

0 0

replied on April 22, 2016 • Show version history

I didn't think it is 1 to 1 with a workflow, but rather pertains to a single activity that takes place in the workflow itself. For example I have the following activity names.

...

IsValid
IsValid_Children
LookforPatientFolder
FolderFound
No
No_Children
CreatePatientFolder
MoveEntry
SetPatientFolderTemplate
ImportDocuments
RetrieveFieldValues
Tokens
CorrectHospitalValue
...

So, every time a workflow runs, it inserts every inner activity into this table?

We receive about 100,000 new documents per day and each document runs through 2-4 workflows. Each of those workflows have quite a few inner activities.

I could be wrong, but this is what I have deduced from looking at the table.

0 0

replied on April 22, 2016

Right, it would insert a row for each activity that runs.

0 0

replied on April 22, 2016

Appreciate the help Miruna. We are working on an architecture that will scale out and more than likely eliminate the possibility of this occurring again. I just wanted to let you know what happened since I assumed it was a bug. Glad you guys have found the cause. We should be good on our end for a while and I will know what to do if we get back to that number again.

Thanks again.

0 0

replied on April 25, 2016

Yeah, since you have no wait conditions, it's unlikely you'll run into data conflicts with the re-seeded identity value. Even at 1 million activities a day, you should be good for at least another 5 years or so. We'll release the fix with the next version.

0 0

Question

Question

Answer

Replies

Sign in to reply to this post.