Case note relationship problems after upgrade from 7.10.22. to 7.11.20

Hi,
we noticed a slow down after upgrading from 7.10.22 to 7.11.20. The whole upgrade process took around 1 hour to complete. We tracked SQL sessions and had noticed that it repeatedly makes queries like:

SELECT id FROM notes WHERE notes.parent_id = '' AND notes.deleted=0 AND notes.parent_type = 'Cases'

As our database has lot of notes related to cases (parent_type = ‘Cases’) without parents (parent_id is null or empty string) it queries each of them with queries like following:

SELECT notes.*,notes_cstm.* FROM notes LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c WHERE notes.id = '1299a1c0-e303-1a4b-61d6-60a6cdea1efd' AND notes.deleted=0 LIMIT 0,1

We have the same problem after the upgrade. We can see lot of logs querying Cases notes without parent and this is slowing down our suiteCRM instance.

This is the excerpt from the log:

[INFO] Query:SELECT id FROM notes WHERE notes.parent_id = '' AND notes.deleted=0 AND notes.parent_type = 'Cases'

Execution Time:0.00024914741516113

Hook called: Notes::before_retrieve

Retrieve Note : SELECT notes.*,notes_cstm.* FROM notes  LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c  WHERE notes.id = '1299a1c0-e303-1a4b-61d6-60a6cdea1efd' AND notes.deleted=0

Limit Query:SELECT notes.*,notes_cstm.* FROM notes  LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c  WHERE notes.id = '1299a1c0-e303-1a4b-61d6-60a6cdea1efd' AND notes.deleted=0 Start: 0 count: 1

… and the same query for each note related to Cases without parent (Case was deleted, but related note wasn’t deleted and the parentId is being cleared after deleting the Case record)

Note related queries are performed each time for each note without it’s case parent. After deleting all notes with parent_type = 'Cases' without parent_id it started working much faster, but unfortunately we are not allowed to manage and clear the database records.

We’ve tried to revert the CRM version back to 7.10.22 and it works fine with the same database instance.

It looks like the problem is related to entering the following part of code:

/data/Relationships/One2MBeanRelationship.php

if (!isset($link->getFocus()->$lhsKey)) {
            LoggerManager::getLogger()->warn('One2MBeanRelationship getQuery: Trying to get property of non-object');
            $linkFocusLhsKey = null;
} else {
            $linkFocusLhsKey = $link->getFocus()->$lhsKey;
}

The similar behaviour is reported in following topic, but there was no clear answer:

I can’t get around why is this being called after upgrade and why are we querying Cases notes on almost each action (studio module browsing, opening modules with cases, etc.). Do you have any clue regarding this?

It seems you have a before_retrieve hook, can you check what it contains?

I was checking this, but there are no custom hooks at all. As far as I understand, the before_retrieve part of the log is related to checking available hooks in include/utils/LogicHook.php, but there is no before_tetrieve hooks being called at all, although the logs indicate this.

Where exactly did you check about the hooks?

I can’t find those queries in the code, it really looks like you have a hook. Check custom/modules/Ext/

Hi,
I’ve just checked following:

  1. custom/modules/Cases - there is a logic_hooks.php defining several hooks, but this is note a before_Retrieve hook from the log, I’ve commented out all hooks from this file, made a quick repair and rebuild but the same thing happens

  2. custom/modules/Notes - no logic_hooks.php defined, custom/modules/Notes/Ext includes only Language and Vardefs subfolders

  3. custom/modules - the is a logic_hooks.php in custom/modules folder, defining several hooks, however not related to Notes and not any before_retrieve as captured in logs

The place were I was following the mentioned logs and hook checks is include/utils/LogicHook.php .
In order to get more detailed log, I’ve used the code from:

However, this should not influence the logic of the call_custom_logic function.

As far as I can see, before the problematic queries are executed, following is being called in include/utils/LogicHook.php.

a) call_custom_logic with $module_dir=Notes and $event=before_retrieve
b) the function is checking and trying to get hooks from $module_dir folder and is getting the empty array (no custom hooks defined in Notes module)
c) after previous point, it is calling getHooks(’’), getting all hooks from custom/modules/logic_hooks.php (previously mentioned in section 3 of my comment)
d) Previous getHooks(’’) retreives array of hooks and it is calling process_hooks function after that, passing $event=before_retrieve and hooks acquired with getHooks. However, no hooks are related to before_retrieve event so the process_hooks is being skipped because of the following condition:
if (!empty($hook_array[$event])) {

I am not sure about the place where SELECT notes.*,notes_cstm.* FROM notes LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c WHERE notes.id = '1299a1c0-e303-1a4b-61d6-60a6cdea1efd' AND notes.deleted=0 are starting to being called, but calls are repeated for each note with parent_type = ‘Cases’ with no parent id.

To doble check everything I’ve commented out all logic_hooks.php files (the one from custom/modules, and the one from custom/modules/Cases), and the same thing happens, I still have following logs:

One2MBeanRelationship getQuery: Trying to get property of non-object

Query: SELECT id FROM notes WHERE notes.parent_id = ‘’ AND notes.deleted=0 AND notes.parent_type = ‘Cases’

Hooks check for: Notes::before_retrieve
Hook called: Notes::before_retrieve, hooks not empty 2 (this is the custom log message I’ve added based on the mentioned community topic)

SELECT notes.,notes_cstm.* FROM notes LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c WHERE notes.id = '7eaa3f25-ab2c-7b10-b595-60a6caed4*
5df’ AND notes.deleted=0

… and the same select for each returned record (WHERE notes.parent_id = ‘’ AND notes.deleted=0 AND notes.parent_type = ‘Cases’)

Try logging a print_r of the $hooks array right after this message.

Or perhaps run a “find in files” for all instances of before_retrieve string in your custom directory…

There is no before_retrieve at all in custom folder and it’s subfolder.

These are places having before_retrieve when searching through all crm folders and subfolders:

`./include/utils/LogicHook.php:51: * before_retrieve`
./data/SugarBean.php:4516:        $this->call_custom_logic('before_retrieve', $custom_logic_arguments);

I’ve already put logging of $hooks array, it consists of hooks being defined at custom/module/logic_hooks.php level. Several after_save, after_delete, after_restore hooks. But there is no before_retrieve hook at all.

That is confusing for me. Something about that logging sounds misleading, but ok, let’s accept that there is no before_retrieve hook… :man_shrugging:

I’m not sure how to further troubleshoot this.

If you have a log message coming specifically from where the SQL is called, you can try generating a stack trace there to find out where it is being called from.

But if you don’t have that message, I don’t know. You’d have to find the source code creating the query, which sometimes is not straight-forward because the query is not directly present in the source code, it is being built part by part…