Database failure in Campaigns display

ggraynoth · 23 March 2023 10:32

Everything in our installation works fine with the exception of a recurring error, which we would like to fix.

When displaying Campaigns and picking any campaign, we get an error in the display for the Target list and e-mail marketing entry even though these exist and the campaigns run successfully via that campaign wizard.

The error is ‘Database Failure - please refer to suitecrm log for details’. I have seen other posts with similar issues but in different areas of the app.

The log file entries that relate are below;

where prospect_lists.deleted=0) ORDER BY prospect_lists.name asc LIMIT 0,10: MySQL error 1250: Table ‘prospect_lists’ from one of the SELECTs cannot be used in global ORDER clause
Thu Mar 23 10:16:47 2023 [3812][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Thu Mar 23 10:16:49 2023 [3812][1][FATAL] Mysqli_query failed.
Thu Mar 23 10:16:49 2023 [3812][1][FATAL] Error retrieving Campaign list: Query Failed: (SELECT prospect_lists.id , prospect_lists.name , prospect_lists.description , prospect_lists.list_type , prospect_lists.assigned_user_id , ‘prospectlists’ panel_name FROM prospect_lists INNER JOIN prospect_list_campaigns ON prospect_lists.id=prospect_list_campaigns.prospect_list_id AND prospect_list_campaigns.campaign_id=‘da418580-3a7c-a7e0-3ad4-6419ea21764e’ AND prospect_list_campaigns.deleted=0

** where prospect_lists.deleted=0) ORDER BY prospect_lists.name desc LIMIT 0,10: MySQL error 1250: Table ‘prospect_lists’ from one of the SELECTs cannot be used in global ORDER clause**
Thu Mar 23 10:16:49 2023 [3812][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Thu Mar 23 10:16:53 2023 [3812][1][FATAL] Mysqli_query failed.
Thu Mar 23 10:16:53 2023 [3812][1][FATAL] Error retrieving Campaign list: Query Failed: (SELECT prospect_lists.id , prospect_lists.name , prospect_lists.description , prospect_lists.list_type , prospect_lists.assigned_user_id , ‘prospectlists’ panel_name FROM prospect_lists INNER JOIN prospect_list_campaigns ON prospect_lists.id=prospect_list_campaigns.prospect_list_id AND prospect_list_campaigns.campaign_id=‘da418580-3a7c-a7e0-3ad4-6419ea21764e’ AND prospect_list_campaigns.deleted=0

** where prospect_lists.deleted=0) ORDER BY prospect_lists.list_type asc LIMIT 0,10: MySQL error 1250: Table ‘prospect_lists’ from one of the SELECTs cannot be used in global ORDER clause**
Thu Mar 23 10:16:53 2023 [3812][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Thu Mar 23 10:16:57 2023 [3812][1][FATAL] Mysqli_query failed.
Thu Mar 23 10:16:57 2023 [3812][1][FATAL] Error retrieving Campaign list: Query Failed: (SELECT prospect_lists.id , prospect_lists.name , prospect_lists.description , prospect_lists.list_type , prospect_lists.assigned_user_id , ‘prospectlists’ panel_name FROM prospect_lists INNER JOIN prospect_list_campaigns ON prospect_lists.id=prospect_list_campaigns.prospect_list_id AND prospect_list_campaigns.campaign_id=‘da418580-3a7c-a7e0-3ad4-6419ea21764e’ AND prospect_list_campaigns.deleted=0

** where prospect_lists.deleted=0) ORDER BY prospect_lists.name desc LIMIT 0,10: MySQL error 1250: Table ‘prospect_lists’ from one of the SELECTs cannot be used in global ORDER clause**
Thu Mar 23 10:16:57 2023 [3812][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.

This is SuiteCRM v 8.2.3 PHP 7.4.3 Windows based installation on Apache and MySQL v 8.0.31 within WAMP.

Screenshot below.

I don’t want to dive in and start changing code in line with some of the other post suggestions without checking with the community that this will fix the problem.

Thanks for any help in advance.

pgr · 23 March 2023 11:46

Is your issue the same as this one?

github.com/salesagility/SuiteCRM-Core

Unable to save selected template in campaign

opened 04:53PM - 04 Mar 23 UTC

jlavsund

Type:Bug Status: Team Investigating Area: Legacy

Issue: When I try to save the selected template in the campaign wizard I get …this error in the browser console. EmailTemplate.js:69 Uncaught TypeError: window.parent.$ is not a function at save (EmailTemplate.js:69:15) at EmailTemplateController (EmailTemplate.js:77:55) at HTMLInputElement.onclick (index.php?return_module=Campaigns&campaign_id=1c129887-849e-9e82-7c3a-64030d550782&return_action=WizardNewsLetter&return_id=1c129887-849e-9e82-7c3a-64030d550782&module=Campaigns&action=WizardMarketing:959:116) save @ EmailTemplate.js:69 EmailTemplateController @ EmailTemplate.js:77 onclick @ index.php?return_module=Campaigns&campaign_id=1c129887-849e-9e82-7c3a-64030d550782&return_action=WizardNewsLetter&return_id=1c129887-849e-9e82-7c3a-64030d550782&module=Campaigns&action=WizardMarketing:959 Expected Behavior: Being able to save the template and click next to continue with the campaign wizard. Actual Behavior: Nothing happens except for the error message in the browser developer console. Steps to Reproduce: 1) Create new campaign. 2) Select email. 3) Fill in all the fields and click next. 4) Select a target list and click next. 5) Select a template and click save. Nothing happens. Environment: SuiteCRM 8.2.4 on an Ubuntu 20.04.5 server with MariaDB 10.3.38 and PHP 7.4.3. and Chrome 110.0.5481.178 on Windows 11.

