Tidying the alfresco workflow database

During an upgrade from Alfresco 4.2.5.1 to 4.2.6 for a client of ours we identified a problematic patch script which does some refactoring of data in the Activiti tables in the database. The customer system has been running for many years and the Activiti historic tables have grown large due to the fact that Alfresco never cleans these automatically. All workflow and task data is stored indefinitely in the database in the act_hi_*.

For this particular system the act_hi_detail table contained 2.1 million records (not that many for a database), however, the nasty SQL used to refactor data in the patch does not work well for a system of this size with that many workflows. The total number of Activiti processes (active and completed) were about 21000 and the patch script ran in our QA environment for many hours. Too many hours for us to have scheduled downtime, so we decided to cancel the upgrade script and find an alternate solution.

After some investigation and some trial and errors we found that when deleting a workflow using the workflow service it clears up the Activiti history tables. This can also be done from the UI per workflow after a workflow has completed. Then you have the option to delete the workflow from the workflow details page. This is nothing you do manually for 21k workflows so here comes the JavaScript console to the rescue!

var ctx = Packages.org.springframework.web.context.ContextLoader.getCurrentWebApplicationContext();
var log = Packages.org.apache.log4j.Logger.getLogger("RL_CANCEL_DELETED_WORKFLOWS");
var workflowService = ctx.getBean('WorkflowService');
logger.log("Starting cancel workflow script");
log.error("Starting cancel workflow script");

var completedWorkflows = workflowService.getCompletedWorkflows();
var limit = 5000;
logger.log("Limit is: "+limit);
log.error("Limit is: "+limit);
logger.log("Number of completed workflows: "+completedWorkflows.size());
log.error("Number of completed workflows: "+completedWorkflows.size());
if (completedWorkflows) {
	for (var i=0;i<completedWorkflows.size();i++) {
		var wf = completedWorkflows.get(i);
		if (wf.isActive()) {
			logger.log("Workflow is still active: "+wf.getId());
			log.error("Workflow is still active: "+wf.getId());
		} else {
			logger.log("Deleting workflow: "+wf.getId());
			log.error("Deleting workflow: "+wf.getId());
			workflowService.deleteWorkflow(wf.getId());
		}
		if (i>=limit) {
			break;
		}
	}
}
logger.log("Finished cancel workflow script");
log.error("Finished cancel workflow script");

This script will delete all completed workflows, both old jBPM workflows and Activiti workflows. The script will most likely time out when you run it so we added some log4j logging to it as well to get a log trail in our alfresco.log.

Since we are doing some workflow maintenance here we might as well delete all Active old jBPM workflows as well (in this case we knew for a fact that they will never be completed).

var ctx = Packages.org.springframework.web.context.ContextLoader.getCurrentWebApplicationContext();
var log = Packages.org.apache.log4j.Logger.getLogger("RL_DELETE_JBPM_WORKFLOWS");
var workflowService = ctx.getBean('WorkflowService');

var activeWorkflows = workflowService.getActiveWorkflows();
var limit = 2000;
logger.log("Limit is: "+limit);
log.error("Limit is: "+limit);
logger.log("Number of active workflows: "+activeWorkflows.size());
log.error("Number of active workflows: "+activeWorkflows.size());
if (activeWorkflows) {
	for (var i=0;i<activeWorkflows.size();i++) {
		var wf = activeWorkflows.get(i);
		if (!wf.isActive()) {
			logger.log("Workflow is not active: "+wf.getId());
			log.error("Workflow is not active: "+wf.getId());
		} else if (wf.getId().indexOf("activiti")===0) {
			logger.log("Activiti workflow: "+wf.getId());
			log.error("Activiti workflow: "+wf.getId());
		} else if (wf.getId().indexOf("jbpm")===0) {
			logger.log("Canceling jBPM workflow: "+wf.getId());
			log.error("Canceling jBPM workflow: "+wf.getId());
			workflowService.cancelWorkflow(wf.getId());
		} else {
			logger.log("Unknown workflow type"+wf.getId());
			log.error("Unknown workflow type"+wf.getId());
		}
		if (i>=limit) {
			break;
		}
	}
}

As a result of this maintenance job, we have about 1700 active activiti workflows and an act_hi_detail table with about 210000 rows (about 10% of the original count) and the patch went through in seconds.

This entry was posted in Uncategorized. Bookmark the permalink.

28 Responses to Tidying the alfresco workflow database

  1. I have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation.

  2. Tk6iiW Thank you ever so for you article.Really thank you! Keep writing.

  3. I’m glad I found this web site, I couldn’t find any knowledge on this matter prior to. Also, operate a site and if you are ever interested in doing some visitor writing for me if possible feel free to let me know, I’m always looking for people to check out my web site.

  4. xe88 tips says:

    i so like this blogger hihih..

  5. Name says:

    comment text
    link

  6. Alicia says:

    I really enjoy this superb article which you’ve provided for us. I guarantee this could be beneficial for the majority of the people. http://www.couponsviews.com

  7. You can definitely see your expertise within the work you write.

  8. wonderful issues altogether, you simply won a logo new reader. What would you suggest in regards to your post that you simply made a few days in the past? Any positive?

  9. dong ho fake says:

    Im obliged for the blog.Really looking forward to read more. Will read on

  10. eztv torrent says:

    This blog is really cool and besides diverting. I have picked many useful tips out of this source. I ad love to come back again soon. Thanks a bunch!

  11. Too many times I passed over this link, and that was a blunder. I am glad I will be back!

  12. This awesome blog is really awesome and besides amusing. I have discovered helluva handy advices out of this amazing blog. I ad love to visit it every once in a while. Cheers!

  13. Muchos Gracias for your blog post.Thanks Again. Much obliged.

  14. wholesale cheap jerseys ??????30????????????????5??????????????? | ????????

  15. Right now it sounds like BlogEngine is the preferred blogging platform available right now. (from what I ave read) Is that what you are using on your blog?

  16. There is evidently a bundle to realize about this. I assume you made certain nice points in features also.

  17. Your style is so unique in comparison to other people I ave read stuff from.

  18. Perfectly pent content material , appreciate it for entropy.

  19. 부커취 says:

    Thank you for your post.Really thank you! Cool.

  20. Major thankies for the blog.Much thanks again. Really Cool.

  21. click here says:

    Your style is very unique in comparison to other folks I ave read stuff from. I appreciate you for posting when you ave got the opportunity, Guess I will just book mark this page.

  22. It as not that I want to duplicate your web-site, but I really like the style and design. Could you tell me which theme are you using? Or was it especially designed?

  23. bacarasite says:

    Always so interesting to visit your site. What a great info, thank you for sharing. 바카라사이트

  24. What a great info, thank you for sharing. this will help me so much in my learning.When your website or blog goes live for the first time, it is exciting. That is until you realize no one but you and your. 파워볼사이트

  25. Wow, great post.Really looking forward to read more. Fantastic. 토토사이트

  26. Pinganillo says:

    Very good information. Lucky me I discovered your site by chance (stumbleupon). I have bookmarked it for later!

  27. Thankyou for this tremendous post, I am glad I observed this site on yahoo.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>