SuiteCRM stopped working: folders_rel folder filled up the entire disk space

egopak · 9 June 2022 14:47

Hello everyone,

I have a self-hosted SuiteCRM 7.11.19 with Email module monitoring several our inbound emails, with one of them having over 14GB of emails (old email box).

SuiteCRM uses a cron job to download new email every 5 minutes, and it seems to be working fine.

However, earlier today I could not longer log into my SuiteCRM, it said my session has expired.

I logged into my system over SSH and found out the disk was full (100GB) and narrowed it down to the overgrown MySQL database with the folders_rel table taking over 70GB).

71G     /var/lib/mysql/suitecrm/folders_rel.ibd

How can I safely clean up the folders_rel table?

Is this happening due to having Auto-Import function enabled on the inbound email boxes?

Any tips would be greatly appreciated.

pgr · 9 June 2022 14:49

Does your folders_rel table have many rows with deleted=1 ?

Perhaps you can start by clearing those, it shouldn’t make a difference.

But make sure you create full backups, just in case…

egopak · 9 June 2022 16:19

No, not a single one.

I’ll try to figure out why there are so many records. Other people have reported that each individual picture is stored as a note / an extra record in this table. Not sure if that’s at the root of this problem though. I think the issue is with Auto-Importing every single email into the database.

Maybe there is a way to delete all the attached pictures from the database (we certainly don’t need them under SuiteCRM).

pgr · 10 June 2022 12:17

Although I don’t use inbound email in SuiteCRM, and so I am not very knowledgeable on this, from what I’ve heard in these forums it’s definitely a good idea to manage the size of your IMAP inboxes in ways that hide the large amounts of email from SuiteCRM, because SuiteCRM will hit performance issues much sooner than any Gmail or Outlook.com.

If you can get a policy of removing or archiving older stuff, or using IMAP labels (folders) to keep a lot of stuff only server-side, and clearing that from SuiteCRM, it should really help.

Try to ensure that only stuff you’re currently working on, or that is relevant to what you’re working on, is stored in SuiteCRM.

egopak · 10 June 2022 14:07

Thank you for your input!

I’m currently investigating this issue, and it seems the problem is not with the amount of emails (or the total size of the emailbox), but rather there’s a bug/issue that fill in folders_rel table with junk.

The issue may be related to this one:

github.com/salesagility/SuiteCRM

Fix Issue #6994: Update pollMonitoredInboxesAOP to double check that …

salesagility:hotfix-7.10.x ← MikeyJC:fix/i6994

opened 07:38AM - 06 Aug 21 UTC

MikeyJC

+7 -1

…SugarFolder has been retrieved correctly. ## Description Fixing… issue #6994 by updating the pollMonitoredInboxesAOP scheduled task (lines 605 - 611) to make sure it gets the SugarFolder correctly. First attempts using the InboundEmail->groupfolder_id value as normal, if that fails, then attempts using InboundEmail->id instead. If that fails as well, then the $isGroupFolderExists value is not set to true. ## Motivation and Context There is a bug where if the value in the groupfolder_id does not match with a SugarFolder id, then the logs will end up being filled with the message ```[FATAL] *** FOLDERS: addBean() is trying to save to a non-saved or non-existent folder```. ## How To Test This Make sure that the task pollMonitoredInboxesAOP is active. Set up an Inbound Email account. Once running, check the logs for the above error message. ## Types of changes - [x] Bug fix (non-breaking change which fixes an issue) - [ ] New feature (non-breaking change which adds functionality) - [ ] Breaking change (fix or feature that would cause existing functionality to change) ### Final checklist - [x] My code follows the code style of this project found [here](https://docs.suitecrm.com/community/contributing-code/coding-standards/). - [ ] My change requires a change to the documentation. - [x] I have read the [**How to Contribute**](https://docs.suitecrm.com/community/contributing-code/) guidelines.

since my log file used to fill up with the mentioned error messages.

I am not sure about the purpose of this folders_rel table, but as far as I understand it should not be nearly as large as it is. I think its purpose is to maintain a relationship (a hierarchy) of emailbox folders, so it shouldn’t ever change except for when new inbound email accounts are added to SuiteCRM and/or those accounts get new folders/labels.

It appears that SuiteCRM fails to detect the right folder and attempts to re-create it every time it saves a new email.

egopak · 10 June 2022 14:14

