SuiteCRM的folder_rel表体积激增（17小时增2GB）求助

Hey there, let's dig into why your folder_rel table is ballooning so quickly—17 hours of 2GB growth is definitely a red flag, especially since you already trimmed some inbound email accounts. Looking at your sample data, the key clue is all those duplicate entries linking the same folder_id to the same InboundEmail record. Here are the most likely causes and fixes:

Likely Causes #

• Duplicate Association Bug in Inbound Email Sync
  The biggest culprit here is that SuiteCRM’s inbound email sync logic isn’t checking for existing folder associations before inserting new ones. Every time the sync runs, it’s creating a new folder_rel entry for the same email folder and inbound email account instead of reusing the existing link. This happens if the sync code lacks a check for folder_id + polymorphic_module + polymorphic_id uniqueness before inserting.

• Overly Frequent Sync Scheduling
  If your inbound email sync is set to run too often (e.g., every minute), even a small logic flaw will generate thousands of duplicate records in hours. The more frequent the sync, the faster the table bloats.

• Uncleaned Soft-Deleted Records (Secondary)
  While your sample shows deleted=0, if old soft-deleted (deleted=1) records aren’t being purged regularly, they can contribute to table bloat over time. However, your rapid growth points more to active duplicate inserts than old garbage data.

Recommended Fixes #

1. Clean Up Existing Duplicate Records
   First, shrink the table by removing redundant entries. Run this SQL query (back up the table first!) to keep only one unique association per folder_id/polymorphic_module/polymorphic_id combo:

   DELETE f1 FROM folders_rel f1
   INNER JOIN folders_rel f2
   ON f1.folder_id = f2.folder_id
   AND f1.polymorphic_module = f2.polymorphic_module
   AND f1.polymorphic_id = f2.polymorphic_id
   AND f1.id > f2.id; -- Keep the oldest entry, adjust if you prefer the newest
   

   After deleting, optimize the table to reclaim space:

   OPTIMIZE TABLE folders_rel;
   

2. Fix the Sync Logic to Prevent Future Duplicates
   Navigate to modules/InboundEmail/InboundEmail.php (or the relevant sync handler in your SuiteCRM version) and modify the code that inserts into folder_rel. Add a check to see if an association already exists before inserting:

   // Before inserting a new folder_rel entry
   $existing = $GLOBALS['db']->fetchOne("
       SELECT id FROM folders_rel
       WHERE folder_id = '{$folder_id}'
       AND polymorphic_module = 'InboundEmail'
       AND polymorphic_id = '{$email_id}'
       AND deleted = 0
   ");
   if (!$existing) {
       // Proceed with inserting the new association
   }
   

   This ensures only one active association exists for each folder-email pair.

3. Adjust Sync Frequency
   Go to Admin > Scheduler and find the inbound email sync tasks. Increase the interval (e.g., from 1 minute to 15-30 minutes) to reduce the number of times the faulty sync logic runs.

4. Verify No Residual Sync Tasks
   Even if you deleted some inbound email accounts, their sync tasks might still be active. Double-check the scheduler for any orphaned tasks linked to deleted accounts and disable/delete them.

5. Enable Garbage Collection
   Make sure the Delete Records scheduler task is enabled and running regularly. This task purges old deleted=1 records across all tables, preventing long-term bloat.

内容的提问来源于stack exchange，提问作者Nelson Rodrigues