Removal of the "Enable deprecated Embedded Activity Revisions" setting ($85) · Snippets

Yo @DaveD thanks for this report. I've bumped into this issue pre-upgrade and before I proceed with the upgrade or even decide what to do I want to look at these activities.

I ran this query to find the activities that meet this criteria (I had over 9000!) after which you can use Find Activities to locate the activity searching by Subject and try to get a sense of what revisions may exist and how recently they were created.

SELECT id, activity_date_time, subject FROM civicrm_activity WHERE is_current_revision = 0 ORDER BY activity_date_time DESC;

Hi,

It's quite likely I'm not understanding this. But, does...

In 5.54 the setting is being removed from the UI and the core code will start ignoring it completely, i.e. no new revisions will ever be created, it will just overwrite the current revision, the same as non-case activities.

...mean that, if running, 5.57, revisions of Case Activities shouldn't be being created?

Because they are on our installation (CiviCRM 5.57.1).

That's what I'm looking at here, right?

Both these records were created a few days ago, and the second is a revision of the first?

{
  "id": 1277491,
  "source_record_id": null,
  "activity_type_id": 93,
  "subject": "(Filed on case 388994) (Filed on case 388994) HV Catch up",
  "activity_date_time": "2023-05-04 10:30:00",
  "is_current_revision": false,
  "original_id": null,
  "is_deleted": false,
  "created_date": "2023-03-29 10:37:02",
  "modified_date": "2023-03-30 15:27:39"
},
{
  "id": 1280023,
  "source_record_id": null,
  "activity_type_id": 93,
  "subject": "HV Catch up",
  "activity_date_time": "2023-05-04 10:30:00",

  "is_current_revision": true,
  "original_id": 1277491,
  "created_date": "2023-03-30 15:28:16",
  "modified_date": "2023-03-30 15:28:44"
}

The reason I'm interested is that I'm trying to track down a problem we've been having recently with lots of deadlocks when running seemingly simple tasks with Case activities, and when I came across this mentioned on StackExchange, I wondered if it might have some bearing.

Hi @gjm coincidentally this seems to have also come up today at https://lab.civicrm.org/dev/core/-/issues/4220. There may still be some code creating revisions. Looking into it.

ALTER TABLE civicrm_activity DROP CONSTRAINT FK_civicrm_activity_original_id;

The above SQL results in syntax error. Below works for me.

ALTER TABLE `civicrm_activity` DROP FOREIGN KEY `FK_civicrm_activity_original_id`;
ALTER TABLE `civicrm_activity` DROP INDEX `FK_civicrm_activity_original_id`;

@kurund What is the db server/version?

MySQL 5.7.38

Thanks @kurund I've updated above which should work also on mariadb. Also note you don't want to remove the index, it's just the constraint delete cascade part that's the problem (since it means the latest revision will get deleted too). It should be on delete set null.

I've run the migrator which completed without error. Now trying to delete the records:

mysql> delete from civicrm_activity where is_current_revision=0;
ERROR 3008 (HY000): Foreign key cascade delete/update exceeds max depth of 15.

Initial googling isn't very helpful. Anyone else come across this? Ideas?

delete from civicrm_activity where is_current_revision=0 LIMIT 1; try that and see if it runs

Also confirm the "bad" constraint got updated: show create table civicrm_activity and look for the one about original_id. If it says cascade DELETE, then that's problematic. Change the constraint to set null.

While there look for anything else that has cascade delete.

Thanks @Stoob & @DaveD

Here's show create table civicrm_activity:

