Contacts Module History SubPanel SQL Query error on WHERE Clause

I’m Running SuiteCRM 7.11.10.

Sometime in the last week a change may have been made that has provoked this problem.

When using the detail view of a Contact, the History SubPanel is using a syntactically incorrect Query. This was causing the Module to break until I discovered a fix 7737 which traps the error and allows the Module to be showen but with nothing showing in the History SubPanel but for “Database failure. Please refer to suitecrm.log for details.” suitecrm.log shows a SQL error in “ListView::processUnionBeans” due to a query that has a broken WHERE clause in “…derivedemails on derivedemails.email_id = email.id where ( ) AND emails.deleted = 0…”. The problem being “where ( )”.

Can anyone give me a pointer as to where to start looking for what has failed to populate the brackets in the WHERE clause?

It’s been driving mad for the last few days!

Try upgrading SuiteCRM to the latest, this can be already fixed.

And make sure you didn’t upgrade your MySQL to an unsupported version (like 8.0)

Hi and thanks.
There hasn’t been a MySQL upgrade.
Could you tell me the bug/fix number so I can look at a manually application of the fix.
An upgrade is going to be a big job due to customisation.
Thanks, Richard

I don’t know the exact bug number. I just remember seeing similar things recently fixed.

I think if you search using the exact error text you should be able to find it. Good luck.

Sorry tried that and couldn’t find anything that’s why I was asking on the forum :slight_smile:

Did a lot more searching but nothing came up.
So I’ve gone for a pragmatic hack to SugarBean::get_union_related_list() before the the SQL is used by
SugarBean::process_union_list_query() to look for and replace the broken SQL;

 if (strpos($final_query, 'where ( ) AND emails.deleted=0')) {
            $final_query = str_replace('where ( ) AND emails.deleted=0', 'where emails.deleted=0', $final_query);
            $final_query_rows = str_replace('where ( ) AND emails.deleted=0', 'where emails.deleted=0', $final_query_rows);
}

If anyone knows if this is really fixed please let me know the bug number.
Thanks.

Can you please provide the complete and exact MySQL error? That’s the starting point for any investigation…

