Database table folders_rel runs full for each email inbound check

💬 General Discussion
1

Hi all,

I have a schedular job for checking inbound emails of one imap postbox. There is no SPAM and at the moment 273 emails inside. Everything works fine.

My problem: With each run he creates several hundred entries in the database.

Like this:
00000021-2a58-a0a0-6dc5-5ea233b9550c InboundEmail c3c76c24-3fd7-0e89-153a-5cad0627889d

My table is now at 16GBytes… What can I do??
Clean ing the table will destroy my 273 emails in the system - or not?

My SuiteCRM version is: 7.11.6

Best regards,
erik

2

Hi, welcome to the community :tada:

Can you explain a bit better which table gets the new records? What do the new rows look like?

Can you find a logic in the “several hundred” new rows created each time? Could it be the number of IMAP folders you have multiplied by the number of emails, or something like that?

I think simply upgrading to the latest would likely solve this problem, you should consider making a full back and upgrading.

3

Hi,
Version 7.11.18, WinServer, Apache 2.4, mariaDB 10.2

This problem is happen to me too.
At the Moment I have 28 mio Rows in the ‘folders_rel’ table.
But just 5340 Emails in the Inbound folder.

the table takes more than 19 GiB.
when I check:
//
select a.folder_id, a.polymorphic_id, a.polymorphic_module,count()
from folders_rel a
group by a.folder_id, a.polymorphic_id, a.polymorphic_module
/*/
all records are equal. (28 mio equal rows - a.folder_id, a.polymorphic_id, a.polymorphic_module)

There is looping.
Can you please advise me to fix this problem.

Thank you

4

@b4f82 did you ever solve your problem?

@lacma what is your SuiteCRM version? Can you check the timestamps on those rows and see if your SuiteCRM is still generating more, or if it generated all of those rows at one time and then stopped?

5

Hello pgr
thanks for the quick reply.

I have just written the system versions at the top of the message.

It is still producing records.

in 20 minutes the number grew from 27’858’732 to 27’861’818.

addition:
i stopped the scheduler and then it stopped with new records

6

I suggest that you turn on log slow queries in Admin / System settings. I am sure those INSERT queries will be quite slow because the table is now huge. This should let you find out which query is inserting the rows, and then you can try and look at the code to see why it’s doing that.

7

I get something like that:
//
Fri May 7 18:32:21 2021 [9276][1][DEBUG] InboundEmail processing 1 email 20-----------------------------------------------------------------------------------------
Fri May 7 18:32:21 2021 [9276][1][DEBUG] *********** InboundEmail doing dupe check.
Fri May 7 18:32:21 2021 [9276][1][INFO] Query:SELECT count(emails.id) AS c FROM emails WHERE emails.message_id = ‘6b4dcdebb30cc868fa09e7baa463fb05’ and emails.deleted = 0
Fri May 7 18:32:21 2021 [9276][1][INFO] Query Execution Time:0.00049304962158203
Fri May 7 18:32:21 2021 [9276][1][DEBUG] InboundEmail found a duplicate email with ID (6b4dcdebb30cc868fa09e7baa463fb05)
Fri May 7 18:32:21 2021 [9276][1][INFO] InboundEmail found a duplicate email: VI1PR06MB47996CC0255CE9C8E9B3B4E9899F0@VI1PR06MB4801.eurprd06.prod.outlook.com
Fri May 7 18:32:21 2021 [9276][1][INFO] Query:SELECT id from emails WHERE message_id ="6b4dcdebb30cc868fa09e7baa463fb05"AND mailbox_id = “46754b8d-8526-494e-7151-5f7244fb3569”
Fri May 7 18:32:21 2021 [9276][1][INFO] Query Execution Time:0.00021696090698242
Fri May 7 18:32:21 2021 [9276][1][INFO] Query:INSERT INTO folders_rel (id, folder_id, polymorphic_module, polymorphic_id, deleted) VALUES (‘d118fc66-14bb-8271-7d92-60956bfcb874’, ‘46754b8d-8526-494e-7151-5f7244fb3569’, ‘InboundEmail’, ‘46754b8d-8526-494e-7151-5f7244fb3569’, 0)
Fri May 7 18:32:21 2021 [9276][1][INFO] Query Execution Time:0.0036449432373047
//

8

It’s hard for me to make anything of that, try exploring those Ids in the database, see how they match with your data, which one is the one most repeated, etc.

Are you a developer? Another thing you can try is looking for some of those messages inside the code, to see if the logic around the message gives you some more clues.

9

Hi pgr
all records are the same except for the id.
can I delete all the 8 mio Rows except one?

how does this table interact with other table keys?

I need to reduce the data volume.

thanks

10

Have a look at this

and visit that first link, it will take you to a separate site with a full database schema.

11

according to this scheme, the folder_rel table stands alone.
what is the function/meaning of this table?

12

I am completely just guessing here, but I’d say…

Field Type Len Meaning
id CHAR 36 the id of this folders_rel record
folder_id CHAR 36 the id of a folder’s record in folders table
polymorphic_module VARCHAR 25 √ the name of a module’s table
polymorphic_id VARCHAR 36 √ the id of a record in the table mentioned in polymorphic_module
deleted BIT 1 √ set to 1 if this folders_rel record is deleted

Just maybe!

13

Hi pgr
maybe I found the origin for the inserts into folders_rel
the log file show ‘folder not found’

//
Wed Jun 2 09:48:06 2021 [16736][1][DEBUG] ----->SchedulersJob calling function: pollMonitoredInboxesAOP
Wed Jun 2 09:48:06 2021 [16736][1][INFO] ----->Scheduler fired job of type pollMonitoredInboxesAOP()
Wed Jun 2 09:48:06 2021 [16736][1][DEBUG] Using system default ImapHandler. Hint: Use any ImapHandlerInterface as dependency of InboundEmail
Wed Jun 2 09:48:06 2021 [16736][1][DEBUG] ImapHandlerFactory will using a ImapHandler
Wed Jun 2 09:48:06 2021 [16736][1][INFO] Query:SELECT id, name FROM inbound_email WHERE is_personal = 0 AND deleted=0 AND status=‘Active’ AND mailbox_type != ‘bounce’
Wed Jun 2 09:48:06 2021 [16736][1][INFO] Query Execution Time:0.00046205520629883
Wed Jun 2 09:48:06 2021 [16736][1][DEBUG] Just got Result from get all Inbounds of Inbound Emails
Wed Jun 2 09:48:06 2021 [16736][1][DEBUG] In while loop of Inbound Emails
Wed Jun 2 09:48:06 2021 [16736][1][DEBUG] Using system default ImapHandler. Hint: Use any ImapHandlerInterface as dependency of InboundEmail
Wed Jun 2 09:48:06 2021 [16736][1][DEBUG] ImapHandlerFactory will using a ImapHandler
Wed Jun 2 09:48:06 2021 [16736][1][DEBUG] Hook called: InboundEmail::before_retrieve
Wed Jun 2 09:48:06 2021 [16736][1][DEBUG] Retrieve InboundEmail : SELECT inbound_email. FROM inbound_email WHERE inbound_email.id = ‘46754b8d-8526-494e-7151-5f7244fb3569’ AND inbound_email.deleted=0
Wed Jun 2 09:48:06 2021 [16736][1][DEBUG] Limit Query:SELECT inbound_email. FROM inbound_email WHERE inbound_email.id = ‘46754b8d-8526-494e-7151-5f7244fb3569’ AND inbound_email.deleted=0 Start: 0 count: 1
Wed Jun 2 09:48:06 2021 [16736][1][INFO] Query:SELECT inbound_email. FROM inbound_email WHERE inbound_email.id = ‘46754b8d-8526-494e-7151-5f7244fb3569’ AND inbound_email.deleted=0 LIMIT 0,1
Wed Jun 2 09:48:06 2021 [16736][1][INFO] Query Execution Time:0.00024199485778809
Wed Jun 2 09:48:06 2021 [16736][1][DEBUG] Hook called: InboundEmail::after_retrieve
Wed Jun 2 09:48:06 2021 [16736][1][WARN] Request ssl value not found.
Wed Jun 2 09:48:06 2021 [16736][1][WARN] Requested folder is not defined
Wed Jun 2 09:48:06 2021 [16736][1][ERROR] ImapHandler trying to use a non valid resource stream.
Wed Jun 2 09:48:06 2021 [16736][1][ERROR] ImapHandler trying to use a non valid resource stream.
Wed Jun 2 09:48:07 2021 [16736][1][INFO] Connected to mail server id: 46754b8d-8526-494e-7151-5f7244fb3569
Wed Jun 2 09:48:07 2021 [16736][1][DEBUG] Trying to connect to mailserver for [ anyemail@account.com ]
Wed Jun 2 09:48:07 2021 [16736][1][DEBUG] Connected to mailserver
Wed Jun 2 09:48:07 2021 [16736][1][DEBUG] -----> getNewMessageIds() got 5632 new Messages
/***/

“Wed Jun 2 09:48:06 2021 [16736][1][WARN] Requested folder is not defined”
folder not found…?
do I have a setup problem?
why it does not find the correct folder?

can you advise me to get to the right code position.

14

Having this problem too.

as code search shows “Requested folder…” is in the InboundEmail.php file.

make the warning more helpfull by printing the folder it is trying to request.

in my case that is a blank folder name.

same for the ssl value.

cheers Martin

15

yes, the pointing to the schema is rather useless as suitecrm doesn’t use referential integrity.
Al the intended use has to be derived from the code.

use google to search for : suitecrm folders_rel github
and find in files found

$query = "SELECT count(*) c FROM folders_rel JOIN emails ON emails.id = folders_rel.polymorphic_id" .
                " WHERE folder_id = " . $this->db->quoted($folderId) . " AND folders_rel.deleted = 0 AND emails.deleted = 0";

kr Martin

1 Like
16

Check this ticket out, problem is due to another method that should return false if it did not import duplicated emails.

1 Like