CREATE TABLE `civicrm_activity` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique  Other Activity ID',
  `source_record_id` int(10) unsigned DEFAULT NULL COMMENT 'Artificial FK to original transaction (e.g. contribution) IF it is not an Activity. Table can be figured out through activity_type_id, and further through component registry.',
  `activity_type_id` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'FK to civicrm_option_value.id, that has to be valid, registered activity type.',
  `subject` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'The subject/purpose/short description of the activity.',
  `activity_date_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time this activity is scheduled to occur. Formerly named scheduled_date_time.',
  `duration` int(10) unsigned DEFAULT NULL COMMENT 'Planned or actual duration of activity expressed in minutes. Conglomerate of former duration_hours and duration_minutes.',
  `location` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Location of the activity (optional, open text).',
  `phone_id` int(10) unsigned DEFAULT NULL COMMENT 'Phone ID of the number called (optional - used if an existing phone number is selected).',
  `phone_number` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Phone number in case the number does not exist in the civicrm_phone table.',
  `details` longtext COLLATE utf8_unicode_ci COMMENT 'Details about the activity (agenda, notes, etc).',
  `status_id` int(10) unsigned DEFAULT NULL COMMENT 'ID of the status this activity is currently in. Foreign key to civicrm_option_value.',
  `priority_id` int(10) unsigned DEFAULT NULL COMMENT 'ID of the priority given to this activity. Foreign key to civicrm_option_value.',
  `parent_id` int(10) unsigned DEFAULT NULL COMMENT 'Parent meeting ID (if this is a follow-up item). This is not currently implemented',
  `is_test` tinyint(4) NOT NULL DEFAULT '0',
  `medium_id` int(10) unsigned DEFAULT NULL COMMENT 'Activity Medium, Implicit FK to civicrm_option_value where option_group = encounter_medium.',
  `is_auto` tinyint(4) NOT NULL DEFAULT '0',
  `relationship_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Relationship ID',
  `is_current_revision` tinyint(4) NOT NULL DEFAULT '1',
  `original_id` int(10) unsigned DEFAULT NULL COMMENT 'Activity ID of the first activity record in versioning chain.',
  `result` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Currently being used to store result id for survey activity, FK to option value.',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `campaign_id` int(10) unsigned DEFAULT NULL COMMENT 'The campaign for which this activity has been triggered.',
  `engagement_level` int(10) unsigned DEFAULT NULL COMMENT 'Assign a specific level of engagement to this activity. Used for tracking constituents in ladder of engagement.',
  `weight` int(11) DEFAULT NULL,
  `is_star` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Activity marked as favorite.',
  `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When was the activity was created.',
  `modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `UI_source_record_id` (`source_record_id`),
  KEY `UI_activity_type_id` (`activity_type_id`),
  KEY `index_is_current_revision` (`is_current_revision`),
  KEY `index_is_deleted` (`is_deleted`),
  KEY `FK_civicrm_activity_phone_id` (`phone_id`),
  KEY `FK_civicrm_activity_parent_id` (`parent_id`),
  KEY `FK_civicrm_activity_relationship_id` (`relationship_id`),
  KEY `FK_civicrm_activity_original_id` (`original_id`),
  KEY `FK_civicrm_activity_campaign_id` (`campaign_id`),
  CONSTRAINT `FK_civicrm_activity_campaign_id` FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_civicrm_activity_original_id` FOREIGN KEY (`original_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_civicrm_activity_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_civicrm_activity_phone_id` FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_civicrm_activity_relationship_id` FOREIGN KEY (`relationship_id`) REFERENCES `civicrm_relationship` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=75731 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

LIMIT 1 worked - so I've continued with various size limits to the point where limit 1 still fails - ie located a record that causes the failure.

That record shows:

mysql> select id, campaign_id, original_id, parent_id, phone_id, relationship_id from civicrm_activity where is_current_revision=0 LIMIT 1;
+-----+-------------+-------------+-----------+----------+-----------------+
| id  | campaign_id | original_id | parent_id | phone_id | relationship_id |
+-----+-------------+-------------+-----------+----------+-----------------+
| 707 |        NULL |         707 |      NULL |     NULL |            NULL |
+-----+-------------+-------------+-----------+----------+-----------------+

The original_id is the same as id which might be cause the cascade problem. So I updated the record to null out the original_id and then tried to delete again but still getting the failure:

mysql> update civicrm_activity set original_id = null where id=707;
mysql> delete from civicrm_activity where id=707;
ERROR 3008 (HY000): Foreign key cascade delete/update exceeds max depth of 15.
mysql> select id, campaign_id, original_id, parent_id, phone_id, relationship_id from civicrm_activity where id=707;
+-----+-------------+-------------+-----------+----------+-----------------+
| id  | campaign_id | original_id | parent_id | phone_id | relationship_id |
+-----+-------------+-------------+-----------+----------+-----------------+
| 707 |        NULL |        NULL |      NULL |     NULL |            NULL |
+-----+-------------+-------------+-----------+----------+-----------------+
1 row in set (0.00 sec)

Logging might play a part. I'll try disabling logging next (later).

In my remaining 16353 records, 2177 have id = original_id

Thought: What about activity_contacts? Does that record have more than 15? That's not really nested though so I doubt that's it.

Also are there any activities that have 707 as their parent_id? (which is not related to revisioning but has a cascade delete - possibly that's bad a constraint too). I could see a situation where somebody used the "schedule followup" on each successive activity, leading to a chain of 15.

Just 2 activity contacts for 707 and 1 parent activity.

Viewed through UI there is nothing unusual, but it is part of a case.

I've also tried disabling logging and deleting that one record but still the same cascade error.

The activity that has parent_id=707 - are there any activities that have THAT activity for parent_id? If there's a long sequence like that where each one then has another "child", that might explain it.

But I think that parent_id constraint has always been dangerous too. I'm surprised that hasn't come up and will make a separate ticket. But one thing you can try is changing the ON DELETE CASCADE for the parent_id constraint to be ON DELETE SET NULL.

Yes, that's it. There is a chain of 30 connected via parent_id. DELETE CASCADE here looks potentially disastrous.

Have made https://lab.civicrm.org/dev/core/-/issues/4451.

Maybe we should update the main body of this snippet to suggest only deleting where parent_id is null, until the fix for 4451 is implemented?

Makes sense - will do. I'm planning to try to get the fix into 5.64, and I should probably do a blog post.

Updated.

parent_id is null doesn't work because it's some other activity that has that.parent_id = this.id, so it's doable as a join but since they're doing manual queries at this point anyway have just referred to the other snippet so they can fix the constraint now.