SuiteCRM v8.2.3
PHP 7.4.33
MySQL 8.0.31
Windows 10 install.
Everything has worked fine with campaigns for weeks, until this afternoon, We ran an initial campaign to one customer - 10 e-mails - and all worked fine as normal.
After running a number of other campaigns of similar sizes, the emails go into the email queue. we normally always send these manually, not via a scheduler, so that we can catch any problems (bouncebacks not corrected etc,…) and this has always worked.
Individual outgoing mails work fine both system and individual users.
The fatal error in the log file for the error that has just started is below and we are at a loss as to what has caused it or how to fix, as nothing whatsoever has been changed on the system. This is one logged on as admin. Any help gratefully received.
Mon Mar 27 16:57:01 2023 [2972][1][FATAL] Query Failed: SELECT favorites.*
FROM favorites
JOIN config ON ( config.id = favorites.parent_id )
WHERE favorites.assigned_user_id = ‘1’
AND favorites.parent_type = ‘Administration’
AND favorites.deleted = 0
AND config.deleted = 0
ORDER BY favorites.date_entered DESC LIMIT 0,10: MySQL error 1054: Unknown column ‘config.deleted’ in ‘where clause’
Thank you for doing this. I have left a comment on the issue page, highlighting the business impact.
We have had to suspend SuiteCRM Campaigns as of today, as we can’t get emails out. So my teams task this morning has been to export the lists that are on the current plan and re-create the email templates in MS-Word. We have used Word mailmerge with excel lists to send the ones that got stuck yesterday and will carry on like this until we can get a fix which works.
These emails are a very important part of both marketing to our existing customers and also lead generation from prospect accounts and work to a monthly plan.
I think that the business will tolerate this for a few weeks (it is what we used to do) but will then force a change of CRM, which I very much hope is not necessary, as SuiteCRM has become a very valued asset and massive improvement over what we have now had to go back to.
Made the change to the .PHP file but this hasn’t had any impact.
We ran a 14 contact target mail list campaign to a single existing customer. The campaign ran fine, as always. We set it to mail within 15 minutes and sent the emails.
They all went into the email queue straight away but will not send when we click the send emails button. The screen refreshes, as normal, but the emails remain where they are.
I have left the code as per your change but can put the old line back in if required. I have simply renamed the original fine to .old and changed the .PHP in the directory.
Interestingly, there were no entries in the log file at all, which suggests that this has removed the FATAL error but still not fixed the issue.
Thank you for you help so far. I think we are getting closer
My fix was only aimed at that FATAL error, I am glad to hear it solves it.
I would treat the rest of the problem as a separate problem altogether, we would now need to hunt for new clues (any log messages?) for that one.
But you might want to check one thing first. You test might be hitting a line of defense that SuiteCRM has to avoid duplicate getting sent to the same emails. It’s a common problem with people testing Campaigns…
You can check this by seeing the Campaign status reports, these emails would appear as “suppressed” (or some similar expression, I don’t remember).
You can delete the corresponding lines in campaign_log database table to force-reset that.
Many thanks @pgr. I will look into the campaign report as suggested, although the one that we ran this morning was to new contacts that have not previously been emailed at all.
Also I will look to update the code with the additional array.
I hope we can find the root cause of the e-mail queue issue.
I made the latest set of changes as per your Github message. This produced the same result as as expected, so this more comprehensive fix for the initial FATL error appears to work.
I created a Campaign status report and this then started procucing errors for everey campaign in the list as below;
Thu Mar 30 13:25:52 2023 [18128][1][FATAL] Mysqli_query failed.
Thu Mar 30 13:25:52 2023 [18128][1][FATAL] Query Failed:
SELECT `campaigns`.name AS 'Name0', `campaigns`.status AS 'Status1', `campaigns`.content AS 'Description2', `campaigns:contacts`.campaign_id AS 'Campaign3', `campaigns`.id AS 'campaigns_id', AS 'campaigns:contacts_id'
FROM `campaigns` LEFT JOIN prospect_list_campaigns `campaigns:contacts`_plc ON `campaigns:contacts`_plc.campaign_id = `campaigns`.id
LEFT JOIN prospect_lists_prospects `campaigns:contacts`_plp ON `campaigns:contacts`_plp.prospect_list_id = `campaigns:contacts`_plc.prospect_list_id AND `campaigns:contacts`_plp.related_type = 'Contacts'
LEFT JOIN contacts `campaigns:contacts` ON `campaigns:contacts`.id = `campaigns:contacts`_plp.related_id AND `campaigns:contacts`.deleted=0
WHERE ( `campaigns`.name = 'WPP Coretech' AND campaigns.deleted = 0 ) LIMIT 0,20
: 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 'AS 'campaigns:contacts_id' FROM `campaigns` LEFT JOIN prospect_list_campaigns `' at line 1
I am unsure as to whether these are in any way related to the other problem as I have personally never run a report around campaigns. The list did however work and shows that in all completed campaign runs, the status of the campaign is active and all recipients seem to also be flagged as active.
I am not sure if this helps or is a ‘red herring’ but the campaign run yesterday is still in the email queue and I ran an additional one and it added to the queue and is also stuck.
OK so I have now gone directly into the DB. The values in the various fields that others have said they have fixed with some SQL are already set correctly. However in the emailman table the 14 entries ‘stuck’ in the queue are the only ones in there and I can’t for the life of me see anything wrong with the entries. Send_attempts=0 as do the other relevant fields, so can’t see why they are stuck due to this.
However in the campaign_log table there were 3 entries which had an activity type of ‘blocked’, when all of the others were ‘targeted’. I manually updated these but that didn’t make any difference either. trying to find this seems like a needle in a haystack for the uninitiated.
Ok thanks. I have tried everything in campaigns and created a single contact target list. Ran the campaign and it worked and email was sent despite the 14 stuck emails in the queue.
I can’t fathom the logic as to how some go through and others dont. I did the same again, new target list with a single contact from the account with the stuck emails and this got stuck as well. Puzzled.
Note that it’s the presence of an email address in the campaign_log table, with the same marketing_id that makes emails get “blocked”. So if you want a full reset you should just delete the entries in the campaign_log table that have some email address.
Thank you. An ‘undocumented feature’. I removed the blocked entries and that issue is addressed.
This morning, I re-ran a campaign after clearing the email queue and the same result. items stuck in the queue again with the same number of entries in the emailman table.
Error below; Fri Mar 31 10:14:48 2023 [14356][1][FATAL] Mysqli_query failed. Fri Mar 31 10:14:48 2023 [14356][1][FATAL] Error retrieving Campaign list: Query Failed: (SELECT emailman.* , campaigns.name as campaign_name, email_marketing.name as message_name, (CASE related_type WHEN 'Contacts' THEN LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) WHEN 'Leads' THEN LTRIM(RTRIM(CONCAT(IFNULL(leads.first_name,''),' ',IFNULL(leads.last_name,'')))) WHEN 'Accounts' THEN accounts.name WHEN 'Users' THEN LTRIM(RTRIM(CONCAT(IFNULL(users.first_name,''),' ',IFNULL(users.last_name,'')))) WHEN 'Prospects' THEN LTRIM(RTRIM(CONCAT(IFNULL(prospects.first_name,''),' ',IFNULL(prospects.last_name,'')))) END) recipient_name FROM emailman LEFT JOIN users ON users.id = emailman.related_id and emailman.related_type ='Users' LEFT JOIN contacts ON contacts.id = emailman.related_id and emailman.related_type ='Contacts' LEFT JOIN leads ON leads.id = emailman.related_id and emailman.related_type ='Leads' LEFT JOIN accounts ON accounts.id = emailman.related_id and emailman.related_type ='Accounts' LEFT JOIN prospects ON prospects.id = emailman.related_id and emailman.related_type ='Prospects' LEFT JOIN prospect_lists ON prospect_lists.id = emailman.list_id LEFT JOIN email_addr_bean_rel ON email_addr_bean_rel.bean_id = emailman.related_id and emailman.related_type = email_addr_bean_rel.bean_module and email_addr_bean_rel.primary_address = 1 and email_addr_bean_rel.deleted=0 LEFT JOIN campaigns ON campaigns.id = emailman.campaign_id LEFT JOIN email_marketing ON email_marketing.id = emailman.marketing_id INNER JOIN (select min(id) as id from emailman em GROUP BY em.related_id,em.marketing_id) secondary on emailman.id = secondary.id WHERE emailman.campaign_id = '8a574497-574a-3c1b-f0e7-6425557c9ce5' AND emailman.deleted=0 AND emailman.deleted=0) ORDER BY emailman.id desc LIMIT 0,10: MySQL error 1250: Table 'emailman' from one of the SELECTs cannot be used in global ORDER clause
I also noticed that after running the campaign and sending the emails, I checked the campaign status and in scrolling down and opening the Message Queue row, there is the ‘Database failure. Please refer to suitecrm.log for details.’ and a corresponding fatal error in the SuiteCRM log file, as below;
Fri Mar 31 10:14:48 2023 [14356][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
I seem to be chasing my tail on this as we are still running our campaigns outside of SuiteCRM.
Apologies, you will probably know anyway but the last Fatal log entry was actually from the emailman issue. The message status display does show the error but doesn’t log a FATAL in the SuiteCRM log.
