Suitecrm: Lead detail view fails when has scheduled call

[tech-ch1]

Issue

On leads having scheduled calls, detailed view is broken (ajax disabled).

Expected Behavior

The call should be displayed in the Activities SubPannel, detailed view shouldn't be broken.

Actual Behavior

On call add, prints a "database failure" message. On page refresh, all subpannels are missing and a "database failure" message is printed at the bottom of the page.

`Thu Nov 8 10:20:22 2018 [9215][1b0e19a9-67ac-8ce6-6ae3-5a69b21c1c5f][FATAL] Mysqli_query failed.
Thu Nov 8 10:20:22 2018 [9215][1b0e19a9-67ac-8ce6-6ae3-5a69b21c1c5f][FATAL] Error retrieving Lead list: Query Failed: (SELECT meetings.id , meetings.assigned_user_id , meetings.name , meetings.status , ' ' contact_name , ' ' contact_id , meetings.date_end as date_due , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' contact_name_owner , ' ' contact_name_mod , meetings.recurring_source , meetings.created_by , 'meetings' panel_name FROM meetings LEFT JOIN meetings_cstm ON meetings.id = meetings_cstm.id_c LEFT JOIN users jt1 ON meetings.assigned_user_id=jt1.id AND jt1.deleted=0

AND jt1.deleted=0 INNER JOIN meetings_leads ON meetings.id=meetings_leads.meeting_id AND meetings_leads.lead_id='7be1a585-8ca0-f09b-b915-5be3f7532b47' AND meetings_leads.deleted=0

where ((meetings.status !='Held' AND meetings.status !='Not Held')) AND meetings.deleted=0) UNION ALL ( SELECT meetings.id , meetings.assigned_user_id , meetings.name , meetings.status , ' ' contact_name , ' ' contact_id , meetings.date_end as date_due , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' contact_name_owner , ' ' contact_name_mod , meetings.recurring_source , meetings.created_by , 'oldmeetings' panel_name FROM meetings LEFT JOIN meetings_cstm ON meetings.id = meetings_cstm.id_c LEFT JOIN users jt1 ON meetings.assigned_user_id=jt1.id AND jt1.deleted=0

AND jt1.deleted=0 where ( meetings.parent_id = '7be1a585-8ca0-f09b-b915-5be3f7532b47'
AND meetings.parent_type = 'Leads' AND meetings.id NOT IN ( SELECT meeting_id FROM meetings_leads ) AND (meetings.status !='Held' AND meetings.status !='Not Held')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.assigned_user_id , tasks.name , tasks.status , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) contact_name , tasks.contact_id , NULL date_due, jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' contact_name_owner , ' ' contact_name_mod , NULL recurring_source, tasks.created_by , 'tasks' panel_name FROM tasks LEFT JOIN contacts contacts ON tasks.contact_id=contacts.id AND contacts.deleted=0

AND contacts.deleted=0 LEFT JOIN users jt1 ON tasks.assigned_user_id=jt1.id AND jt1.deleted=0

AND jt1.deleted=0 INNER JOIN leads tasks_rel ON tasks.parent_id=tasks_rel.id AND tasks_rel.deleted=0
AND tasks.parent_type = 'Leads'
where ( tasks.parent_id='7be1a585-8ca0-f09b-b915-5be3f7532b47' AND (tasks.status != 'Completed' AND tasks.status != 'Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.assigned_user_id , calls.name , calls.status , ' ' contact_name , ' ' contact_id , calls.date_end as date_due , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' contact_name_owner , ' ' contact_name_mod , calls.recurring_source , calls.created_by , 'calls' panel_name FROM calls LEFT JOIN calls_cstm ON calls.id = calls_cstm.id_c LEFT JOIN users jt1 ON calls.assigned_user_id=jt1.id AND jt1.deleted=0

AND jt1.deleted=0 INNER JOIN calls_leads ON calls.id=calls_leads.call_id AND calls_leads.lead_id='7be1a585-8ca0-f09b-b915-5be3f7532b47' AND calls_leads.deleted=0

where ((calls.status != 'Held' AND calls.status != 'Not Held')) AND calls.deleted=0 ) UNION ALL ( SELECT calls.id , calls.assigned_user_id , calls.name , calls.status , ' ' contact_name , ' ' contact_id , calls.date_end as date_due , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' contact_name_owner , ' ' contact_name_mod , calls.recurring_source , calls.created_by , 'oldcalls' panel_name FROM calls LEFT JOIN calls_cstm ON calls.id = calls_cstm.id_c LEFT JOIN users jt1 ON calls.assigned_user_id=jt1.id AND jt1.deleted=0

AND jt1.deleted=0 where ( calls.parent_id = '7be1a585-8ca0-f09b-b915-5be3f7532b47'
AND calls.parent_type = 'Leads' AND calls.id NOT IN ( SELECT call_id FROM calls_leads ) AND (calls.status != 'Held' AND calls.status != 'Not Held')) AND calls.deleted=0 ) ORDER BY date_start desc LIMIT 0,10: MySQL error 1054: Unknown column 'date_start' in 'order clause'
Thu Nov 8 10:20:22 2018 [9215][1b0e19a9-67ac-8ce6-6ae3-5a69b21c1c5f][FATAL] Exception handling in /srv/ch1/suitecrm/include/MVC/Controller/SugarController.php:400
Thu Nov 8 10:20:22 2018 [9215][1b0e19a9-67ac-8ce6-6ae3-5a69b21c1c5f][FATAL] Exception in Controller: Défaillance de la base de données. Veuillez consulter suitecrm.log pour plus de détails.
Thu Nov 8 10:20:22 2018 [9215][1b0e19a9-67ac-8ce6-6ae3-5a69b21c1c5f][FATAL] backtrace:

0 /srv/ch1/suitecrm/include/database/DBManager.php(353): sugar_die('D\xC3\xA9faillance de...')

1 /srv/ch1/suitecrm/include/database/DBManager.php(328): DBManager->registerError('Error retrievin...', 'Error retrievin...', true)

2 /srv/ch1/suitecrm/include/database/MysqliManager.php(179): DBManager->checkError('Error retrievin...', true)

3 /srv/ch1/suitecrm/include/database/MysqlManager.php(285): MysqliManager->query('(SELECT meeting...', true, 'Error retrievin...')

4 /srv/ch1/suitecrm/data/SugarBean.php(1284): MysqlManager->limitQuery('(SELECT meeting...', 0, 10, true, 'Error retrievin...')

5 /srv/ch1/suitecrm/data/SugarBean.php(1016): SugarBean->process_union_list_query(Object(Lead), '(SELECT meeting...', 0, 10, 10, '', Object(aSubPanel), '( SELECT count(...', Array)

6 /srv/ch1/suitecrm/include/ListView/ListView.php(1141): SugarBean::get_union_related_list(Object(Lead), 'date_start desc', 'desc', '', 0, -1, 10, '', Object(aSubPanel))

7 /srv/ch1/suitecrm/include/ListView/ListViewSubPanel.php(142): ListView->processUnionBeans(Object(Lead), Object(aSubPanel), 'activities_CELL', false)

8 /srv/ch1/suitecrm/include/SubPanel/SubPanel.php(220): ListViewSubPanel->process_dynamic_listview('Leads', Object(Lead), Object(aSubPanel), false)

9 /srv/ch1/suitecrm/include/SubPanel/SubPanelTiles.php(357): SubPanel->ProcessSubPanelListView('include/SubPane...', Array)

10 /srv/ch1/suitecrm/include/MVC/View/SugarView.php(1167): SubPanelTiles->display()

11 /srv/ch1/suitecrm/include/MVC/View/SugarView.php(239): SugarView->_displaySubPanels()

12 /srv/ch1/suitecrm/include/MVC/Controller/SugarController.php(435): SugarView->process()

13 /srv/ch1/suitecrm/include/MVC/Controller/SugarController.php(375): SugarController->processView()

14 /srv/ch1/suitecrm/include/MVC/SugarApplication.php(113): SugarController->execute()

15 /srv/ch1/suitecrm/index.php(52): SugarApplication->execute()

16 {main}

`

