PMSE tables cleaning in database

For BPM pmse_inbox is surely not the only dynamic table which fills up, so you should look on all pmse_ tables and understand what they contain before you start to remove historic entries.

For emails you found all relevant tables. Be careful because email_texts does not contain any timestamp. They are all related by the id of the emails table which is referenced as email_id in the other tables.

For emails an additional module is important as all the attachments are saved in the notes module and uploaded as file to the upload directory. You find these notes by their field email_id which is set only for email attachments. If you delete the database record in the note table the linked file will be orphaned, so always use a V10 REST service to delete the note. The REST service removes the database entry and deletes the file from the upload directory.

All in all for Emails the deletion by V10 REST calls is the best choice of all as all entries in all tables are set to deleted and the uploaded file is removed from the upload directory.

Hi Enes Saridogan,

Is this for on-site?

If it is, for the BPM part you could have a look at a tool I built a while back, as-is (no official support) called toothpaste. Have a look at what it does behind the scenes: https://github.com/esimonetti/toothpaste 

Hope it helps

Enrico Simonettican you update your Toothpaste README to let us know what the tool does? 

Hello Harald,

Thanks for your feedback, it's ok for the emails_* tables, I'll do a check for pmse_inbox.

Have a nice day !!

Enes

Hello Enrico Simonetti,

Yes, it is on site. I see that it is only available for MySQL, our customer is running on Oracle Dabatase

According to that, we can also not use sugarcli.phar for cleaning the datase ... 

I'll have a look, thanks a lot !

Have a nice day !

Enes

Enes Saridogan,

I would recommend to have a look and to give it a try anyway. Only some commands are specific to MySQL. While the code has only been tested on MySQL, when using the ORM (SugarQuery or Doctrine) it should work as-is on all databases.

Hope it helps!

Hi Enes Saridogan

For cleaning up the SugarBPM processes, Sugar has the following ER: 79949 (https://portal.sugarondemand.com/#supp_Bugs/79949) that is fixed in release 10.3.0 Cloud only). I would suggest opening a ticket with the Sugar support portal and get a hotfix containing the ability to delete BPM tables via the scheduler job.

Kind Regards,

Junaid

Looks like very hot stuff ...

As it is cooked recently so definitely very hot

Hello Enrico,

It's working like a charm on a "classic" stack with MySQL, I'll check on Wednesday morning for the other customer. If it works, it will save me a lot of thing :)

The optimize command cleaned 1.1Gb of data on my local database !

I'll take you informed about that. 

See you there !

Best regards,

Enes