PMSE tables cleaning in database

Dear developpers,

I would like to make a cleaning for the executed BPM processes, is it enough to remove data from the table PMSE_INBOX or is there other table to cleanup ?

Additionnaly, I would do the same for the emails, I want to keep only one year of data, I identified the following tables :

emails
emails_beans
emails_email_addr_rel
emails_text

What do you propose ? Did you face the same issue ? What is the best practice to make a safe cleanup / archiving ?

Thanks in advance,

Enes

0 Harald Kuske over 5 years ago

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.

Harald Kuske
Principal Solution Architect – Professional Services, EMEA
hkuske@sugarcrm.com
SugarCRM Deutschland GmbH
0 Enrico Simonetti over 5 years ago
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 Simonetti

Sugar veteran (from 2007)

www.naonis.tech

Feel free to reach out for consulting regarding:

API Integration and Automation Services

Sugar Architecture

Sugar Performance Optimisation

Sugar Consulting, Best Practices and Technical Training

AWS and Sugar Technical Help

CTO-as-a-service

Solutions-as-a-service

and more!

All active SugarCRM certifications

Actively working remotely with customers based in APAC and in the United States
0 Jeff Bickart over 5 years ago in reply to Enrico Simonetti

Enrico Simonettican you update your Toothpaste README to let us know what the tool does?
0 Enes Saridogan over 5 years ago in reply to Harald Kuske

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
0 Enes Saridogan over 5 years ago in reply to Enrico Simonetti

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
+1 Enrico Simonetti over 5 years ago in reply to Enes Saridogan
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!
--

Enrico Simonetti

Sugar veteran (from 2007)

www.naonis.tech

Feel free to reach out for consulting regarding:

API Integration and Automation Services

Sugar Architecture

Sugar Performance Optimisation

Sugar Consulting, Best Practices and Technical Training

AWS and Sugar Technical Help

CTO-as-a-service

Solutions-as-a-service

and more!

All active SugarCRM certifications

Actively working remotely with customers based in APAC and in the United States
+1 Junaid Usman over 5 years ago

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
0 Harald Kuske over 5 years ago in reply to Junaid Usman

Looks like very hot stuff ...

Harald Kuske
Principal Solution Architect – Professional Services, EMEA
hkuske@sugarcrm.com
SugarCRM Deutschland GmbH
0 Junaid Usman over 5 years ago in reply to Harald Kuske

As it is cooked recently so definitely very hot
0 Enes Saridogan over 5 years ago in reply to Enrico Simonetti

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