Possible Fix

As a workaround, I edited modules/Leads/metadata/subpaneldefs.php and removed line 51 'sort_by' => 'date_start'. It fixes the problem.

Steps to Reproduce

1. Disable AJAX for Leads and Calls
2. Create a lead L
3. Go to lead detailed view
4. Create a call for L
5. You got an error message.
6. Refresh
7. Detailed view is broken

Your Environment

• SuiteCRM Version used: 7.10.10
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Firefox 64.0 64bit
• Environment name and version (e.g. MySQL, PHP 7): php-fpm 7.2, MariaDB 10.1.26
• Operating System and version (e.g Ubuntu 16.04): Debian Stretch 9.0 (stable)

[tech-ch1]

I tried with AJAX enabled, and the problem is still here.
However, I tried on the demo version, and I couldn't reproduce the bug, so, other modules might be involved.

[markbond1007]

Hi,

I can confirm this bug, I am getting it here on the Events View (I have added an activities relationship to Events). I can go a bit further though, I also can't reproduce it on a clean install however I do have a snapshot (image) of the "custom files" mentioned when I performed the upgrade. ALSO if I find an event with no tasks/calls/activities and then order the Activities panel by something else (ie subject), its fine until the next browser session is started.

If you set the system to debug mode and capture the SQL generated, its a huge UNION statement (as provided by you above), however on a clean install (with the added relationship) when I visit the page the (similar) SQL statement has no order by statement on it.

