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

Product Support
1

Your software
My Mautic version is: v2.16.3
My PHP version is: v7.2.33
My Database type and version is: MariaDB v10.3.25

Your problem
app/console mautic:campaigns:trigger throws Integrity Constraint Violation for Duplicate entry for key ‘campaign_rotation’

These errors are showing in the log:

[2020-10-23 14:03:01] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO oacampaign_lead_event_log (rotation, date_triggered, is_scheduled, trigger_date, system_triggered, metadata_path_taken, event_id, lead_id, campaign_id, ip_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [2, "2020-10-23 14:03:01", 0, null, 1, "a:0:{}", null, null, 0, 238, 23582, 31, null]:  SQLS violation: 1062 Duplicate entry '238-23582-2' for key 'oacampaign_rotation' [] []
[2020-10-23 14:03:01] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2020-10-23 14:03:02] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2020-10-23 14:03:02] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2020-10-23 14:03:02] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2020-10-23 14:03:02] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2020-10-23 14:03:02] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2020-10-23 14:08:03] mautic.ERROR: CAMPAIGN: An exception occurred while executing 'INSERT INTO oacampaign_lead_event_log (rotation, date_triggered, is_scheduled, trigger_date, system_triggered, metadata_path_taken, event_id, lead_id, campaign_id, ip_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [2, "2020-10-23 14:08:03", 0, null, 1, "a:0:{}", null, null, 0, 238, 23582, 31, null]:  SQLS violation: 1062 Duplicate entry '238-23582-2' for key 'oacampaign_rotation' [] []
[2020-10-23 14:08:03] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2020-10-23 14:08:03] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2020-10-23 14:08:03] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2020-10-23 14:08:03] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2020-10-23 14:08:03] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []
[2020-10-23 14:08:03] mautic.ERROR: CAMPAIGN: The EntityManager is closed. [] []

Steps I have tried to fix the problem:

Have tried deleting the duplicate entries manually in the database hoping the mautic:campaigns:trigger would mark the entry as successfully inserted, however, the error just moves onto the next duplicate key.

Have tried upgrading Mautic to v3, but ran into bigger problems so I rolled back to this version. On rolling back, I downoaded a clean v2.16.3 from github & migrated my local.config to the fresh download directory. These error still fill up my logs every 5min due to cronjob firing the campaign:trigger task

Any assistance would be much appreciated.

regards,
Andrew

2

Try
mautic:segment:update --force' mautic:campaigns:update --force’

The issue refers to the ‘duplicate_rotation’ field entry being duplicated in the database table. The data model of Mautic does not allow this. Odd that it duplicates. I suspect the previous console command you executed with cron did not complete yet when you run this one.

I notice Integrity Constraint Violation mentioning the issue happening when cron jobs run tasks overlapping in his case. I think in this thread the issue is that a foreign key does not exist anymore / yet when the next action is triggered.

3

Thanks codes9

I don’t think my cron jobs are overlapping in their timing.
The mautic:segment:update --force completes successfully, with 0 contacts requiring updates in every segment. After checking that no more queries are being executed on the db, and then executing the mautic:campaigns:trigger after still produces the same Integrity Constraint error.

Output of manually executing this task:.

[mautic_fresh]$ app/console mautic:campaigns:trigger
Triggering events for campaign 20
Triggering events for newly added contacts
0 total events(s) to be processed in batches of 100 contacts

0 total events were executed
0 total events were scheduled

Triggering scheduled events
0 total events(s) to be processed in batches of 100 contacts

0 total events were executed
0 total events were scheduled

Triggering events for inactive contacts

0 total events were executed
0 total events were scheduled

Triggering events for campaign 24
Triggering events for newly added contacts
3 total events(s) to be processed in batches of 100 contacts
 3/3 [============================] 100%
1 total event was executed
2 total events were scheduled

Triggering scheduled events
0 total events(s) to be processed in batches of 100 contacts

0 total events were executed
0 total events were scheduled

Triggering events for inactive contacts

0 total events were executed
0 total events were scheduled

Triggering events for campaign 29
Triggering events for newly added contacts
0 total events(s) to be processed in batches of 100 contacts

0 total events were executed
0 total events were scheduled

Triggering scheduled events
0 total events(s) to be processed in batches of 100 contacts

0 total events were executed
0 total events were scheduled

Triggering events for inactive contacts

0 total events were executed
0 total events were scheduled

Triggering events for campaign 30
Triggering events for newly added contacts
89 total events(s) to be processed in batches of 100 contacts
 89/89 [============================] 100%
0 total events were executed
0 total events were scheduled

Triggering scheduled events
0 total events(s) to be processed in batches of 100 contacts

0 total events were executed
0 total events were scheduled

Triggering events for inactive contacts
1 total decisions(s) to be analyzed for inactivity for approximately 89 contacts in batches of 100
 89/89 [============================] 100%
0 total events were executed
0 total events were scheduled

Triggering events for campaign 31
Triggering events for newly added contacts
295 total events(s) to be processed in batches of 100 contacts
 295/295 [============================] 100%
0 total events were executed
0 total events were scheduled

Triggering scheduled events
11297 total events(s) to be processed in batches of 100 contacts
 11297/11297 [============================] 100%Triggering events for campaign 35