Forgot to mention: folders_rel table contains over 125M records!

Also, it may be that the issue occurred after I applied a “hotfix” from this commit:

github.com/salesagility/SuiteCRM

Fix Issue #6994: Update pollMonitoredInboxesAOP to double check that …

salesagility:hotfix-7.10.x ← MikeyJC:fix/i6994

opened 07:38AM - 06 Aug 21 UTC

MikeyJC

+7 -1

…SugarFolder has been retrieved correctly. ## Description Fixing… issue #6994 by updating the pollMonitoredInboxesAOP scheduled task (lines 605 - 611) to make sure it gets the SugarFolder correctly. First attempts using the InboundEmail->groupfolder_id value as normal, if that fails, then attempts using InboundEmail->id instead. If that fails as well, then the $isGroupFolderExists value is not set to true. ## Motivation and Context There is a bug where if the value in the groupfolder_id does not match with a SugarFolder id, then the logs will end up being filled with the message ```[FATAL] *** FOLDERS: addBean() is trying to save to a non-saved or non-existent folder```. ## How To Test This Make sure that the task pollMonitoredInboxesAOP is active. Set up an Inbound Email account. Once running, check the logs for the above error message. ## Types of changes - [x] Bug fix (non-breaking change which fixes an issue) - [ ] New feature (non-breaking change which adds functionality) - [ ] Breaking change (fix or feature that would cause existing functionality to change) ### Final checklist - [x] My code follows the code style of this project found [here](https://docs.suitecrm.com/community/contributing-code/coding-standards/). - [ ] My change requires a change to the documentation. - [x] I have read the [**How to Contribute**](https://docs.suitecrm.com/community/contributing-code/) guidelines.

Either I applied it incorrectly, or the hotfix doesn’t work properly. Will continue investigating.

pgr · 10 June 2022 16:04

Maybe the code is fixed now, and has been for a while, but it had previously filled up quite a lot, due to the old bug. Maybe now it just went over a certain threshold because of new emails and became noticeable.

If you try an aggregate query grouping the entries in that row by month or year of creation, you might get insights as the when this was most problematic.

I would also look into orphaned rows on that table. By “orphaned” I mean rows that reference items that have been deleted are no longer present in SuiteCRM. These should be the ones you can safely clear.

So you would need a SELECT joining folders_rel with Emails table, something like this (untested!)

SELECT id 
FROM folders_rel 
JOIN emails ON emails.id = folders_rel.polymorphic_id AND folders_rel.polymorphic_module = 'Emails'
WHERE emails.deleted = 1;

Then when you’re confident you have what you need (and full BACKUPS!) you can consider turning that into a DELETE…

egopak · 10 June 2022 20:08

After some more digging, I found that the same problem happened to others as well.

Looks like my issue is related to #9192:

github.com/salesagility/SuiteCRM

Email - folders_rel fills with duplicate entries

opened 02:13PM - 15 Jun 21 UTC

closed 02:33PM - 28 Jan 22 UTC

tsmgeek

Type:Bug Status:Fix Proposed Priority:Important Area: Emails

https://community.suitecrm.com/t/database-table-folders-rel-runs-full-for-each-e…mail-inbound-check/73490/13 Ive just come across the same issue as indicated in forum post above. My folders_rel table currently has 99million rows+ and filling each and every run checking the inbound folder. As always this comes back to a terribly broken email module. SuiteCRM: 7.10.29

It seems to have been merged into hotfix branch in Nov 2021, so I wouldn’t face this issue if I had updated the system earlier

I’m going to remove the duplicated entries from folders_rel and will attempt to upgrade my SuiteCRM to the latest 7.11.x.

egopak · 11 June 2022 03:41

Alright, applying the changes from the commits of the hotfix above solved the issue.

So, to summarize, a solution to my issue was the following:

Apply the fix from Fix Issue #6994: Update pollMonitoredInboxesAOP to double check that … by MikeyJC · Pull Request #9246 · salesagility/SuiteCRM · GitHub
This makes sure the issue will not re-appear after removing the duplicate records.
Delete the duplicate records from folders_rel. I only kept 3 actual records, corresponding to my inbound_email mailboxes and deleted the rest.
[Optional] Run OPTIMIZE TABLE inbound_email; to free up the disk space

Now SuiteCRM is working again, and looks like no data has been lost.