Do note the large select with unions has the 'order by' statement right at the end, outside of the individual selects that are unioned together, this IS legitimate BUT you would need a date_start field in each of the selects (or a field that is aliased to date_start), this field is NOT present in the selected fields, hence the error.

Regards

Mark

[markbond1007]

Okay a bit more on this (and I found other bugs/issues on here relating to this where Dillon has been fixing things). The reason you wont find this in a clean 7.10.10 install is because the issue is now with the upgrade process rather than core code (the core code is fine - the bugs have been fixed there it seems). Basically a lot of clearing up has been done and the sort by has now been swapped from date_start to date_due (the bugs were where all the code wasnt updated to reflect that)

I noticed in Studio that the Activities relationship is not one that can be deleted/amended and isnt marked as being managed/created by studio. I am assuming therefore that Studio is having an issue "updating" the generated code for this. On a clean 7.10.10 install with the activities relationship added to Events I ran this:
grep -ir "sort_by' => 'date_start" $(find . -name '*.php') | more
and got this:
./modules/Tasks/metadata/subpanels/ForActivities.php: 'sort_by' => 'date_start', ./modules/Emails/metadata/subpaneldefs.php: 'sort_by' => 'date_start', ./modules/Campaigns/metadata/subpaneldefs.php: 'sort_by' => 'date_start',

I ran the same command on my upgraded version and got this (stripped a lot of upgrade directories):

./custom/modules/FP_events/Ext/Layoutdefs/layoutdefs.ext.php: 'sort_by' => 'date_start', ./custom/Extension/modules/FP_events/Ext/Layoutdefs/fp_events_activities_1_calls_FP_events.php: 'sort_by' => 'date_start', ./custom/Extension/modules/relationships/layoutdefs/fp_events_activities_1_calls_FP_events.php: 'sort_by' => 'date_start', ./modules/Contacts/metadata/subpaneldefs.php: 'sort_by' => 'date_start', ./modules/Prospects/metadata/subpaneldefs.php: 'sort_by' => 'date_start', ./modules/Tasks/metadata/subpanels/ForActivities.php: 'sort_by' => 'date_start', ./modules/Leads/metadata/subpaneldefs.php: 'sort_by' => 'date_start', ./modules/Emails/metadata/subpaneldefs.php: 'sort_by' => 'date_start', ./modules/Accounts/metadata/subpaneldefs.php: 'sort_by' => 'date_start', ./modules/Campaigns/metadata/subpaneldefs.php: 'sort_by' => 'date_start',

It looks like the upgrade patch doesnt include all of the updates that the full download does (hence the subpanel issue) plus Im not sure if its becuase of that OR becuase it cant properly control the relationship regeneration that the first 3 files listed have a problem

[markbond1007]

Even more interestingly the file:
/custom/modules/FP_events/Ext/Layoutdefs/layoutdefs.ext.php

In the upgraded version actually had two entries for each of activities and History, they had creation dates at the top, the newer (correctly specified) ones (created at the upgrade time) were at the top of the file, the older ones were below therefore overwriting the array and breaking things.

I editted those top 3 files (commenting out the duplicated entries in the layoutdefs.ext.php) and it did fix my problem, and I am assuming permanently as they mainly in the custom directory. However, its likely that others will have this problem as it does appear that the upgrade didn't fully work.

Regards

Mark

[pgorod]

custom/modules/FP_events/Ext/Layoutdefs/layoutdefs.ext.php

is a generated file, look at the sources for it under

custom/Extension/modules/FP_events/Ext/

[markbond1007]

Yes I know, thats part of what I am saying, that generated file is generated incorrectly as a part of the upgrade. After the upgrade (and after several repair/rebuilds) it still contained both an older generated version of the Activities & History subpanels and the newer one. As the older ones came later on in the file they were over-writing the built array.

[pgorod]

Oh, now I get it, thanks. Can you please use this trick and analyze which files are being read and written during the QR&R?
https://pgorod.github.io/Audit-File-Accesses/

[markbond1007]

Okay file attached for your perusal (should just contain the QR&R).
audit.log

[pgorod]

Does your log contain both reads and writes, or only reads? I thought (from memory) that these should be distinguishable in the log, but I can't see anything there to tell them apart.

Please go in your ModuleInstall\ModuleInstaller.php file and find this line

