SuiteCRM reminders table too big

bf.ts90 · 22 February 2021 08:22

Goodmorning,
in our installation, I noticed that reminders table has around 1081833 records and, for a specific event, there are 36408 reminder records.
My question is, why? I saw that around every 15/20 minutes, there are 60 new records.
What does it contains?
How can I resolve?
For some time now, we have noticed that our suitecrm responds with 20/30 seconds of waiting every time a page is accessed. once the reboot is done, the situation returns to respond well, as long as it does not degrade again.
we think that reminders are the cause of the degradation

Hope that someone can help me,
B.

pgr · 22 February 2021 10:02

There was an old bug like that, which version are you running, is it recent?

bf.ts90 · 22 February 2021 10:06

Our suitecrm version is 7.11.2

pgr · 22 February 2021 11:09

I think it should be fixed by that version. Maybe you simply have the older accumulated data. Have you looked at your database to see if it’s mostly old data or not?

Maybe you can simply clear out most of it, and check if the Prune scheduler jobs are running so it won’t fill up again?

bf.ts90 · 22 February 2021 11:33

How can I decide which remiders remove?
I export reminders defined for 40 meetings, and there are 1.031.496 reminders, and only 212.323 have the flag deleted = true. So If i remove the deleted reminders, I already have around 800.000 reminders, for only 40 meetings. I don’t know why, the system continue to create reminders for the same meetings.
I have the same problem with reminders_invitee

B

pgr · 22 February 2021 12:35

I think an upgrade would probably solve this for you, there were so many fixes since 7.11.2, like this one (it’s just one example)

About deleting more reminders, I am not sure. Maybe have a look at the dates, or other fields that might make sense to you (like whether an email was sent or not).

There are also SQL queries to track orphaned records, meaning (in this case), reminders records that belong to calls or meetings which no longer exist in SuiteCRM.

bf.ts90 · 22 February 2021 13:03

thank you so much.
At the moment, I’m not able to upgrade the CRM cause there are a lot of customization and before, I must try upgrade in a dev instance, and check if all continue to work and than plan to make the upgrade in production. I can’t plan an upgrade today for tomorrow
So, I think that the problem, is that the reminders were created without control (maybe from some jobs?)
I would like to stop this creations when it isn’t necessary. Can you help me to find the points where SuiteCRM creates reminders?

pgr · 22 February 2021 13:06

You’re running that installation since which version of SuiteCRM?

You should first establish if your excessive number of reminders is old (from the time before some bug was fixed), and so you might not have too much of a problem now, after some clean-ups.

It’s different if you realize that you’re still getting too many added recently.

bf.ts90 · 22 February 2021 21:18

I installed SuiteCRM directly with 7.11.2 version on February 2019 (2 years ago).
between Feb. 2019 and Dec.2019 there are only 725 reminders
between Dec2019 and May2020 there are 507 reminders
than, from July 2020, something change: in one month, created 9.675 reminders (only 1302 deleted)

I’m analizing the meetings related to reminders, and I found that most proliferated reminders are related to mettings with repetition; some of the meetings have deleted flag = 1 but nonetheless reminders are continuously created.

pgr · 23 February 2021 10:52

That is really strange.
Can you try isolating the moment when the reminders get created?

Is it when the meeting is created? Or edited? Or deleted? Or some other moment?

another thing, do you have any Workflows or logic hooks working on those modules?

bf.ts90 · 23 February 2021 12:53

I have only one worfklow, for meetings module, easy easy: on create new records, and only when record is saved, there are 2 conditions on a custom fields (dropdown field) and, if the record has a particular value, send a mail to a user.

about the other your questions:
I isolated one metting and I saw that, around every 10 minutes, the record was updated from user = 1, and then were created 2 records on reminder.
I don’t know why, the metting record, was updated.
I didn’t tell you, that in our system there is googlecalendar syncronization, and it is scheduled every 5 minutes.

pgr · 23 February 2021 13:30

