I deleted all archived cases but in the database arc_ tables have a lot of data in them
Hello!
I deleted all archived cases in the bonita portal, but if i check my database only in my arch_flownode table i have more than 2 million records. Is this a bug? Or somewhere the app malfunctioned, and didnt delete the process instances? Is it safe to just delete all data from the arc_ tables?
Or is it normal to keep these records even if you dont need them?
Zoltan
Hello,
When you delete a case via the API, all relatives tables should be deleted.
How did you delete the cases?
There is a plug-in in the Truckmilk tool named "dross data". This plug-in track all the "dross" data and purge them.
For example, if you have a record in ARCH_FLOWNODE which is not related to a process instance, then this tool will detect it and then purge it. And there is many use case when you may have some "dross data".
Here the link to the Truckmilk page
https://community.bonitasoft.com/project/pagetruckmilk-execute-jobs-inte...
and a PY Tutorial to explain what it is https://youtu.be/O6Ef7kEe0Ps
Hope this will help you to reduce your database!
PS: Truckmilk has a lot of options too to purge your old cases based on multiple criteria.
Comments
Thank you, we will try this solution, it is really helpful :)
Does it work with bonita version 7.11.4?
I tried to install, but the jar file that needs to be copied to the server doesnt exist in the latest version of TruckMilk. Is it not neccesary to put jar file to the server? Or it has a different name?
Edit: I downloaded the jar from github, but the job stays in pending_start, and doesnt change to executing.
Edit2: The video and the documentation is different a little :) In the docs i need to find the *Quarz* jar in the lib folder, however in the video it is clear that i need the additionallib folder, and not just the Quarz file. However if i am not mistaken in the maintenance tab with the redeploy the libs are put to the place where it should be.
After trying a few times i still cant get it working pending_start is shown, it never runs. Im using 7.11.4 version of bonita, i know the user is always at fault, but could someone give an advice how to make this work? :)
Hello,
Thank you for your help, i managed to make it work using your guidence. The second point was that i missed, getStatus, and start was the key to make it work. I first tried locally, everything seemed fine, the docker instance was a bit tricky, because i needed to put the *.jar-s in the web-inf/lib folder in the image, but after some thinking i could do it, it works fine on the docker instance of Bonita 7.11.4.
Regards
Zoltan
I have two more questions :) Both are probably our wrong doing.
1. I needed to change my docker toolbox to docker desktop, so i did a fresh reinstall, and now the heartbeat function doesnt work. In the next heartbeat field i see this: [undefined] Any ideas?
2. We probably messed up at the beginning and started to delete the arch_ table records, to be honest i dont really remember, but i think we did, because the SQL size was to big, and we didnt insert everything after reloading the backup. That is how i remember, maybe we didnt do such a thing on the live server. I know i did it on my developer envirement.. and had similar issues. Is there a way to save/clean this database? We are getting this error:
2021-02-25 17:35:07.395 +0000 INFO: org.bonitasoft.radar.archive.RadarCleanArchivedDross RadarCleanArchivedDross ## DeleteResult:[0] 10First[39880,] Errors? true
2021-02-25 17:35:14.537 +0000 INFO: org.bonitasoft.radar.archive.RadarCleanArchivedDross RadarCleanArchivedDross ## QueryResult:[1] with TenantId[1] MaxNumberToProcess[30000] SQLQUERY:select id from arch_process_instance where tenantid = ? and stateid not in (3,4,6) and not exists (select * from arch_process_instance ar where arch_process_instance.sourceobjectid = ar.sourceobjectid and ar.stateid in (3,4,6)) and not exists (select * from process_instance ar where arch_process_instance.sourceobjectid = ar.id and ar.tenantid=? )
2021-02-25 17:35:14.540 +0000 SEVERE: org.bonitasoft.radar.archive.RadarCleanArchivedDross RadarCleanArchivedDross ##During getCountOfFlowNode : java.sql.SQLException: You can't specify target table 'arch_process_instance' for update in FROM clause SqlQuery[delete from arch_process_instance where tenantid = ? and stateid not in (3,4,6) and not exists (select * from arch_process_instance ar where arch_process_instance.sourceobjectid = ar.sourceobjectid and ar.stateid in (3,4,6)) and not exists (select * from process_instance ar where arch_process_instance.sourceobjectid = ar.id and ar.tenantid=? ) and id in (?)] at java.sql.SQLException: You can't specify target table 'arch_process_instance' for update in FROM clause
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1109)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1057)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1377)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1042)
at org.bonitasoft.radar.archive.RadarCleanArchivedDross.executeUpdate(RadarCleanArchivedDross.java:692)
at org.bonitasoft.radar.archive.RadarCleanArchivedDross.deleteDross(RadarCleanArchivedDross.java:634)
at org.bonitasoft.truckmilk.plugin.cases.MilkCleanArchivedDross.executeJob(MilkCleanArchivedDross.java:173)
at org.bonitasoft.truckmilk.engine.MilkExecuteJobThread.run(MilkExecuteJobThread.java:213)
2021-02-25 17:35:14.540 +0000 INFO: org.bonitasoft.radar.archive.RadarCleanArchivedDross RadarCleanArchivedDross ## DeleteResult:[0] 10First[39880,] Errors? true
2021-02-25 17:35:16.599 +0000 INFO: org.bonitasoft.radar.archive.RadarCleanArchivedDross RadarCleanArchivedDross ## QueryResult:[1] with TenantId[1] MaxNumberToProcess[10] SQLQUERY:select id from arch_process_instance where tenantid = ? and stateid not in (3,4,6) and not exists (select * from arch_process_instance ar where arch_process_instance.sourceobjectid = ar.sourceobjectid and ar.stateid in (3,4,6)) and not exists (select * from process_instance ar where arch_process_instance.sourceobjectid = ar.id and ar.tenantid=? )
2021-02-25 17:35:16.602 +0000 SEVERE: org.bonitasoft.radar.archive.RadarCleanArchivedDross RadarCleanArchivedDross ##During getCountOfFlowNode : java.sql.SQLException: You can't specify target table 'arch_process_instance' for update in FROM clause SqlQuery[delete from arch_process_instance where tenantid = ? and stateid not in (3,4,6) and not exists (select * from arch_process_instance ar where arch_process_instance.sourceobjectid = ar.sourceobjectid and ar.stateid in (3,4,6)) and not exists (select * from process_instance ar where arch_process_instance.sourceobjectid = ar.id and ar.tenantid=? ) and id in (?)] at java.sql.SQLException: You can't specify target table 'arch_process_instance' for update in FROM clause
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1109)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1057)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1377)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1042)
at org.bonitasoft.radar.archive.RadarCleanArchivedDross.executeUpdate(RadarCleanArchivedDross.java:692)
at org.bonitasoft.radar.archive.RadarCleanArchivedDross.deleteDross(RadarCleanArchivedDross.java:634)
at org.bonitasoft.truckmilk.plugin.cases.MilkCleanArchivedDross.executeJob(MilkCleanArchivedDross.java:173)
at org.bonitasoft.truckmilk.engine.MilkExecuteJobThread.run(MilkExecuteJobThread.java:213)
1. I only have this in the log when i reset the scheduler:
2021-02-26 07:02:48.375 +0000 INFO: org.bonitasoft.truckmilk.engine.MilkReportEngine #BonitaCommandAPI TruckMilk.MilkReportEngine: MilkReportEngine ~~ 26/02 07:02:48 Scheduler is started; Schedule reset; Quartz Job Up and running; Heat beat informationLast Heart Beat[undefined], Next[undefined];
And i see this if i reload the page:
2021-02-26 07:06:27.853 +0000 INFO: org.bonitasoft.console.common.server.page.PageResourceServlet This servlet is deprecated. You can now access your resources through their relative URL.see the custom page documentation.
2021-02-26 07:06:28.084 +0000 INFO: org.bonitasoft.command.BonitaCommand BonitaCommand ~~~Verb[GETSTATUS] Tenant[1] Result: in 8 ms
I also found something like this:
2021-02-26 07:10:05.354 +0000 INFO: org.bonitasoft.truckmilk.engine.MilkCmdControl #BonitaCommandAPI TruckMilk.MilkCmdControl: SchedulerStartStop requested[null] -
I did some research, i get #1093 in MySQL, here is a link that explains how i cant select and delete the same table in the same query. A looked at the MySQL docs, this error persists in the later versions of MySQL, in 8.0 this error still exists. Updating the database couldnt help, the reason why it could run in local bonita is because it uses H2 database not MySQL, probably it would work with PostgreSQL. This is what i could come up with after some reasearch :)
https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-...
And this is my query:
delete from arch_process_instance where tenantid = 1 and stateid not in (3,4,6) and not exists (select * from arch_process_instance ar where arch_process_instance.sourceobjectid = ar.sourceobjectid and ar.stateid in (3,4,6)) and not exists (select * from process_instance ar where arch_process_instance.sourceobjectid = ar.id and ar.tenantid=1 ) and id in (1)
2. I used 7.9.0 and then migrated to 7.11.4 (the migration tool said 7.11.0, but we are using 7.11.4 and seem to work fine. The database is something we cant change because we are not the maintainer of it, but it is a MySQL 5.7.33. Maybe we really f.cked up at the beggining. I should add that the heartbeat function worked well, with the database, in Docker Toolbox.
Unfortunately we are not using a subscription model.
Hello,
for the [undefined], I got an explanation: with Quartz, it's not possible to get the time of the next execution. So, the time is "undefined"... I changed this message to "in the minute" (or something like that)
For the MySql request and the "not possible for MySql to delete something based on the same table", ouah, I didn't know that! This page was developed / tested on H2, Postgres, Oracle.
So, best is to change and play the request by yourself. Go to the "parameters" section, and you will see all SQL requests. Execute the job in "detection", then you know where you have some purge to execute. Let's say that you detect some items in a section. You have a code in each section. Back to the parameter, you have the SQL Request behind the code.
Change it to be acceptable by MySql, then replay it.
Best,
Comments
Hello
Once i made a mistake on a gateway. The condition was wrong. The Loop went on for 2 Months.
Each time the loop was traversed, a entry was made in ARCH_FLOWNODE table.
After one month, i had 300 Million records. Table space went upto 950 GB.
I still have that data. I scared to delete as i am not sure what else will get affected.
Let me know if you find a solution.
regards