Remove ORDER BY from batch expunge

winterhazel · winterhazel · commit 02d8f18809db · 2026-01-27T17:48:10.000-03:00
diff --git a/engine/schema/src/main/java/com/cloud/usage/dao/UsageDaoImpl.java b/engine/schema/src/main/java/com/cloud/usage/dao/UsageDaoImpl.java
@@ -549,9 +549,14 @@ public void expungeAllOlderThan(int days, long limitPerQuery) {
         Date limit = DateUtils.addDays(new Date(), -days);
         sc.setParameters("endDate", limit);
 
-        logger.debug("Removing all cloud_usage records older than [{}].", limit);
-        int totalRemoved = Transaction.execute(TransactionLegacy.USAGE_DB, (TransactionCallback<Integer>) status -> batchExpunge(sc, limitPerQuery));
-        logger.info("Removed a total of [{}] cloud_usage records older than [{}].", totalRemoved, limit);
+        TransactionLegacy txn = TransactionLegacy.open(TransactionLegacy.USAGE_DB);
+        try {
+            logger.debug("Removing all cloud_usage records older than [{}].", limit);
+            int totalExpunged = batchExpunge(sc, limitPerQuery);
+            logger.info("Removed a total of [{}] cloud_usage records older than [{}].", totalExpunged, limit);
+        } finally {
+            txn.close();
+        }
     }
 
     public UsageVO persistUsage(final UsageVO usage) {
diff --git a/framework/db/src/main/java/com/cloud/utils/db/Filter.java b/framework/db/src/main/java/com/cloud/utils/db/Filter.java
@@ -57,7 +57,8 @@ public Filter(Class<?> clazz, String field, boolean ascending) {
     }
 
     public Filter(long limit) {
-        _orderBy = " ORDER BY RAND() LIMIT " + limit;
+        _orderBy = " ORDER BY RAND()";
+        _limit = limit;
     }
 
     public Filter(Long offset, Long limit) {
diff --git a/framework/db/src/main/java/com/cloud/utils/db/GenericDaoBase.java b/framework/db/src/main/java/com/cloud/utils/db/GenericDaoBase.java
@@ -1161,6 +1161,8 @@ protected void addFilter(final StringBuilder sql, final Filter filter) {
                 if (filter.getLimit() != null) {
                     sql.append(", ").append(filter.getLimit());
                 }
+            } else if (filter.getLimit() != null) {
+                sql.append(" LIMIT ").append(filter.getLimit());
             }
         }
     }
@@ -1322,7 +1324,7 @@ public int batchExpunge(final SearchCriteria<T> sc, final Long batchSize) {
         Filter filter = null;
         final long batchSizeFinal = ObjectUtils.defaultIfNull(batchSize, 0L);
         if (batchSizeFinal > 0) {
-            filter = new Filter(batchSizeFinal);
+            filter = new Filter(null, batchSizeFinal);
         }
         int expunged = 0;
         int currentExpunged = 0;

Original file line number	Diff line number	Diff line change
`@@ -57,7 +57,8 @@ public Filter(Class<?> clazz, String field, boolean ascending) {`
`57`	`57`	`}`
`58`	`58`
`59`	`59`	`public Filter(long limit) {`
`60`		`- _orderBy = " ORDER BY RAND() LIMIT " + limit;`
	`60`	`+ _orderBy = " ORDER BY RAND()";`
	`61`	`+ _limit = limit;`
`61`	`62`	`}`
`62`	`63`
`63`	`64`	`public Filter(Long offset, Long limit) {`
Original file line number	Diff line number	Diff line change
`@@ -1161,6 +1161,8 @@ protected void addFilter(final StringBuilder sql, final Filter filter) {`
`1161`	`1161`	`if (filter.getLimit() != null) {`
`1162`	`1162`	`sql.append(", ").append(filter.getLimit());`
`1163`	`1163`	`}`
	`1164`	`+ } else if (filter.getLimit() != null) {`
	`1165`	`+ sql.append(" LIMIT ").append(filter.getLimit());`
`1164`	`1166`	`}`
`1165`	`1167`	`}`
`1166`	`1168`	`}`
`@@ -1322,7 +1324,7 @@ public int batchExpunge(final SearchCriteria<T> sc, final Long batchSize) {`
`1322`	`1324`	`Filter filter = null;`
`1323`	`1325`	`final long batchSizeFinal = ObjectUtils.defaultIfNull(batchSize, 0L);`
`1324`	`1326`	`if (batchSizeFinal > 0) {`
`1325`		`- filter = new Filter(batchSizeFinal);`
	`1327`	`+ filter = new Filter(null, batchSizeFinal);`
`1326`	`1328`	`}`
`1327`	`1329`	`int expunged = 0;`
`1328`	`1330`	`int currentExpunged = 0;`