Maybe you can try turning off that scheduler (or any other that runs every 10 minutes…) for an hour or so and check if that reminder creation stops. This would help find where the problem is coming from.

bf.ts90 · 23 February 2021 14:00

Ok, thank you, now I try it!
An information abount prune database.
At the moment this job is turn off.
I would like know what the job make…
If I delete the meetings that create reminders without sense, and than execute prune, does the job remove also reminders (and reminder_invitees) related?
In other case, If I would delete all reminders (and reminders_invitees) related to a deleted meetings, I only set deleted = 1 in reminders and reminders_invitees, and then prune database?

pgr · 23 February 2021 14:16

I don’t know exactly what it does, the best thing to do is to follow this function wherever it leads you…

github.com

salesagility/SuiteCRM/blob/master/modules/Schedulers/_AddJobsHere.php#L297


        require('modules/ACL/ACLController.php');
    }
    require('modules/EmailMan/EmailManDelivery.php');
    return true;
}
/**
 *  Job 3
 */
function pruneDatabase()
{
    $GLOBALS['log']->info('----->Scheduler fired job of type pruneDatabase()');
    $backupDir = sugar_cached('backups');
    $backupFile = 'backup-pruneDatabase-GMT0_' . gmdate('Y_m_d-H_i_s', strtotime('now')) . '.php';
    $db = DBManagerFactory::getInstance();
    $tables = $db->getTablesArray();
    $queryString = array();
    if (!empty($tables)) {

It does a few clean-ups, but also leaves a lot of things out that should have some maintenance and don’t. It’s not complete, you may find it doesn’t even touch meetings and reminders, I don’t know.

bf.ts90 · 23 February 2021 15:46

thank you for your answer. I looked the code of prune job and I saw that take all tables in the database and cycle to them, and remove record with deleted = 1 and work also in _cstm tables to remove records with empty id_c field.

So, to remove from table the reminders, I must set with deleted = 1 before.

Now, in your opinion, can I made a query directly to reminders table, that set the deleted flag = 1 ?

pgr · 23 February 2021 16:14

For every record in SuiteCRM, when you delete it from the UI, what happens in the database is just that: deleted flag set to true.

I am not sure how reminders are deleted in the UI, but basically if you do the same directly on the database it has the same effect. Just make sure you don’t need those reminders any more!.. and create a backup first, just in case.

bf.ts90 · 23 February 2021 16:31

Yes, perfect.
I would set reminders deleted flag = 1 where the related_event_module_id (meeting related) is deleted.
So. I’ ll delete the meetings that continues to generate reminders; remove reminders associated, and then run prune so, the job removes from database all events deleted, all reminders deleted.
There isn’t UI point on SuiteCRM that shows the Reminders and Reminders Invitees (I think just in relationship related to meeting)

bf.ts90 · 26 February 2021 17:54

@pgr goodmorning,
In our instance I executed this operations:

delete the repetion meetings that cause the problem (generate reminders every time)
set deleted = 1 for: all reminders, reminders_invitees, meetings_users related with meetings with deleted flag = 1
execute prune scheduler, only for: meetings , meetings_cstm, reminders, reminders_invitees, meetings_users

Now, the situation is under control: no reminders created without sense, the tables (reminders and reminders_invitees) have around 2000/3000 records, the system, run fast and response are very quickly.

The last think that I don’t know, is why repetition events with reminders works so badly.

thank you for your time and help!
B.

pgr · 26 February 2021 18:17

Good work!

If you can open an issue on GitHub, with a simplified use-case that needs to be fixed, it would help. Maybe something like this:

create a meeting with recurrence
add reminders to it
what you would expect to be the logical treatment of the reminders in each meeting occurrence
what actually happens

BTW, did you see the private message I sent you a while ago?

Thanks

bf.ts90 · 26 February 2021 18:48

I’m not sure that all meetings with recurrence, have this problem. And I don’t know if google calendar sync may have contributed.
Maybe next days, I try to create a meeting with recurrence and analyze what’s happen…

B.