Deleting data from jbpm tables in database

[ksdeger]

We have a system that is successfully using JBPM. It creates a very large number of process instances a day, so we would like to delete them when they have completed to free up space in the database.

I am going to write code using GraphSession.deleteProcessInstance to do this, but until I can get that done I was hoping to put out a quick SQL script to do the clean up. I have the SQL script completed, but there is something odd going on. The jbpm_bytearray table has a large number of rows with no values in the name_ or filedefinition_ columns. Also, the id_'s for these rows do not appear in the jbpm_variableinstance bytearrayvalue_ column. I looked at the jbpm hibernate cfg.xml files, and I can't figure out where these rows are coming from, or how I would know to which process instance they belongs.

Just as an example, I ran a single file through our JBPM flow, and after the clean up, which removes all the jbpm_bytearray and jbpm_byteblock rows which have valid references in the jbpm_variableinstance table, there were still 276 new rows in the jbpm_blockbyte table.

I cannot leave this data to accumulate as it eventually kills the database.

Any suggestions? We are using JBoss 4.0.1sp1 and JBPM 3.0.2.

[kukeltje]

Bytearrays....nothing odd, by design. The processdefinition and all accompanying data is stored that way in blocks of configurable size. The other rows are always related to a 'first row'. There are kind of sequencenumbers in there (cannot tell you the details now since I do not have access to any code)

Just look a little better, you'll see the relation

[kukeltje]

oh, and btw. the bytearrays are afaik related to a process definition, not process instance

[ksdeger]

I've examined the sql code that creates the tables and all the relationships when the jbpm database is created. The only relationships I see for bytearray, based on foreign keys are:

jbpm_bytearray's filedefinition_ has a foreign key relationship to jbpm_moduledefinition (but filedefinition_ is empty in the rows in question)

jbpm_variableinstance's bytearrayvalue_ has a foreign key relationship to jbpm_bytearray

jbpm_byteblock's processfile_ has a foreign key relationship to jbpm_bytearray

(Ok, there are also some relationships from the log, but that's not important right now).

jbpm_variableinstance has a relationship to a jbpm_processinstance but not to jbpm_processdefiniton, exept indirectly.

What am I missing? I've looked at the hbm.xml files and found the same relationships and nothing else. Did something change with Jbpm 3.1?