and right after the $GLOBALS['log']->debug command, add these three lines:

                               $extension .= "\n\n// " . get_class($this) . "->merge_files(): found " . $module_install.$entry . "\n" ;
                               $extension .= "// Reading " . substr_count( $file, "\n" ) . " lines last modified on " . date ("F d Y H:i:s.", filemtime($module_install . '/' . $entry)) . "\n";
                               $extension .= "// -------------------------------------------------------------------------------------------------------------\n";

Now do a QR & R again and it should tell exactly from which file it's picking up each part.

[markbond1007]

That was the audit log I have modified the file mentioned and uploaded my suitecrm.log file below (its in DEBUG mode so quite large):

suitecrm.log

[pgorod]

I prefer not to look at your huge log file :-)

That's not where you should be looking, it's in the generated files at custom/Extension/modules/FP_events/Ext/

Also note that you have some _override files, remember these get special treatment:

custom/Extension/modules/FP_events/Ext/Layoutdefs/_overrideFP_events_subpanel_fp_events_notes_1.php

custom/Extension/modules/FP_events/Ext/Layoutdefs/_overrideFP_events_subpanel_mandi_mandiresponses_fp_events.php

custom/Extension/modules/Accounts/Ext/Layoutdefs/_overrideAccount_subpanel_accounts_fp_events_1.php

[pgorod]

Just an extra explanation: during Quick Repair & Rebuild all the extension files are collected in two runs.

• 1st run collects all files not starting with _override
• 2nd run collects all which start with _override

This ensures the overrides are at the end, making them effective over and above any previous definitions.

[markbond1007]

Ahh sorry I thought you wanted the log file.

layoutdefs.ext.txt

I have uploaded the offending generated file, its not currently got the problem becuase as mentioned I manually updated the file in custom that casued the problem in this file. However I can now tell you which file caused it:

// ModuleInstaller->merge_files(): found custom/Extension/modules/FP_events/Ext/Layoutdefs/fp_events_activities_1_calls_FP_events.php

This file is merged into the file after tasks one, the calls version had the date_start instead of date_due (and thus over-wrote the correct information provided by tasks).

The _override files must have been generated by studio as I certainly didn't create them ;-) but looking at them they don't appear to be anything that would cause this.

[pgorod]

So I'm a bit lost now, should I conclude this was just a problem with your customizations, or do you think there is any bug in SuiteCRM?

Note that probably you were redefining too much in your customizations, if you want to tweak only a small detail you shouldn't re-declare the entire array, but just direct your change to the specific entry needs to be changed.

Also, when SuiteCRM upgrades and finds it is updating files that won't be recognized, because they will be overridden by something in your custom folder, it warns you on screen. You can review these warnings later in upgradeWizard.log.

[markbond1007]

No I don't think its a problem with my customisations (per se), I think its more likely a logic issue to do with the way Activities are handled/assigned.

So to try to clarify:

I have added activities as a relationship to Events prior to the update (perfectly fine works as expected). however Activities is a "Special" child in that there is no real set of DB objects called activities, but a series of relationships to tasks,calls,meetings etc. Once added its not recognised in the studio as something that's been added by the studio (it only sees the individual relationships that can't be edited/deleted) this may or may not be a contributing factor. When the update happened the events/calls relationship was NOT updated to use the date_due sort function at the Event/Activities level leaving date_start as the erroneous sort method, strangely (or not) the Event/Tasks relationship was updated.

Additionally several subpaneldefs.php files appear to have not copied across during the update (if you look on this thread back a few posts you can see which subpaneldefs.php havent copied). So if I unpack the upgrade 7.10.x-7.10.10 file and look at the file:
SuiteCRM-Upgrade7.10-to-7.10.10/modules/Leads/metadata/subpaneldefs.php
it clearly shows a 'sort_by' => 'date_due'.

If I compare that to the file in my upgraded site (so CRM/www/modules/Leads/metadata/subpaneldefs.php) this file is still on date_start

If I check the upgradeWizard.log file I can see where it has backed up that file, but nowhere that it has copied the new one in.

Just to re-iterate, I am not currently suffering a problem (at least on my dev site) as I manually fixed the 3 files with the problem, however I expect there may be others with the problem and it may be something that causes more problems down the line if its not fixed.

Hope that helps

Regards

Mark

[pgorod]

Ok, thanks for the detailed explanation.

@Dillon-Brown, do you see any reason why the files wouldn't be copied during the upgrade?

[Dillon-Brown]

Yes, I've been able to confirm that it does appear to be a core issue with the upgrade packs, will be fixed in the next release. Thanks for the all the assistance debugging this issue @markbond1007, much appreciated.

[pgorod]

This also explains other people getting the date_due bug even after the upgrade that supposedly fixes it. It's good to know.