Triggering events for newly added contacts
3 total events(s) to be processed in batches of 100 contacts
 3/3 [============================] 100%Triggering events for campaign 46
Triggering events for newly added contacts
1046 total events(s) to be processed in batches of 100 contacts
 1046/1046 [============================] 100%Triggering events for campaign 49
Triggering events for newly added contacts
1021 total events(s) to be processed in batches of 100 contacts
 1021/1021 [============================] 100%Triggering events for campaign 50
Triggering events for newly added contacts
1011 total events(s) to be processed in batches of 100 contacts
 1011/1011 [============================] 100%Triggering events for campaign 51
Triggering events for newly added contacts
1830 total events(s) to be processed in batches of 100 contacts
 1830/1830 [============================] 100%Triggering events for campaign 53
Triggering events for newly added contacts
0 total events(s) to be processed in batches of 100 contacts

0 total events were executed
0 total events were scheduled

Triggering scheduled events
0 total events(s) to be processed in batches of 100 contacts

0 total events were executed
0 total events were scheduled

Triggering events for inactive contacts
 89/89 [============================] 100%
0 total events were executed
0 total events were scheduled

Triggering events for campaign 54
Triggering events for newly added contacts
14 total events(s) to be processed in batches of 100 contacts
4

Looks like it executes ok when you run it manually.

  • Do you still get the same errors in your log?

Have tried upgrading Mautic to v3, but ran into bigger problems so I rolled back to this version. On rolling back, I downoaded a clean v2.16.3 from github & migrated my local.config to the fresh download directory. These error still fill up my logs every 5min due to cronjob firing the campaign:trigger task

Did you roll back changes to your database as well? I.e. restore database from an older backup.

Upgrading Mautic makes changes to your database structure aka Migrations. Using a 3.0 database with a 2.16.3 installation could lead to unexpected results.

5

The same error appears in the error log when I execute the command manually - just no error indication in the terminal window.

Yes - database was rolled back to the correct version after the failed 3.0 upgrade attempt.

Is there a way to check if my db structure is correct for v2.16.3?

6

You can use these commands to check the Structure of your database. Clearing Cache will remove any cache referring to the old database schema after these steps.

BTW: Ensure you’re happy with your current backups before running any of these commands.

Run the Following Commands in the terminal

php /var/www/html/app/console doctrine:migration:migrate
This will check if there is any pending database migrations (structure changes)

This will apply the changes in the previous step to your Database.
php /var/www/html/app/console doctrine:schema:update --force

Refresh Cache to ensure everything works after this.
php /var/www/html/app/console cache:clear

7

run the update commands via SSH.
your/path/bin/console mautic:update:find
your/path/bin/console mautic:update:apply
then your/path/bin/console mautic:update:apply --finish
or
Download Mautic from https://www.mautic.org/download (Never Github)
copy files over and update local.php

8

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?

9

Outputs:
app/console doctrine:migration:migrate:

$ app/console doctrine:migration:migrate

                    Mautic Migrations


WARNING! You have 9 previously executed migrations in the database that are not registered migrations.
    >> 2018-11-11 09:54:47 (20181111095447)
    >> 2019-11-26 09:39:23 (20191126093923)
    >> 2019-12-19 15:56:30 (20191219155630)
    >> 2020-02-11 09:54:09 (20200211095409)
    >> 2020-02-12 14:15:30 (20200212141530)
    >> 2020-02-20 17:20:41 (20200220172041)
    >> 2020-02-27 11:04:31 (20200227110431)
    >> 2020-03-02 16:48:01 (20200302164801)
    >> 2020-04-22 14:43:00 (20200422144300)
Are you sure you wish to continue? (y/n)y
WARNING! You are about to execute a database migration that could result in schema changes and data lost. Are you sure you wish to continue? (y/n)y
No migrations to execute.

and:
app/console doctrine:schema:update --force :

$ app/console doctrine:schema:update --force


  [Doctrine\DBAL\DBALException]
  Unknown column type "datetime_immutable" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types
  \Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs d
  uring database introspection then you might have forgot to register all database types for a Doctrine Type. Use AbstractPlatform#
  registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you mi
  ght have a problem with the cache or forgot some mapping information.
10

There it goes again.
Mautic 3.2.1 php 7.3 MariaDb 10.4

No campaign is executed anymore. Entity Manager is closed and everything stands still.
Not even a regular testcampaign with only one send email action is working.

Has anyone encountered this as well?

This is the log output:

[2020-12-09 16:50:19] 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, campaign_id, ip_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’ with params [2, “2020-12-09 16:50:19”, 0, null, 1, “a:0:{}”, “email”, 28, 0, 97, “1”, 9, null]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘97-1-2’ for key ‘campaign_rotation’
[2020-12-09 16:50:19] mautic.ERROR: CAMPAIGN: The EntityManager is closed.
[2020-12-09 16:50:19] mautic.ERROR: CAMPAIGN: The EntityManager is closed.

1 Like
11

I have quite a few of these in my logs as well. I have a devops team looking at the issue. I run 3.2.0-rc and have been unsuccessful in upgrading to 3.2.1 so far. Those are my priority fixes before we start to work on performance. I will tag you when we find a solution

1 Like
12

Just to inform everyone who’s running into this as well:

The current PR #7129 seems to fix that issue.
We have it in production now and it looks good so far! :slight_smile: