Do chuncked job deletionfeat/chunked-mysql-joblist-remove

This is helpful in cases where we are deleting tons jobs at the same
time in a gallera cluster. This doesn't happen often but this can create
issues.

Test plan:

1. Use https://github.com/nextcloud/quota_warning/pull/88
2. Change max to 1
3. Enabled/Disable quota_warning app and see jobs getting sucessfully
   added and removed

Signed-off-by: Carl Schwan <carl@carlschwan.eu>

2 files changed, 22 insertions, 1 deletions

diff --git a/lib/private/BackgroundJob/JobList.php b/lib/private/BackgroundJob/JobList.php
index 67caea62af0..21af79c4686 100644
--- a/lib/private/BackgroundJob/JobList.php
+++ b/lib/private/BackgroundJob/JobList.php
@@ -29,6 +29,7 @@
  */
 namespace OC\BackgroundJob;
 
+use Doctrine\DBAL\Platforms\MySQLPlatform;
 use OCP\AppFramework\QueryException;
 use OCP\AppFramework\Utility\ITimeFactory;
 use OCP\AutoloadNotAllowedException;
@@ -114,7 +115,22 @@ class JobList implements IJobList {
 			$argumentJson = json_encode($argument);
 			$query->andWhere($query->expr()->eq('argument_hash', $query->createNamedParameter(md5($argumentJson))));
 		}
-		$query->execute();
+
+		// Add galera safe delete chunking if using mysql
+		// Stops us hitting wsrep_max_ws_rows when large row counts are deleted
+		if ($this->connection->getDatabasePlatform() instanceof MySQLPlatform) {
+			// Then use chunked delete
+			$max = IQueryBuilder::MAX_ROW_DELETION;
+
+			$query->setMaxResults($max);
+
+			do {
+				$deleted = $query->execute();
+			} while ($deleted === $max);
+		} else {
+			// Dont use chunked delete - let the DB handle the large row count natively
+			$query->execute();
+		}
 	}
 
 	/**
diff --git a/lib/public/DB/QueryBuilder/IQueryBuilder.php b/lib/public/DB/QueryBuilder/IQueryBuilder.php
index 669003246d9..76754f7bf41 100644
--- a/lib/public/DB/QueryBuilder/IQueryBuilder.php
+++ b/lib/public/DB/QueryBuilder/IQueryBuilder.php
@@ -73,6 +73,11 @@ interface IQueryBuilder {
 	 */
 	public const PARAM_STR_ARRAY = Connection::PARAM_STR_ARRAY;
 
+	/**
+	 * @since 24.0.0 Indicates how many rows can be deleted at once with MySQL
+	 * database server.
+	 */
+	public const MAX_ROW_DELETION = 100000;
 
 	/**
 	 * Enable/disable automatic prefixing of table names with the oc_ prefix