From 37e9088ebe89754ea4e72bd596b343ff72b3a98c Mon Sep 17 00:00:00 2001
From: Tim Otten <totten@civicrm.org>
Date: Tue, 26 Sep 2023 01:04:06 -0700
Subject: [PATCH] FiveSixtySeven - Add pagination for event-queue backfill

---
 .../Incremental/php/FiveSixtySeven.php        | 31 ++++++++++++++++---
 1 file changed, 26 insertions(+), 5 deletions(-)

diff --git a/CRM/Upgrade/Incremental/php/FiveSixtySeven.php b/CRM/Upgrade/Incremental/php/FiveSixtySeven.php
index 0cb680c709..a620d3b41b 100644
--- a/CRM/Upgrade/Incremental/php/FiveSixtySeven.php
+++ b/CRM/Upgrade/Incremental/php/FiveSixtySeven.php
@@ -21,6 +21,8 @@
  */
 class CRM_Upgrade_Incremental_php_FiveSixtySeven extends CRM_Upgrade_Incremental_Base {
 
+  const MAILING_BATCH_SIZE = 500000;
+
   public function setPreUpgradeMessage(&$preUpgradeMessage, $rev, $currentVer = NULL) {
     parent::setPreUpgradeMessage($preUpgradeMessage, $rev, $currentVer);
     if ($rev === '5.67.alpha1') {
@@ -85,11 +87,16 @@ class CRM_Upgrade_Incremental_php_FiveSixtySeven extends CRM_Upgrade_Incremental
       FOREIGN KEY (`job_id`)
       REFERENCES `civicrm_mailing_job`(`id`) ON DELETE SET NULL
     ', [], FALSE, FALSE, FALSE, FALSE);
-      CRM_Core_DAO::executeQuery('
-UPDATE  civicrm_mailing_event_queue q
-INNER JOIN civicrm_mailing_job job ON job.id = q.job_id
-SET q.mailing_id = job.mailing_id, q.is_test=job.is_test
-WHERE q.mailing_id IS NULL');
+
+      [$minId, $maxId] = CRM_Core_DAO::executeQuery("SELECT coalesce(min(id),0), coalesce(max(id),0)
+      FROM civicrm_mailing_event_queue ")->getDatabaseResult()->fetchRow();
+      for ($startId = $minId; $startId <= $maxId; $startId += self::MAILING_BATCH_SIZE) {
+        $endId = min($maxId, $startId + self::MAILING_BATCH_SIZE - 1);
+        $task = new CRM_Queue_Task([static::class, 'fillMailingEvents'],
+          [$startId, $endId],
+          sprintf('Backfill civicrm_mailing_event_queue (%d => %d)', $startId, $endId));
+        $ctx->queue->createItem($task, ['weight' => -1]);
+      }
     }
     catch (\Civi\Core\Exception\DBQueryException $e) {
       throw new CRM_Core_Exception(
@@ -108,6 +115,20 @@ WHERE q.mailing_id IS NULL');
     return TRUE;
   }
 
+  public static function fillMailingEvents(CRM_Queue_TaskContext $ctx, int $startId, int $endId): bool {
+    CRM_Core_DAO::executeQuery('
+UPDATE  civicrm_mailing_event_queue q
+INNER JOIN civicrm_mailing_job job ON job.id = q.job_id
+SET q.mailing_id = job.mailing_id, q.is_test=job.is_test
+WHERE q.id >= %1 AND q.id <= %2 AND q.mailing_id IS NULL',
+      [
+        1 => [$startId, 'Int'],
+        2 => [$endId, 'Int'],
+      ]
+    );
+    return TRUE;
+  }
+
   public static function addNoteNote(CRM_Queue_TaskContext $ctx): bool {
     CRM_Core_BAO_OptionValue::ensureOptionValueExists([
       'option_group_id' => 'note_used_for',
-- 
2.25.1