Please check the browser console for Javascript errors to see if it matches that.

ggraynoth · 23 March 2023 14:50

Hi

Thanks for the quick response. I am not sure that it is the same. I can run the Campaign wizard with no problem at all and the emails get sent at the scheduled time etc…

This is when I go into view a Campaign and select any campaign listed, all of which have been successfully executed via the Wizard. even though a target list has been attached etc… in the display I get the database failure as shown in the screen shot and the log entries. It is in the display sub-tables.

It is closer to another post where it was suggested that a change to SugarBean.php may fix the issue but it is restricted only to the Campaign display screen, not anywhere else that I can see.

If it was a MySQL issue, I would expect this everywhere.

As I said, happy to change code if it is recognisable but don’t really want to start changing code at a guess.

I am going to check on the demo to see if it is there as well.

ggraynoth · 23 March 2023 15:28

OK - now fixed.

I took a leap of faith and edited the SugarBean.php file with one very simple change suggested in a post where someone had a similar issue in the Projects module.

Around line 930 (it won’t be exactly there but close to) it was suggested to comment out a line relating to the query. The section of code from SugarBean.php is below. I commented out the line in bold, saved and reloaded the app. All now fine. These minor frustrations take a disproportionate time to get sorted but a good look at the other posts has got me there so far.

//Put the query into the final_query
$query = $subquery[‘select’] . " " . $subquery[‘from’] . " " . $subquery[‘where’];
if (!$first) {
$query = ’ UNION ALL ( ’ . $query . ’ )';
$final_query_rows .= " UNION ALL ";
} else {
//$query = ‘(’ . $query . ‘)’;
$first = false;

pgr · 23 March 2023 17:43

I think that was already fixed in 8.2.4, so if you had upgraded I believe it would have been solved.

ggraynoth · 24 March 2023 15:51

OK, thank you. Having just got 8.2.3 fully configured and operational, we were keen to have this as a stable production release, which has been very well received by the users. We were therefore keen to get it fully accepted and operational before starting upgrades or any further customisation.

Good to know that the new version addresses this and I am sure we will upgrade in due course.

Great product and thanks to you and everyone for their support thus far.