Big Campaign Update failing in middle

[mikew]

Your software
My Mautic version is: 4.0.1
My PHP version is: 7.4
My Database type and version is: mariadb

Your problem
My problem is:

We are doing a big campaign update of 800K+ records. So I wanted to start doing some benchmarking for the client and was monitoring the update.
The benchmarking looks like the following:
1 minute: 10K records imported
10 minutes: 87k
20 minutes: 167K
31 minutes: 251K
40 minutes: 320K
50 minutes: 384K
60 minutes: 440K

Then when the import hit 62% (Imported 549K) it was thrown out with the following error:

The stream or file "/var/www/mautic/app/../var/logs/mautic_prod-2022-11-16.php" could not be opened in append mode: failed to open stream: Permission denied

I then try and rerun the campaign update again, it runs for a bit and then the same thing.

@rcheesley @joeyk - any ideas ?

Also if this happens on such a big campaign can anyone think of a way I could catch this error or monitor it and then restart the job again ?

[mikew]

When going through the php log from today and prepping only for CAMPAIGN entries I see the following:

[2022-11-16 07:05:44] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_leads (date_added, manually_removed, manually_added, date_last_exited, rotation, campaign_id, lead_id) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2022-11-16 07:0
5:44", 0, 0, null, 1, 727, "484495"]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '727-484495' for key 'PRIMARY' [] []
[2022-11-16 07:20:43] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_leads (date_added, manually_removed, manually_added, date_last_exited, rotation, campaign_id, lead_id) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2022-11-16 07:2
0:43", 0, 0, null, 1, 727, "739800"]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '727-739800' for key 'PRIMARY' [] []
[2022-11-16 07:35:44] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_leads (date_added, manually_removed, manually_added, date_last_exited, rotation, campaign_id, lead_id) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2022-11-16 07:3
5:44", 0, 0, null, 1, 727, "879230"]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '727-879230' for key 'PRIMARY' [] []
[2022-11-16 07:50:44] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_leads (date_added, manually_removed, manually_added, date_last_exited, rotation, campaign_id, lead_id) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2022-11-16 07:5
0:44", 0, 0, null, 1, 727, "1075882"]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '727-1075882' for key 'PRIMARY' [] []
[2022-11-16 08:00:04] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_lead_event_log (rotation, date_triggered, is_scheduled, trigger_date, system_triggered, metadata, channel, channel_id, non_action_path_taken, event_id, lead_id, c
ampaign_id, ip_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [3, "2022-11-16 08:00:04", 0, null, 1, "a:0:{}", "mobile_notification", null, 0, 402, "5492184", 31, null]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '402-54921
84-3' for key 'campaign_rotation' [] []
[2022-11-16 08:00:05] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_lead_event_log (rotation, date_triggered, is_scheduled, trigger_date, system_triggered, metadata, channel, channel_id, non_action_path_taken, event_id, lead_id, c
ampaign_id, ip_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [3, "2022-11-16 08:00:05", 0, null, 1, "a:0:{}", "email", 32, 0, 121, "1206546", 12, null]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '121-1206546-3' for key 'c
ampaign_rotation' [] []
[2022-11-16 08:05:48] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_leads (date_added, manually_removed, manually_added, date_last_exited, rotation, campaign_id, lead_id) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2022-11-16 08:0
5:48", 0, 0, null, 1, 727, "1149634"]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '727-1149634' for key 'PRIMARY' [] []
[2022-11-16 08:10:10] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_lead_event_log (rotation, date_triggered, is_scheduled, trigger_date, system_triggered, metadata, channel, channel_id, non_action_path_taken, event_id, lead_id, c
ampaign_id, ip_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [2, "2022-11-16 08:10:10", 0, null, 1, "a:0:{}", "email", 31, 0, 116, "650957", 12, null]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '116-650957-2' for key 'cam
paign_rotation' [] []
[2022-11-16 08:26:28] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_leads (date_added, manually_removed, manually_added, date_last_exited, rotation, campaign_id, lead_id) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2022-11-16 08:2
6:28", 0, 0, null, 1, 727, "1241546"]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '727-1241546' for key 'PRIMARY' [] []
[2022-11-16 08:44:55] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_leads (date_added, manually_removed, manually_added, date_last_exited, rotation, campaign_id, lead_id) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2022-11-16 08:4
4:55", 0, 0, null, 1, 727, "2056416"]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '727-2056416' for key 'PRIMARY' [] []
[2022-11-16 08:45:05] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2022-11-16 08:50:58] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_leads (date_added, manually_removed, manually_added, date_last_exited, rotation, campaign_id, lead_id) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2022-11-16 08:5
0:58", 0, 0, null, 1, 727, "2462150"]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '727-2462150' for key 'PRIMARY' [] []
[2022-11-16 08:51:07] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2022-11-16 09:00:07] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO campaign_lead_event_log (rotation, date_triggered, is_scheduled, trigger_date, system_triggered, metadata, channel, channel_id, non_action_path_taken, event_id, lead_id, c
ampaign_id, ip_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [2, "2022-11-16 09:00:07", 0, null, 1, "a:0:{}", "email", 92, 0, 411, "262140", 31, null]:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '411-262140-2' for key 'cam
paign_rotation' [] []

[joeyk]

Hi, I never experienced this, but we are not running campaigns for so long time.

[mikew]

@mikew

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

Thanks @joeyk

To add to this thread I have seen a number of different posts on this topic as well:

Mautic:campaigns:trigger throws Integrity Constraint Violation for Duplicate entry for key 'campaign_rotation'

Having exactly the same issue on mautic 3.1. After duplicate key violation which caused due to jump to event issue. (Github The real issue here from my end is when this error pops up all scheduled campaigns actions that will be triggered afterwards wouldnt take any action not in this run and not afterwards. Any idea how to bypass this error so it wont affect the rest of the campaigns?

maybe @escopecz can help she some light here ?

[joeyk]

I think it also matters how you run your campaign commands. I suggest paralell running using ID range in each thread.

[mikew]

when you say ID range are you referring to running a specific campaign with -i [campaign_id]

If so yes this is what I am doing - we have our crontab builder that I sent you a while ago that does this

[joeyk]

No. I’m referring to contact ID.
I’m sorry, I didn’t have time to look at the cronjob builder in detail, I realized it was too complicated code for me

[mikew]

how would you go about doing this on a db of 1M ?

[joeyk]

Create ranges of contacts.
We pull in the campaign members by campaign, create even batches and run the commands paralell via bash script.
The end command looks like this apprx.
php /var/www/html/mautic/bin/console mautic:campaign:trigger --min-contact-id="$minId" --max-contact-id="$maxId" --thread-id="$thisbatch" --campaign-i="$thisCampaign" -f

This way we have 10 threads processing equal number of conacts every minute. The script runs until the total id pool is completed and moves to the next campaign. It is still work in progress, but it kinda works.

I’m not saying your way is not correct, just saying we use this

[mikew]

Hey Joey, sorry for going on here, what would the midID and maxID be ?
If the DB is 1M, but so many anonymous users such that there can be a userid with id 5M

[joeyk]

No worries, (sorry im on mobile)
You are defining the range:
–min-contact-id=1 --max-contact-id=10000
–min-contact-id=10001 --max-contact-id=20000
–min-contact-id=20001 --max-contact-id=30000
Etc…

[mikew]

hmm… I wonder how this will be dealt with huge DB, any thoughts on how big the range can be from min to max ?

[joeyk]

I would do it this way (based on server resources)
Chop up the 1M into 10 threads, each 10k users.
Then I would measure how long it runs.
Let’s say 2 min.
Then I would time the next batch into 2 min afterwards.
You can alternatively look into this:

Mauticast #37 🔈 Replacing Cron Jobs by Timers (Klemen Kobetič)

[escopecz]

Please test Avoid increment twice for campaign jump to events by fedys · Pull Request #10206 · mautic/mautic · GitHub if it solves your problem

[mikew]

Thanks @escopecz will install and test, just hard to reproduce this one. Will keep you updated

[joeyk]

@mikew
This supposed to fix multi.threading:

Fix campaign trigger command multithreading