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.