Thu Mar  4 16:51:40 2021 [2365][1][FATAL] Mysqli_query failed.
Thu Mar  4 16:51:40 2021 [2365][1][FATAL] Error running count query for Contact List:  Query Failed: ( SELECT count(*) FROM tasks  INNER JOIN  contacts tasks_rel ON tasks.contact_id=tasks_rel.id AND tasks_rel.deleted=0

 where ( tasks.contact_id='584703ea-7999-433e-f643-5c9bd2ad3574' AND (tasks.status='Completed' OR tasks.status='Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT count(*) FROM tasks  INNER JOIN  contacts tasks_parent_rel ON tasks.parent_id=tasks_parent_rel.id AND tasks_parent_rel.deleted=0
 AND tasks.parent_type = 'Contacts'
 where ( tasks.parent_id='584703ea-7999-433e-f643-5c9bd2ad3574' AND (tasks.status='Completed' OR tasks.status='Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT count(*) FROM meetings  LEFT JOIN meetings_cstm ON meetings.id = meetings_cstm.id_c  INNER JOIN  meetings_contacts ON meetings.id=meetings_contacts.meeting_id AND meetings_contacts.contact_id='584703ea-7999-433e-f643-5c9bd2ad3574' AND meetings_contacts.deleted=0

 where ((meetings.status='Held' OR meetings.status='Not Held')) AND meetings.deleted=0 ) UNION ALL ( SELECT count(*) FROM calls  LEFT JOIN calls_cstm ON calls.id = calls_cstm.id_c  INNER JOIN  calls_contacts ON calls.id=calls_contacts.call_id AND calls_contacts.contact_id='584703ea-7999-433e-f643-5c9bd2ad3574' AND calls_contacts.deleted=0

 where ((calls.status='Held' OR calls.status='Not Held')) AND calls.deleted=0 ) UNION ALL ( SELECT count(*) FROM notes  LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c  INNER JOIN  contacts notes_rel ON notes.contact_id=notes_rel.id AND notes_rel.deleted=0

 where ( notes.contact_id='584703ea-7999-433e-f643-5c9bd2ad3574') AND notes.deleted=0 ) UNION ALL ( SELECT count(*) FROM emails  INNER JOIN  emails_beans ON emails.id=emails_beans.email_id AND emails_beans.bean_id='584703ea-7999-433e-f643-5c9bd2ad3574' AND emails_beans.deleted=0
 AND emails_beans.bean_module = 'Contacts'
 where emails.deleted=0 ) UNION ALL ( SELECT count(*) FROM emails  JOIN (select DISTINCT email_id from emails_email_addr_rel eear

        join email_addr_bean_rel eabr on eabr.bean_id ='584703ea-7999-433e-f643-5c9bd2ad3574' and eabr.bean_module = 'Contacts' and
        eabr.email_address_id = eear.email_address_id and eabr.deleted=0
        where eear.deleted=0 and eear.email_id not in
        (select eb.email_id from emails_beans eb where eb.bean_module ='Contacts' and eb.bean_id = '584703ea-7999-433e-f643-5c9bd2ad3574')
        ) derivedemails on derivedemails.email_id = emails.id where ( ) AND emails.deleted=0 ): MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND emails.deleted=0 )' at line 19
Thu Mar  4 16:51:40 2021 [2365][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Thu Mar  4 16:51:40 2021 [2365][1][FATAL] [ListView::processUnionBeans] . #0 /var/www/html/include/database/DBManager.php(353): sugar_die('Database failur...')
#1 /var/www/html/include/database/DBManager.php(328): DBManager->registerError('Error running c...', 'Error running c...', true)
#2 /var/www/html/include/database/MysqliManager.php(179): DBManager->checkError('Error running c...', true)
#3 /var/www/html/data/SugarBean.php(1510): MysqliManager->query('( SELECT count(...', true, 'Error running c...')
#4 /var/www/html/data/SugarBean.php(1260): SugarBean->_get_num_rows_in_query('( SELECT count(...', true)
#5 /var/www/html/data/SugarBean.php(1023): SugarBean->process_union_list_query(Object(Contact), '(SELECT tasks.i...', 0, -1, 10, '', Object(aSubPanel), '( SELECT count(...', Array)
#6 /var/www/html/include/ListView/ListView.php(1156): SugarBean::get_union_related_list(Object(Contact), 'date_entered de...', 'desc', '', 0, -1, 10, '', Object(aSubPanel))
#7 /var/www/html/include/ListView/ListViewSubPanel.php(142): ListView->processUnionBeans(Object(Contact), Object(aSubPanel), 'history_CELL', false)
#8 /var/www/html/include/SubPanel/SubPanel.php(220): ListViewSubPanel->process_dynamic_listview('Contacts', Object(Contact), Object(aSubPanel), false)
#9 /var/www/html/include/SubPanel/SubPanelTiles.php(357): SubPanel->ProcessSubPanelListView('include/SubPane...', Array)
#10 /var/www/html/include/MVC/View/SugarView.php(1191): SubPanelTiles->display()
#11 /var/www/html/include/MVC/View/SugarView.php(255): SugarView->_displaySubPanels()
#12 /var/www/html/include/MVC/Controller/SugarController.php(435): SugarView->process()
#13 /var/www/html/include/MVC/Controller/SugarController.php(375): SugarController->processView()
#14 /var/www/html/include/MVC/SugarApplication.php(113): SugarController->execute()
#15 /var/www/html/index.php(52): SugarApplication->execute()
#16 {main}

I couldn’t find it either, maybe this is a new issue and does not have a fix.

If you can try it on a separate installation (live demo, for example), you’ll know if it’s a generic bug in SuiteCRM or just something gone wrong in your particular installation.

Pretty much convinced it’s our problem as its only been happening in the last 1 1/2 weeks. There have been a number of system changes in that time. No SuiteCRM, PHP or MySQL upgrades though, just SuiteCRM customisation changes. I’ve reviewed them all and none would have a direct effect on this issue unless a change was botched of course. The hack I put in SugarBean::get_union_related_list() has fixed it for us and our client. If I get any spare time I’ll investigate further but for now this fixes it for us.

1 Like