Email campagin won't send (or queue all emails)

[lucakuehne]

Hi

Yesterday I created an email-campagin. Then I created a mass-email planned for todays morning. I also sent a test-email for this mass-email and everything looked fine.
Today I checked the campaign and saw that the massemail hasn't been sent. And only around 620 of the ~10'000 contacts in the targetlist have been queued for sending.

For testing purposed I created another campaign with just a smaller targetlist and the all emails did get cued and sent as well.

I guess this is a bug based on the size of the targetlist... Could anybody confirm that they have successfully sent an email-campaign with a targetlist bigger than 10'000 items?

Best regards
Luca

[yuri]

Hi Luca,

What status of Mass Email record? In Process, Complete or something else?

Anything in data/logs?

[lucakuehne]

Hi Yuri

The status of the mass-email record ist "Pending".

I was able to find some entries regarding mass emails in the logs:

[2020-03-31 04:30:08] Espo.ERROR: Job ProcessMassEmail#createQueue 5e81e21f01c05fa4c: [HY000] SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '=' [] []
[2020-03-31 04:35:07] Espo.ERROR: Job ProcessMassEmail#createQueue 5e81e21f01c05fa4c: [HY000] SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '=' [] []
[2020-03-31 04:40:16] Espo.ERROR: Job ProcessMassEmail#createQueue 5e81e21f01c05fa4c: [HY000] SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '=' [] []

That's a bit strange for me, as in the jobs in the administration all mass email jobs have been marked as "Success"

[yuri]

Seems the mysql error is a culprit. Must be different collations are used for some columns in database. I don't know where yet.

[yuri]

What entity types the faulty campaign has in the target list. Only contacts or mix of different entity types?

[lucakuehne]

Seems the mysql error is a culprit. Must be different collations are used for some columns in database. I don't know where yet.

So let's find out together :-) I'll help you as much as I can.

What entity types the faulty campaign has in the target list. Only contacts or mix of different entity types?

Just contacts and some leads.
I just removed the leads from the target list. The scheduled job for the processing of the mass emails did just run, but nothing different. I also recreated a new mass mail record. Just to be sure to have a clean record to start with. But saddly still the same error in the logs...

Also another question: What is the UID in the log entry? Is this the ID of the scheduled job or, the running job instance? Or the mass email or the contact?
EDIT: I just figured out the UID is the ID of the mass email

[yuri]

It's an ID of MassEmail record

[yuri]

Do you have the latest version of EspoCRM?

[yuri]

Please check the collations of the following columns in database:

table: contact
column: id

table: contact_target_list
column: contact_id

[lucakuehne]

Do you have the latest version of EspoCRM?

Yes, version 5.8.4

Please check the collations of the following columns in database:
table: contact
column: id

table: contact_target_list
column: contact_id

I checked them and they are they same:
contact.id => utf8_unicode_ci
contact_target_list.contact_id => utf8_unicode_ci

[yuri]

Try to replace application/Espo/Modules/Crm/Services/MassEmail.php file with this one: https://raw.githubusercontent.com/es.../MassEmail.php'

It's from the next version, maybe it will help.

[lucakuehne]

I replaced the file and restarted the apache, but the error still occurs.

[yuri]

Does it process the same number of targets every time? Maybe the error occurs at a specific record?

[lucakuehne]

Hi Yuri

Yes it does indeed stop ervery time after it has processed 621 contacts.
But how can I determine at which contact the proccess fails? I don't really see the logic on how the mass email processes the target items...

[yuri]

Hi Luca,

Please replace the file again: https://raw.githubusercontent.com/es.../MassEmail.php. I made a few changes to it.

Then.

Add to this line: https://github.com/espocrm/espocrm/blob/hotfix/5.8.5/application/Espo/Modules/Crm/Services/MassEmail.php#L179

the following code:

$GLOBALS['log']->error('Item count: '. count($itemList)); 

Wrap this line https://github.com/espocrm/espocrm/b...Email.php#L185 with try catch

try {
     $emailAddressRecord = $this->getEntityManager()->getRepository('EmailAddress')->getByAddress($emailAddress);
} catch (\Exception $e) {
   $GLOBALS['log']->error('Bad email address: ' . $emailAddress);
   throw new \Exception($e->getMessage());
}