From ec94f5499ca6a3412c32568c930a0a7f17f4bc5b Mon Sep 17 00:00:00 2001 From: Anna Larch Date: Wed, 8 May 2024 20:38:51 +0200 Subject: [PATCH] fix(caldav): automatically delete outdated scheduling objects Signed-off-by: Anna Larch --- .../composer/composer/autoload_classmap.php | 2 + .../dav/composer/composer/autoload_static.php | 2 + .../DeleteOutdatedSchedulingObjects.php | 35 ++++++++++++++ apps/dav/lib/CalDAV/CalDavBackend.php | 38 +++++++++++++++ .../lib/Migration/DeleteSchedulingObjects.php | 38 +++++++++++++++ .../Version1004Date20170825134824.php | 1 + .../composer/composer/autoload_classmap.php | 1 + .../composer/composer/autoload_static.php | 1 + apps/settings/lib/AppInfo/Application.php | 2 + .../lib/SetupChecks/SchedulingTableSize.php | 48 +++++++++++++++++++ core/Application.php | 6 +++ lib/private/Repair.php | 2 + 12 files changed, 176 insertions(+) create mode 100644 apps/dav/lib/BackgroundJob/DeleteOutdatedSchedulingObjects.php create mode 100644 apps/dav/lib/Migration/DeleteSchedulingObjects.php create mode 100644 apps/settings/lib/SetupChecks/SchedulingTableSize.php diff --git a/apps/dav/composer/composer/autoload_classmap.php b/apps/dav/composer/composer/autoload_classmap.php index 1aff4d758c7..176f5d32ed0 100644 --- a/apps/dav/composer/composer/autoload_classmap.php +++ b/apps/dav/composer/composer/autoload_classmap.php @@ -16,6 +16,7 @@ return array( 'OCA\\DAV\\BackgroundJob\\CalendarRetentionJob' => $baseDir . '/../lib/BackgroundJob/CalendarRetentionJob.php', 'OCA\\DAV\\BackgroundJob\\CleanupDirectLinksJob' => $baseDir . '/../lib/BackgroundJob/CleanupDirectLinksJob.php', 'OCA\\DAV\\BackgroundJob\\CleanupInvitationTokenJob' => $baseDir . '/../lib/BackgroundJob/CleanupInvitationTokenJob.php', + 'OCA\\DAV\\BackgroundJob\\DeleteOutdatedSchedulingObjects' => $baseDir . '/../lib/BackgroundJob/DeleteOutdatedSchedulingObjects.php', 'OCA\\DAV\\BackgroundJob\\EventReminderJob' => $baseDir . '/../lib/BackgroundJob/EventReminderJob.php', 'OCA\\DAV\\BackgroundJob\\GenerateBirthdayCalendarBackgroundJob' => $baseDir . '/../lib/BackgroundJob/GenerateBirthdayCalendarBackgroundJob.php', 'OCA\\DAV\\BackgroundJob\\OutOfOfficeEventDispatcherJob' => $baseDir . '/../lib/BackgroundJob/OutOfOfficeEventDispatcherJob.php', @@ -288,6 +289,7 @@ return array( 'OCA\\DAV\\Migration\\BuildSocialSearchIndexBackgroundJob' => $baseDir . '/../lib/Migration/BuildSocialSearchIndexBackgroundJob.php', 'OCA\\DAV\\Migration\\CalDAVRemoveEmptyValue' => $baseDir . '/../lib/Migration/CalDAVRemoveEmptyValue.php', 'OCA\\DAV\\Migration\\ChunkCleanup' => $baseDir . '/../lib/Migration/ChunkCleanup.php', + 'OCA\\DAV\\Migration\\DeleteSchedulingObjects' => $baseDir . '/../lib/Migration/DeleteSchedulingObjects.php', 'OCA\\DAV\\Migration\\FixBirthdayCalendarComponent' => $baseDir . '/../lib/Migration/FixBirthdayCalendarComponent.php', 'OCA\\DAV\\Migration\\RefreshWebcalJobRegistrar' => $baseDir . '/../lib/Migration/RefreshWebcalJobRegistrar.php', 'OCA\\DAV\\Migration\\RegenerateBirthdayCalendars' => $baseDir . '/../lib/Migration/RegenerateBirthdayCalendars.php', diff --git a/apps/dav/composer/composer/autoload_static.php b/apps/dav/composer/composer/autoload_static.php index 9850414b8af..b8888ed425e 100644 --- a/apps/dav/composer/composer/autoload_static.php +++ b/apps/dav/composer/composer/autoload_static.php @@ -31,6 +31,7 @@ class ComposerStaticInitDAV 'OCA\\DAV\\BackgroundJob\\CalendarRetentionJob' => __DIR__ . '/..' . '/../lib/BackgroundJob/CalendarRetentionJob.php', 'OCA\\DAV\\BackgroundJob\\CleanupDirectLinksJob' => __DIR__ . '/..' . '/../lib/BackgroundJob/CleanupDirectLinksJob.php', 'OCA\\DAV\\BackgroundJob\\CleanupInvitationTokenJob' => __DIR__ . '/..' . '/../lib/BackgroundJob/CleanupInvitationTokenJob.php', + 'OCA\\DAV\\BackgroundJob\\DeleteOutdatedSchedulingObjects' => __DIR__ . '/..' . '/../lib/BackgroundJob/DeleteOutdatedSchedulingObjects.php', 'OCA\\DAV\\BackgroundJob\\EventReminderJob' => __DIR__ . '/..' . '/../lib/BackgroundJob/EventReminderJob.php', 'OCA\\DAV\\BackgroundJob\\GenerateBirthdayCalendarBackgroundJob' => __DIR__ . '/..' . '/../lib/BackgroundJob/GenerateBirthdayCalendarBackgroundJob.php', 'OCA\\DAV\\BackgroundJob\\OutOfOfficeEventDispatcherJob' => __DIR__ . '/..' . '/../lib/BackgroundJob/OutOfOfficeEventDispatcherJob.php', @@ -303,6 +304,7 @@ class ComposerStaticInitDAV 'OCA\\DAV\\Migration\\BuildSocialSearchIndexBackgroundJob' => __DIR__ . '/..' . '/../lib/Migration/BuildSocialSearchIndexBackgroundJob.php', 'OCA\\DAV\\Migration\\CalDAVRemoveEmptyValue' => __DIR__ . '/..' . '/../lib/Migration/CalDAVRemoveEmptyValue.php', 'OCA\\DAV\\Migration\\ChunkCleanup' => __DIR__ . '/..' . '/../lib/Migration/ChunkCleanup.php', + 'OCA\\DAV\\Migration\\DeleteSchedulingObjects' => __DIR__ . '/..' . '/../lib/Migration/DeleteSchedulingObjects.php', 'OCA\\DAV\\Migration\\FixBirthdayCalendarComponent' => __DIR__ . '/..' . '/../lib/Migration/FixBirthdayCalendarComponent.php', 'OCA\\DAV\\Migration\\RefreshWebcalJobRegistrar' => __DIR__ . '/..' . '/../lib/Migration/RefreshWebcalJobRegistrar.php', 'OCA\\DAV\\Migration\\RegenerateBirthdayCalendars' => __DIR__ . '/..' . '/../lib/Migration/RegenerateBirthdayCalendars.php', diff --git a/apps/dav/lib/BackgroundJob/DeleteOutdatedSchedulingObjects.php b/apps/dav/lib/BackgroundJob/DeleteOutdatedSchedulingObjects.php new file mode 100644 index 00000000000..fa53a8be4f0 --- /dev/null +++ b/apps/dav/lib/BackgroundJob/DeleteOutdatedSchedulingObjects.php @@ -0,0 +1,35 @@ +setInterval(23 * 60 * 60); + $this->setTimeSensitivity(self::TIME_INSENSITIVE); + } + + /** + * @param array $argument + */ + protected function run($argument): void { + $time = $this->time->getTime() - (60 * 60); + $this->calDavBackend->deleteOutdatedSchedulingObjects($time, 50000); + $this->logger->info("Removed outdated scheduling objects"); + } +} diff --git a/apps/dav/lib/CalDAV/CalDavBackend.php b/apps/dav/lib/CalDAV/CalDavBackend.php index 0aa4426c786..af03d57018c 100644 --- a/apps/dav/lib/CalDAV/CalDavBackend.php +++ b/apps/dav/lib/CalDAV/CalDavBackend.php @@ -2738,6 +2738,44 @@ class CalDavBackend extends AbstractBackend implements SyncSupport, Subscription ->executeStatement(); } + /** + * Deletes all scheduling objects last modified before $modifiedBefore from the inbox collection. + * + * @param int $modifiedBefore + * @param int $limit + * @return void + */ + public function deleteOutdatedSchedulingObjects(int $modifiedBefore, int $limit): void { + $query = $this->db->getQueryBuilder(); + $query->select('id') + ->from('schedulingobjects') + ->where($query->expr()->lt('lastmodified', $query->createNamedParameter($modifiedBefore))) + ->setMaxResults($limit); + $result = $query->executeQuery(); + $count = $result->rowCount(); + if($count === 0) { + return; + } + $ids = array_map(static function (array $id) { + return (int)$id[0]; + }, $result->fetchAll(\PDO::FETCH_NUM)); + $result->closeCursor(); + + $numDeleted = 0; + $deleteQuery = $this->db->getQueryBuilder(); + $deleteQuery->delete('schedulingobjects') + ->where($deleteQuery->expr()->in('id', $deleteQuery->createParameter('ids'), IQueryBuilder::PARAM_INT_ARRAY)); + foreach(array_chunk($ids, 1000) as $chunk) { + $deleteQuery->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); + $numDeleted += $deleteQuery->executeStatement(); + } + + if($numDeleted === $limit) { + $this->logger->info("Deleted $limit scheduling objects, continuing with next batch"); + $this->deleteOutdatedSchedulingObjects($modifiedBefore, $limit); + } + } + /** * Creates a new scheduling object. This should land in a users' inbox. * diff --git a/apps/dav/lib/Migration/DeleteSchedulingObjects.php b/apps/dav/lib/Migration/DeleteSchedulingObjects.php new file mode 100644 index 00000000000..3919236788b --- /dev/null +++ b/apps/dav/lib/Migration/DeleteSchedulingObjects.php @@ -0,0 +1,38 @@ +info('Cleaning up old scheduling events'); + $time = $this->time->getTime() - (60 * 60); + $this->calDavBackend->deleteOutdatedSchedulingObjects($time, 50000); + if (!$this->jobList->has(DeleteOutdatedSchedulingObjects::class, null)) { + $output->info('Adding background job to delete old scheduling objects'); + $this->jobList->add(DeleteOutdatedSchedulingObjects::class, null); + } + } +} diff --git a/apps/dav/lib/Migration/Version1004Date20170825134824.php b/apps/dav/lib/Migration/Version1004Date20170825134824.php index a7cbaa78ef2..7321bba62ff 100644 --- a/apps/dav/lib/Migration/Version1004Date20170825134824.php +++ b/apps/dav/lib/Migration/Version1004Date20170825134824.php @@ -383,6 +383,7 @@ class Version1004Date20170825134824 extends SimpleMigrationStep { ]); $table->setPrimaryKey(['id']); $table->addIndex(['principaluri'], 'schedulobj_principuri_index'); + $table->addIndex(['lastmodified'], 'schedulobj_lastmodified_idx'); } if (!$schema->hasTable('cards_properties')) { diff --git a/apps/settings/composer/composer/autoload_classmap.php b/apps/settings/composer/composer/autoload_classmap.php index 17e47f62a7d..6bbb2978a87 100644 --- a/apps/settings/composer/composer/autoload_classmap.php +++ b/apps/settings/composer/composer/autoload_classmap.php @@ -117,6 +117,7 @@ return array( 'OCA\\Settings\\SetupChecks\\PushService' => $baseDir . '/../lib/SetupChecks/PushService.php', 'OCA\\Settings\\SetupChecks\\RandomnessSecure' => $baseDir . '/../lib/SetupChecks/RandomnessSecure.php', 'OCA\\Settings\\SetupChecks\\ReadOnlyConfig' => $baseDir . '/../lib/SetupChecks/ReadOnlyConfig.php', + 'OCA\\Settings\\SetupChecks\\SchedulingTableSize' => $baseDir . '/../lib/SetupChecks/SchedulingTableSize.php', 'OCA\\Settings\\SetupChecks\\SecurityHeaders' => $baseDir . '/../lib/SetupChecks/SecurityHeaders.php', 'OCA\\Settings\\SetupChecks\\SupportedDatabase' => $baseDir . '/../lib/SetupChecks/SupportedDatabase.php', 'OCA\\Settings\\SetupChecks\\SystemIs64bit' => $baseDir . '/../lib/SetupChecks/SystemIs64bit.php', diff --git a/apps/settings/composer/composer/autoload_static.php b/apps/settings/composer/composer/autoload_static.php index 1dccc69b923..df8f985d7ab 100644 --- a/apps/settings/composer/composer/autoload_static.php +++ b/apps/settings/composer/composer/autoload_static.php @@ -132,6 +132,7 @@ class ComposerStaticInitSettings 'OCA\\Settings\\SetupChecks\\PushService' => __DIR__ . '/..' . '/../lib/SetupChecks/PushService.php', 'OCA\\Settings\\SetupChecks\\RandomnessSecure' => __DIR__ . '/..' . '/../lib/SetupChecks/RandomnessSecure.php', 'OCA\\Settings\\SetupChecks\\ReadOnlyConfig' => __DIR__ . '/..' . '/../lib/SetupChecks/ReadOnlyConfig.php', + 'OCA\\Settings\\SetupChecks\\SchedulingTableSize' => __DIR__ . '/..' . '/../lib/SetupChecks/SchedulingTableSize.php', 'OCA\\Settings\\SetupChecks\\SecurityHeaders' => __DIR__ . '/..' . '/../lib/SetupChecks/SecurityHeaders.php', 'OCA\\Settings\\SetupChecks\\SupportedDatabase' => __DIR__ . '/..' . '/../lib/SetupChecks/SupportedDatabase.php', 'OCA\\Settings\\SetupChecks\\SystemIs64bit' => __DIR__ . '/..' . '/../lib/SetupChecks/SystemIs64bit.php', diff --git a/apps/settings/lib/AppInfo/Application.php b/apps/settings/lib/AppInfo/Application.php index 9f7ec3036f4..e64dbfe4e84 100644 --- a/apps/settings/lib/AppInfo/Application.php +++ b/apps/settings/lib/AppInfo/Application.php @@ -86,6 +86,7 @@ use OCA\Settings\SetupChecks\PhpOutputBuffering; use OCA\Settings\SetupChecks\PushService; use OCA\Settings\SetupChecks\RandomnessSecure; use OCA\Settings\SetupChecks\ReadOnlyConfig; +use OCA\Settings\SetupChecks\SchedulingTableSize; use OCA\Settings\SetupChecks\SecurityHeaders; use OCA\Settings\SetupChecks\SupportedDatabase; use OCA\Settings\SetupChecks\SystemIs64bit; @@ -216,6 +217,7 @@ class Application extends App implements IBootstrap { $context->registerSetupCheck(RandomnessSecure::class); $context->registerSetupCheck(ReadOnlyConfig::class); $context->registerSetupCheck(SecurityHeaders::class); + $context->registerSetupCheck(SchedulingTableSize::class); $context->registerSetupCheck(SupportedDatabase::class); $context->registerSetupCheck(SystemIs64bit::class); $context->registerSetupCheck(TempSpaceAvailable::class); diff --git a/apps/settings/lib/SetupChecks/SchedulingTableSize.php b/apps/settings/lib/SetupChecks/SchedulingTableSize.php new file mode 100644 index 00000000000..cf629f5b12c --- /dev/null +++ b/apps/settings/lib/SetupChecks/SchedulingTableSize.php @@ -0,0 +1,48 @@ +l10n->t('Scheduling objects table size'); + } + + public function getCategory(): string { + return 'database'; + } + + public function run(): SetupResult { + $qb = $this->connection->getQueryBuilder(); + $qb->select($qb->func()->count('id')) + ->from('schedulingobjects'); + $query = $qb->executeQuery(); + $count = $query->fetchOne(); + $query->closeCursor(); + + if ($count > 500000) { + return SetupResult::warning( + $this->l10n->t('You have more than 500 000 rows in the scheduling objects table. Please run the expensive repair jobs via occ maintenance:repair --include-expensive') + ); + } + return SetupResult::success( + $this->l10n->t('Scheduling objects table size is within acceptable range.') + ); + } +} diff --git a/core/Application.php b/core/Application.php index a4e915caec2..c25fb4fb6f4 100644 --- a/core/Application.php +++ b/core/Application.php @@ -191,6 +191,12 @@ class Application extends App { ['principaluri'] ); + $event->addMissingIndex( + 'schedulingobjects', + 'schedulobj_lastmodified_idx', + ['lastmodified'] + ); + $event->addMissingIndex( 'properties', 'properties_path_index', diff --git a/lib/private/Repair.php b/lib/private/Repair.php index b1800d6087d..4a9cac9ff7d 100644 --- a/lib/private/Repair.php +++ b/lib/private/Repair.php @@ -81,6 +81,7 @@ use OC\Repair\RepairDavShares; use OC\Repair\RepairInvalidShares; use OC\Repair\RepairMimeTypes; use OC\Template\JSCombiner; +use OCA\DAV\Migration\DeleteSchedulingObjects; use OCP\AppFramework\QueryException; use OCP\AppFramework\Utility\ITimeFactory; use OCP\Collaboration\Resources\IManager; @@ -224,6 +225,7 @@ class Repair implements IOutput { return [ new OldGroupMembershipShares(\OC::$server->getDatabaseConnection(), \OC::$server->getGroupManager()), \OC::$server->get(ValidatePhoneNumber::class), + \OC::$server->get(DeleteSchedulingObjects::class), ]; } -- 2.39.5