Database table folders_rel runs full for each email inbound check

b4f82 · 20 June 2020 20:09

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

pgr · 21 June 2020 10:42

Hi, welcome to the community

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.

lacma · 7 May 2021 16:19

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

pgr · 7 May 2021 16:23

@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?

lacma · 7 May 2021 16:32

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

pgr · 7 May 2021 17:25

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.

lacma · 7 May 2021 18:04

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
//

pgr · 7 May 2021 19:10

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.

lacma · 10 May 2021 06:55

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

pgr · 10 May 2021 11:44

Have a look at this

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

lacma · 10 May 2021 14:16

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

pgr · 10 May 2021 14:41

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!

lacma · 2 June 2021 10:03

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.

mb2 · 16 June 2021 08:51

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

mb2 · 16 June 2021 09:01

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

rbranco82 · 16 June 2021 17:20

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