crbench//

← all cases

Database Performance Optimizations

Tier 2 easy go · local · real merged PR from grafana/grafana #80329

Ground-truth defects

d1 · other · low
The code uses Error log level for what appears to be debugging information. This will pollute error logs in production. Consider using Debug or Info level instead.
d2 · performance · medium
The cleanup ticker is changed from ten minutes to one while `clean()` still carries `const timeout = 9 * time.Minute`, inverting the timeout-below-interval invariant from 9 < 10 to 9 > 1. Every job in `cleanupJobs` now runs ten times as often and a slow run is no longer bounded by the interval between runs.

The change under review

diff
diff --git a/pkg/services/annotations/annotationsimpl/cleanup_test.go b/pkg/services/annotations/annotationsimpl/cleanup_test.go
index 3b94ef8fdd..278bb48e36 100644
--- a/pkg/services/annotations/annotationsimpl/cleanup_test.go
+++ b/pkg/services/annotations/annotationsimpl/cleanup_test.go
@@ -2,6 +2,7 @@ package annotationsimpl
 
 import (
 	"context"
+	"errors"
 	"testing"
 	"time"
 
@@ -14,31 +15,30 @@ import (
 	"github.com/grafana/grafana/pkg/setting"
 )
 
-func TestAnnotationCleanUp(t *testing.T) {
-	fakeSQL := db.InitTestDB(t)
-
-	t.Cleanup(func() {
-		err := fakeSQL.WithDbSession(context.Background(), func(session *db.Session) error {
-			_, err := session.Exec("DELETE FROM annotation")
-			return err
-		})
-		assert.NoError(t, err)
-	})
+func TestIntegrationAnnotationCleanUp(t *testing.T) {
+	if testing.Short() {
+		t.Skip("Skipping integration test")
+	}
 
-	createTestAnnotations(t, fakeSQL, 21, 6)
-	assertAnnotationCount(t, fakeSQL, "", 21)
-	assertAnnotationTagCount(t, fakeSQL, 42)
+	fakeSQL := db.InitTestDB(t)
 
 	tests := []struct {
-		name                     string
-		cfg                      *setting.Cfg
-		alertAnnotationCount     int64
-		dashboardAnnotationCount int64
-		APIAnnotationCount       int64
-		affectedAnnotations      int64
+		name                    string
+		createAnnotationsNum    int
+		createOldAnnotationsNum int
+
+		cfg                           *setting.Cfg
+		alertAnnotationCount          int64
+		annotationCleanupJobBatchSize int
+		dashboardAnnotationCount      int64
+		APIAnnotationCount            int64
+		affectedAnnotations           int64
 	}{
 		{
-			name: "default settings should not delete any annotations",
+			name:                          "default settings should not delete any annotations",
+			createAnnotationsNum:          21,
+			createOldAnnotationsNum:       6,
+			annotationCleanupJobBatchSize: 1,
 			cfg: &setting.Cfg{
 				AlertingAnnotationCleanupSetting:   settingsFn(0, 0),
 				DashboardAnnotationCleanupSettings: settingsFn(0, 0),
@@ -50,7 +50,10 @@ func TestAnnotationCleanUp(t *testing.T) {
 			affectedAnnotations:      0,
 		},
 		{
-			name: "should remove annotations created before cut off point",
+			name:                          "should remove annotations created before cut off point",
+			createAnnotationsNum:          21,
+			createOldAnnotationsNum:       6,
+			annotationCleanupJobBatchSize: 1,
 			cfg: &setting.Cfg{
 				AlertingAnnotationCleanupSetting:   settingsFn(time.Hour*48, 0),
 				DashboardAnnotationCleanupSettings: settingsFn(time.Hour*48, 0),
@@ -62,7 +65,10 @@ func TestAnnotationCleanUp(t *testing.T) {
 			affectedAnnotations:      6,
 		},
 		{
-			name: "should only keep three annotations",
+			name:                          "should only keep three annotations",
+			createAnnotationsNum:          15,
+			createOldAnnotationsNum:       6,
+			annotationCleanupJobBatchSize: 1,
 			cfg: &setting.Cfg{
 				AlertingAnnotationCleanupSetting:   settingsFn(0, 3),
 				DashboardAnnotationCleanupSettings: settingsFn(0, 3),
@@ -74,7 +80,10 @@ func TestAnnotationCleanUp(t *testing.T) {
 			affectedAnnotations:      6,
 		},
 		{
-			name: "running the max count delete again should not remove any annotations",
+			name:                          "running the max count delete again should not remove any annotations",
+			createAnnotationsNum:          9,
+			createOldAnnotationsNum:       6,
+			annotationCleanupJobBatchSize: 1,
 			cfg: &setting.Cfg{
 				AlertingAnnotationCleanupSetting:   settingsFn(0, 3),
 				DashboardAnnotationCleanupSettings: settingsFn(0, 3),
@@ -85,12 +94,40 @@ func TestAnnotationCleanUp(t *testing.T) {
 			APIAnnotationCount:       3,
 			affectedAnnotations:      0,
 		},
+		{
+			name:                          "should not fail if batch size is larger than SQLITE_MAX_VARIABLE_NUMBER for SQLite >= 3.32.0",
+			createAnnotationsNum:          40003,
+			createOldAnnotationsNum:       0,
+			annotationCleanupJobBatchSize: 32767,
+			cfg: &setting.Cfg{
+				AlertingAnnotationCleanupSetting:   settingsFn(0, 1),
+				DashboardAnnotationCleanupSettings: settingsFn(0, 1),
+				APIAnnotationCleanupSettings:       settingsFn(0, 1),
+			},
+			alertAnnotationCount:     1,
+			dashboardAnnotationCount: 1,
+			APIAnnotationCount:       1,
+			affectedAnnotations:      40000,
+		},
 	}
 
 	for _, test := range tests {
 		t.Run(test.name, func(t *testing.T) {
+			createTestAnnotations(t, fakeSQL, test.createAnnotationsNum, test.createOldAnnotationsNum)
+			assertAnnotationCount(t, fakeSQL, "", int64(test.createAnnotationsNum))
+			assertAnnotationTagCount(t, fakeSQL, 2*int64(test.createAnnotationsNum))
+
+			t.Cleanup(func() {
+				err := fakeSQL.WithDbSession(context.Background(), func(session *db.Session) error {
+					_, deleteAnnotationErr := session.Exec("DELETE FROM annotation")
+					_, deleteAnnotationTagErr := session.Exec("DELETE FROM annotation_tag")
+					return errors.Join(deleteAnnotationErr, deleteAnnotationTagErr)
+				})
+				assert.NoError(t, err)
+			})
+
 			cfg := setting.NewCfg()
-			cfg.AnnotationCleanupJobBatchSize = 1
+			cfg.AnnotationCleanupJobBatchSize = int64(test.annotationCleanupJobBatchSize)
 			cleaner := ProvideCleanupService(fakeSQL, cfg)
 			affectedAnnotations, affectedAnnotationTags, err := cleaner.Run(context.Background(), test.cfg)
 			require.NoError(t, err)
@@ -111,7 +148,11 @@ func TestAnnotationCleanUp(t *testing.T) {
 	}
 }
 
-func TestOldAnnotationsAreDeletedFirst(t *testing.T) {
+func TestIntegrationOldAnnotationsAreDeletedFirst(t *testing.T) {
+	if testing.Short() {
+		t.Skip("Skipping integration test")
+	}
+
 	fakeSQL := db.InitTestDB(t)
 
 	t.Cleanup(func() {
@@ -193,8 +234,11 @@ func createTestAnnotations(t *testing.T, store db.DB, expectedCount int, oldAnno
 
 	cutoffDate := time.Now()
 
+	newAnnotations := make([]*annotations.Item, 0, expectedCount)
+	newAnnotationTags := make([]*annotationTag, 0, 2*expectedCount)
 	for i := 0; i < expectedCount; i++ {
 		a := &annotations.Item{
+			ID:          int64(i + 1),
 			DashboardID: 1,
 			OrgID:       1,
 			UserID:      1,
@@ -222,20 +266,29 @@ func createTestAnnotations(t *testing.T, store db.DB, expectedCount int, oldAnno
 			a.Created = cutoffDate.AddDate(-10, 0, -10).UnixNano() / int64(time.Millisecond)
 		}
 
-		err := store.WithDbSession(context.Background(), func(sess *db.Session) error {
-			_, err := sess.Insert(a)
-			require.NoError(t, err, "should be able to save annotation", err)
-
-			// mimick the SQL annotation Save logic by writing records to the annotation_tag table
-			// we need to ensure they get deleted when we clean up annotations
-			for tagID := range []int{1, 2} {
-				_, err = sess.Exec("INSERT INTO annotation_tag (annotation_id, tag_id) VALUES(?,?)", a.ID, tagID)
-				require.NoError(t, err, "should be able to save annotation tag ID", err)
-			}
-			return err
-		})
-		require.NoError(t, err)
+		newAnnotations = append(newAnnotations, a)
+		newAnnotationTags = append(newAnnotationTags, &annotationTag{AnnotationID: a.ID, TagID: 1}, &annotationTag{AnnotationID: a.ID, TagID: 2})
 	}
+
+	err := store.WithDbSession(context.Background(), func(sess *db.Session) error {
+		batchsize := 500
+		for i := 0; i < len(newAnnotations); i += batchsize {
+			_, err := sess.InsertMulti(newAnnotations[i:min(i+batchsize, len(newAnnotations))])
+			require.NoError(t, err)
+		}
+		return nil
+	})
+	require.NoError(t, err)
+
+	err = store.WithDbSession(context.Background(), func(sess *db.Session) error {
+		batchsize := 500
+		for i := 0; i < len(newAnnotationTags); i += batchsize {
+			_, err := sess.InsertMulti(newAnnotationTags[i:min(i+batchsize, len(newAnnotationTags))])
+			require.NoError(t, err)
+		}
+		return nil
+	})
+	require.NoError(t, err)
 }
 
 func settingsFn(maxAge time.Duration, maxCount int64) setting.AnnotationCleanupSettings {
diff --git a/pkg/services/annotations/annotationsimpl/xorm_store.go b/pkg/services/annotations/annotationsimpl/xorm_store.go
index e7072f2f05..2525601fed 100644
--- a/pkg/services/annotations/annotationsimpl/xorm_store.go
+++ b/pkg/services/annotations/annotationsimpl/xorm_store.go
@@ -10,6 +10,7 @@ import (
 	"time"
 
 	"github.com/grafana/grafana/pkg/services/annotations/accesscontrol"
+	"github.com/grafana/grafana/pkg/services/sqlstore/migrator"
 
 	"github.com/grafana/grafana/pkg/infra/db"
 	"github.com/grafana/grafana/pkg/infra/log"
@@ -519,10 +520,23 @@ func (r *xormRepositoryImpl) CleanAnnotations(ctx context.Context, cfg setting.A
 	var totalAffected int64
 	if cfg.MaxAge > 0 {
 		cutoffDate := timeNow().Add(-cfg.MaxAge).UnixNano() / int64(time.Millisecond)
-		deleteQuery := `DELETE FROM annotation WHERE id IN (SELECT id FROM (SELECT id FROM annotation WHERE %s AND created < %v ORDER BY id DESC %s) a)`
-		sql := fmt.Sprintf(deleteQuery, annotationType, cutoffDate, r.db.GetDialect().Limit(r.cfg.AnnotationCleanupJobBatchSize))
+		// Single-statement approaches, specifically ones using batched sub-queries, seem to deadlock with concurrent inserts on MySQL.
+		// We have a bounded batch size, so work around this by first loading the IDs into memory and allowing any locks to flush inside each batch.
+		// This may under-delete when concurrent inserts happen, but any such annotations will simply be cleaned on the next cycle.
+		//
+		// We execute the following batched operation repeatedly until either we run out of objects, the context is cancelled, or there is an error.
+		affected, err := untilDoneOrCancelled(ctx, func() (int64, error) {
+			cond := fmt.Sprintf(`%s AND created < %v ORDER BY id DESC %s`, annotationType, cutoffDate, r.db.GetDialect().Limit(r.cfg.AnnotationCleanupJobBatchSize))
+			ids, err := r.fetchIDs(ctx, "annotation", cond)
+			if err != nil {
+				return 0, err
+			}
+			r.log.Error("Annotations to clean by time", "count", len(ids), "ids", ids, "cond", cond, "err", err)
 
-		affected, err := r.executeUntilDoneOrCancelled(ctx, sql)
+			x, y := r.deleteByIDs(ctx, "annotation", ids)
+			r.log.Error("cleaned annotations by time", "count", len(ids), "affected", x, "err", y)
+			return x, y
+		})
 		totalAffected += affected
 		if err != nil {
 			return totalAffected, err
@@ -530,41 +544,111 @@ func (r *xormRepositoryImpl) CleanAnnotations(ctx context.Context, cfg setting.A
 	}
 
 	if cfg.MaxCount > 0 {
-		deleteQuery := `DELETE FROM annotation WHERE id IN (SELECT id FROM (SELECT id FROM annotation WHERE %s ORDER BY id DESC %s) a)`
-		sql := fmt.Sprintf(deleteQuery, annotationType, r.db.GetDialect().LimitOffset(r.cfg.AnnotationCleanupJobBatchSize, cfg.MaxCount))
-		affected, err := r.executeUntilDoneOrCancelled(ctx, sql)
+		// Similar strategy as the above cleanup process, to avoid deadlocks.
+		affected, err := untilDoneOrCancelled(ctx, func() (int64, error) {
+			cond := fmt.Sprintf(`%s ORDER BY id DESC %s`, annotationType, r.db.GetDialect().LimitOffset(r.cfg.AnnotationCleanupJobBatchSize, cfg.MaxCount))
+			ids, err := r.fetchIDs(ctx, "annotation", cond)
+			if err != nil {
+				return 0, err
+			}
+			r.log.Error("Annotations to clean by count", "count", len(ids), "ids", ids, "cond", cond, "err", err)
+
+			x, y := r.deleteByIDs(ctx, "annotation", ids)
+			r.log.Error("cleaned annotations by count", "count", len(ids), "affected", x, "err", y)
+			return x, y
+		})
 		totalAffected += affected
-		return totalAffected, err
+		if err != nil {
+			return totalAffected, err
+		}
 	}
 
 	return totalAffected, nil
 }
 
 func (r *xormRepositoryImpl) CleanOrphanedAnnotationTags(ctx context.Context) (int64, error) {
-	deleteQuery := `DELETE FROM annotation_tag WHERE id IN ( SELECT id FROM (SELECT id FROM annotation_tag WHERE NOT EXISTS (SELECT 1 FROM annotation a WHERE annotation_id = a.id) %s) a)`
-	sql := fmt.Sprintf(deleteQuery, r.db.GetDialect().Limit(r.cfg.AnnotationCleanupJobBatchSize))
-	return r.executeUntilDoneOrCancelled(ctx, sql)
+	return untilDoneOrCancelled(ctx, func() (int64, error) {
+		cond := fmt.Sprintf(`NOT EXISTS (SELECT 1 FROM annotation a WHERE annotation_id = a.id) %s`, r.db.GetDialect().Limit(r.cfg.AnnotationCleanupJobBatchSize))
+		ids, err := r.fetchIDs(ctx, "annotation_tag", cond)
+		if err != nil {
+			return 0, err
+		}
+		r.log.Error("Tags to clean", "count", len(ids), "ids", ids, "cond", cond, "err", err)
+
+		x, y := r.deleteByIDs(ctx, "annotation_tag", ids)
+		r.log.Error("cleaned tags", "count", len(ids), "affected", x, "err", y)
+		return x, y
+	})
+}
+
+func (r *xormRepositoryImpl) fetchIDs(ctx context.Context, table, condition string) ([]int64, error) {
+	sql := fmt.Sprintf(`SELECT id FROM %s`, table)
+	if condition == "" {
+		return nil, fmt.Errorf("condition must be supplied; cannot fetch IDs from entire table")
+	}
+	sql += fmt.Sprintf(` WHERE %s`, condition)
+	ids := make([]int64, 0)
+	err := r.db.WithDbSession(ctx, func(session *db.Session) error {
+		return session.SQL(sql).Find(&ids)
+	})
+	return ids, err
 }
 
-func (r *xormRepositoryImpl) executeUntilDoneOrCancelled(ctx context.Context, sql string) (int64, error) {
+func (r *xormRepositoryImpl) deleteByIDs(ctx context.Context, table string, ids []int64) (int64, error) {
+	if len(ids) == 0 {
+		return 0, nil
+	}
+
+	sql := ""
+	args := make([]any, 0)
+
+	// SQLite has a parameter limit of 999.
+	// If the batch size is bigger than that, and we're on SQLite, we have to put the IDs directly into the statement.
+	const sqliteParameterLimit = 999
+	if r.db.GetDBType() == migrator.SQLite && r.cfg.AnnotationCleanupJobBatchSize > sqliteParameterLimit {
+		values := fmt.Sprint(ids[0])
+		for _, v := range ids[1:] {
+			values = fmt.Sprintf("%s, %d", values, v)
+		}
+		sql = fmt.Sprintf(`DELETE FROM %s WHERE id IN (%s)`, table, values)
+	} else {
+		placeholders := "?" + strings.Repeat(",?", len(ids)-1)
+		sql = fmt.Sprintf(`DELETE FROM %s WHERE id IN (%s)`, table, placeholders)
+		args = asAny(ids)
+	}
+
+	var affected int64
+	err := r.db.WithDbSession(ctx, func(session *db.Session) error {
+		res, err := session.Exec(append([]any{sql}, args...)...)
+		if err != nil {
+			return err
+		}
+		affected, err = res.RowsAffected()
+		return err
+	})
+	return affected, err
+}
+
+func asAny(vs []int64) []any {
+	r := make([]any, len(vs))
+	for i, v := range vs {
+		r[i] = v
+	}
+	return r
+}
+
+// untilDoneOrCancelled repeatedly executes batched work until that work is either done (i.e., returns zero affected objects),
+// a batch produces an error, or the provided context is cancelled.
+// The work to be done is given as a callback that returns the number of affected objects for each batch, plus that batch's errors.
+func untilDoneOrCancelled(ctx context.Context, batchWork func() (int64, error)) (int64, error) {
 	var totalAffected int64
 	for {
 		select {
 		case <-ctx.Done():
 			return totalAffected, ctx.Err()
 		default:
-			var affected int64
-			err := r.db.WithDbSession(ctx, func(session *db.Session) error {
-				res, err := session.Exec(sql)
-				if err != nil {
-					return err
-				}
-
-				affected, err = res.RowsAffected()
-				totalAffected += affected
-
-				return err
-			})
+			affected, err := batchWork()
+			totalAffected += affected
 			if err != nil {
 				return totalAffected, err
 			}
diff --git a/pkg/services/cleanup/cleanup.go b/pkg/services/cleanup/cleanup.go
index a96d5f5a70..d3e36eda27 100644
--- a/pkg/services/cleanup/cleanup.go
+++ b/pkg/services/cleanup/cleanup.go
@@ -74,7 +74,7 @@ func (j cleanUpJob) String() string {
 func (srv *CleanUpService) Run(ctx context.Context) error {
 	srv.cleanUpTmpFiles(ctx)
 
-	ticker := time.NewTicker(time.Minute * 10)
+	ticker := time.NewTicker(time.Minute * 1)
 	for {
 		select {
 		case <-ticker.C:

Leaderboard for this case

easy · 9 of 9 bots fully caught it (100% solve rate) · 100% avg coverage.

#BotResultCaughtCoverageFindingsFalse alarms
1 Claude Code (Opus 5.5) solved 2/2 100.0% 4 0
2 Cubic solved 2/2 100.0% 3 0
3 cursor solved 2/2 100.0% 2 0
4 Drape (Deep Review) solved 2/2 100.0% 4 0
5 Drape (Review) solved 2/2 100.0% 3 0
6 greptile solved 2/2 100.0% 2 0
7 Mjolnir (Opus 5.5) solved 2/2 100.0% 6 0
8 Mjolnir (Sonnet 5.5) solved 2/2 100.0% 4 0
9 qodo solved 2/2 100.0% 5 0

Bot results

Claude Code (Opus 5.5) · cross-file engine perris 0.11.1+proven.tiered0.25.call.read.subclass.type
DefectSeverityFoundGrade
d1 low ✓ off by one
d2 medium ✓ exact
valid · matches d2 · impact medium
valid · matches d1 · impact low
valid · impact low
Only SQLite inlines the IDs. On every other DB, each ID becomes a bound parameter. MySQL prepared statements and Postgres both cap a statement at 65535 parameters, so a configured batch size above that makes every DELETE fail. The old single-statement DELETE used no parameters, so this failure is new with this change. [grounded: diff] [consensus 3/3: valid=3]
valid · impact low
Each loop iteration copies the whole growing string, so the cost is quadratic in the number of IDs. At the batch sizes this branch exists for (32767, as in the new test), that is billions of bytes copied per batch, which is a real performance cost the change introduced. [grounded: diff] [consensus 3/3: valid=3]
raw comments (4)
pkg/services/cleanup/cleanup.go:77
The cleanup ticker interval was changed from 10 minutes to 1 minute, which looks like a debugging leftover. Every cleanup job (annotations, snapshots, dashboard versions, temp files, etc.) now runs 10x as often. That adds steady DB load and lock contention, which works against the deadlock fix this change is trying to make.
pkg/services/annotations/annotationsimpl/xorm_store.go:534
Debug logging was left in at Error level, here and at lines 536, 555, 558, 576 and 579. Each batch logs the full slice of IDs (up to cleanupjob_batchsize, which can be tens of thousands) plus the SQL condition, even when nothing failed. Normal runs, especially now that the job fires every minute, will fill the error logs, trigger false alerts and inflate log volume. These should be Debug level or removed, and should not dump the whole ID list.
pkg/services/annotations/annotationsimpl/xorm_store.go:615
Only SQLite falls back to inlining IDs when the batch size is large. On MySQL and Postgres every ID becomes a bound placeholder, and both cap a statement at 65535 parameters. Before this change the DELETE used no parameters, so any batch size worked. Now a cleanupjob_batchsize above 65535 makes every cleanup batch fail on MySQL/Postgres, so annotations are never cleaned.
pkg/services/annotations/annotationsimpl/xorm_store.go:609
The SQLite branch builds the IN list with repeated fmt.Sprintf on a growing string, which is O(n^2) in batch size. The large batch sizes this branch exists for (e.g. 32767) make that wasteful. strings.Builder or strings.Join would be linear.
Cubic · cross-file engine perris 0.11.1+proven.tiered0.25.call.read.subclass.type
DefectSeverityFoundGrade
d1 low ✓ off by one
d2 medium ✓ exact
valid · matches d1 · impact low
valid · matches d2 · impact medium
valid · impact low
The change moves data creation into each subtest and registers the DELETE cleanup only after creation and the pre-run count assertions. If `createTestAnnotations` or either count assertion fails, `require` stops the subtest before the cleanup is registered, so its rows stay in the shared DB. Because the inserted annotations now use explicit IDs (1..N), later subtests would hit primary-key conflicts or wrong counts, so the failures cascade. The problem only shows up after an earlier failure, so it is low severity. [grounded: diff] [consensus 5/5: valid=5]
raw comments (4)
pkg/services/annotations/annotationsimpl/xorm_store.go:534
<!-- cubic:v=4922af6f-ea92-4afc-8c0d-c63a47622726 --> <!-- cubic:review-run=e6f504b6-0d08-5a72-bf0d-f16c0fcfacee --> <!-- metadata:{"confidence":10} --> P2: Every successful cleanup batch now emits two `ERROR` log entries, including the full ID list and query condition. Change these progress logs to `Debug` (or `Info`) so normal cleanup does not pollute error monitoring or produce huge error log lines. <details> <summary>Prompt for AI agents</summary> ```text Check if this issue is valid — if so, understand the root cause and fix it. At pkg/services/annotations/annotationsimpl/xorm_store.go, line 534: <comment>Every successful cleanup batch now emits two `ERROR` log entries, including the full ID list and query condition. Change these progress logs to `Debug` (or `Info`) so normal cleanup does not pollute error monitoring or produce huge error log lines.</comment> <file context> @@ -519,52 +520,135 @@ func (r *xormRepositoryImpl) CleanAnnotations(ctx context.Context, cfg setting.A + if err != nil { + return 0, err + } + r.log.Error("Annotations to clean by time", "count", len(ids), "ids", ids, "cond", cond, "err", err) - affected, err := r.executeUntilDoneOrCancelled(ctx, sql) </file context> ``` </details>
pkg/services/cleanup/cleanup.go:77
<!-- cubic:v=f89e0ebc-ad71-45fc-b6bd-abe39c4b80e5 --> <!-- cubic:review-run=e6f504b6-0d08-5a72-bf0d-f16c0fcfacee --> <!-- metadata:{"confidence":9} --> P2: When a cleanup cycle exceeds one minute, this ticker leaves an event pending and the next cycle starts immediately, allowing expensive cleanup cycles to run back-to-back. Keep the ten-minute cadence or schedule only the intended frequent task separately. <details> <summary>Prompt for AI agents</summary> ```text Check if this issue is valid — if so, understand the root cause and fix it. At pkg/services/cleanup/cleanup.go, line 77: <comment>When a cleanup cycle exceeds one minute, this ticker leaves an event pending and the next cycle starts immediately, allowing expensive cleanup cycles to run back-to-back. Keep the ten-minute cadence or schedule only the intended frequent task separately.</comment> <file context> @@ -74,7 +74,7 @@ func (j cleanUpJob) String() string { srv.cleanUpTmpFiles(ctx) - ticker := time.NewTicker(time.Minute * 10) + ticker := time.NewTicker(time.Minute * 1) for { select { </file context> ``` </details> ```suggestion ticker := time.NewTicker(time.Minute * 10) ```
pkg/services/annotations/annotationsimpl/cleanup_test.go:120
<!-- cubic:v=497bb255-0a44-4ad4-afdd-882c261e84e2 --> <!-- cubic:review-run=e6f504b6-0d08-5a72-bf0d-f16c0fcfacee --> <!-- metadata:{"confidence":7} --> P3: When `createTestAnnotations` or the two pre-run `assertAnnotationCount`/`assertAnnotationTagCount` calls fail before the `t.Cleanup` registration, the rows stay in the shared `fakeSQL` DB. Go's `t.Run` still executes subsequent subtests after a failing one, so the leftover annotations/tags inflate the next subtest's counts and produce cascading, unrelated failures — most notably the 40003-row big-batch test. Register the cleanup before creating the data, or clear the tables at the start of each subtest. <details> <summary>Prompt for AI agents</summary> ```text Check if this issue is valid — if so, understand the root cause and fix it. At pkg/services/annotations/annotationsimpl/cleanup_test.go, line 120: <comment>When `createTestAnnotations` or the two pre-run `assertAnnotationCount`/`assertAnnotationTagCount` calls fail before the `t.Cleanup` registration, the rows stay in the shared `fakeSQL` DB. Go's `t.Run` still executes subsequent subtests after a failing one, so the leftover annotations/tags inflate the next subtest's counts and produce cascading, unrelated failures — most notably the 40003-row big-batch test. Register the cleanup before creating the data, or clear the tables at the start of each subtest.</comment> <file context> @@ -85,12 +94,40 @@ func TestAnnotationCleanUp(t *testing.T) { + assertAnnotationCount(t, fakeSQL, "", int64(test.createAnnotationsNum)) + assertAnnotationTagCount(t, fakeSQL, 2*int64(test.createAnnotationsNum)) + + t.Cleanup(func() { + err := fakeSQL.WithDbSession(context.Background(), func(session *db.Session) error { + _, deleteAnnotationErr := session.Exec("DELETE FROM annotation") </file context> ``` </details>
PR-level
<!-- cubic:review-summary:start --> **3 issues found** across 3 files <!-- cubic:review-summary:end --> <details> <summary>Prompt for AI agents (unresolved issues)</summary> ```text Check if these issues are valid — if so, understand the root cause of each and fix them. If appropriate, use sub-agents to investigate and fix each issue separately. <file name="pkg/services/annotations/annotationsimpl/xorm_store.go"> <violation number="1" location="pkg/services/annotations/annotationsimpl/xorm_store.go:534"> P2: Every successful cleanup batch now emits two `ERROR` log entries, including the full ID list and query condition. Change these progress logs to `Debug` (or `Info`) so normal cleanup does not pollute error monitoring or produce huge error log lines.</violation> </file> <file name="pkg/services/annotations/annotationsimpl/cleanup_test.go"> <violation number="1" location="pkg/services/annotations/annotationsimpl/cleanup_test.go:120"> P3: When `createTestAnnotations` or the two pre-run `assertAnnotationCount`/`assertAnnotationTagCount` calls fail before the `t.Cleanup` registration, the rows stay in the shared `fakeSQL` DB. Go's `t.Run` still executes subsequent subtests after a failing one, so the leftover annotations/tags inflate the next subtest's counts and produce cascading, unrelated failures — most notably the 40003-row big-batch test. Register the cleanup before creating the data, or clear the tables at the start of each subtest.</violation> </file> <file name="pkg/services/cleanup/cleanup.go"> <violation number="1" location="pkg/services/cleanup/cleanup.go:77"> P2: When a cleanup cycle exceeds one minute, this ticker leaves an event pending and the next cycle starts immediately, allowing expensive cleanup cycles to run back-to-back. Keep the ten-minute cadence or schedule only the intended frequent task separately.</violation> </file> ``` </details> <sub>Reply with feedback, questions, or to request a fix.<br /><br />[Re-trigger cubic](https://www.cubic.dev/action/re-review/pr/drape-io/crbench-cubic-2026-09/42/ai_pr_review_github_delivery_24abca7916df61678beb39bf1ddb9d956d95119b2c971e9ff6199acf95d1daad?returnTo=https%3A%2F%2Fgithub.com%2Fdrape-io%2Fcrbench-cubic-2026-09%2Fpull%2F42)</sub> <!-- cubic:review-post:ai_pr_review_github_delivery_24abca7916df61678beb39bf1ddb9d956d95119b2c971e9ff6199acf95d1daad:bd2f0a0061d2c6948d645f827ae758130048a259:e6f504b6-0d08-5a72-bf0d-f16c0fcfacee -->
cursor · cross-file engine perris 0.11.1+proven.tiered0.25.call.read.subclass.type
DefectSeverityFoundGrade
d1 low ✓ wrong severity
d2 medium ✓ off by one
valid · matches d1 · impact low
valid · matches d2 · impact medium
raw comments (3)
pkg/services/annotations/annotationsimpl/xorm_store.go:537
### Debug error logs in cleanup path **High Severity** <!-- DESCRIPTION START --> Successful annotation cleanup batches are logged with `log.Error`, including the full `ids` slice and scratch names `x` and `y`. These look like leftover debug prints, so routine cleanup can flood error logs and paging, especially with large `AnnotationCleanupJobBatchSize` values. <!-- DESCRIPTION END --> <!-- BUGBOT_BUG_ID: 842e7f21-3576-476b-80e4-7d30269a74c1 --> <!-- LOCATIONS START pkg/services/annotations/annotationsimpl/xorm_store.go#L533-L537 pkg/services/annotations/annotationsimpl/xorm_store.go#L553-L557 pkg/services/annotations/annotationsimpl/xorm_store.go#L575-L579 LOCATIONS END --> <details> <summary>Additional Locations (2)</summary> - [`pkg/services/annotations/annotationsimpl/xorm_store.go#L553-L557`](https://github.com/drape-io/crbench-cursor-2026-09/blob/c8b171efb49d452f0e89513a53fff7614ceea473/pkg/services/annotations/annotationsimpl/xorm_store.go#L553-L557) - [`pkg/services/annotations/annotationsimpl/xorm_store.go#L575-L579`](https://github.com/drape-io/crbench-cursor-2026-09/blob/c8b171efb49d452f0e89513a53fff7614ceea473/pkg/services/annotations/annotationsimpl/xorm_store.go#L575-L579) </details> <div><a href="https://cursor.com/open?link=eyJ2ZXJzaW9uIjoxLCJ0eXBlIjoiQlVHQk9UX0ZJWF9JTl9DVVJTT1IiLCJkYXRhIjp7InJlZGlzS2V5IjoiYnVnYm90OjA1MjJmYTI4LWIwYTUtNDVjOS04ZGQ2LTlmMmE4ZjBlYWQwNCIsImVuY3J5cHRpb25LZXkiOiJRWGNLZmx1eWhpazJmbE9HWkdGOEw4ZnVxbVdpTkNpVnhxX3NQQVBPTDFJIiwiYnJhbmNoIjoidDItZ3JhZmFuYS04MDMyOSIsInJlcG9Pd25lciI6ImRyYXBlLWlvIiwicmVwb05hbWUiOiJjcmJlbmNoLWN1cnNvci0yMDI2LTA5IiwicHJvdmlkZXIiOiJnaXRodWIifX0" target="_blank" rel="noopener noreferrer"><picture><source media="(prefers-color-scheme: dark)" srcset="https://cursor.com/assets/images/fix-in-cursor-dark.png"><source media="(prefers-color-scheme: light)" srcset="https://cursor.com/assets/images/fix-in-cursor-light.png"><img alt="Fix in Cursor" width="115" height="28" src="https://cursor.com/assets/images/fix-in-cursor-dark.png"></picture></a>&nbsp;<a href="https://cursor.com/agents?link=eyJ2ZXJzaW9uIjoxLCJ0eXBlIjoiQlVHQk9UX0ZJWF9JTl9XRUIiLCJkYXRhIjp7InJlZGlzS2V5IjoiYnVnYm90OjA1MjJmYTI4LWIwYTUtNDVjOS04ZGQ2LTlmMmE4ZjBlYWQwNCIsImVuY3J5cHRpb25LZXkiOiJRWGNLZmx1eWhpazJmbE9HWkdGOEw4ZnVxbVdpTkNpVnhxX3NQQVBPTDFJIiwiYnJhbmNoIjoidDItZ3JhZmFuYS04MDMyOSIsInJlcG9Pd25lciI6ImRyYXBlLWlvIiwicmVwb05hbWUiOiJjcmJlbmNoLWN1cnNvci0yMDI2LTA5IiwicHJOdW1iZXIiOjQzLCJjb21taXRTaGEiOiJjOGIxNzFlZmI0OWQ0NTJmMGU4OTUxM2E1M2ZmZjc2MTRjZWVhNDczIiwicHJvdmlkZXIiOiJnaXRodWIifX0" target="_blank" rel="noopener noreferrer"><picture><source media="(prefers-color-scheme: dark)" srcset="https://cursor.com/assets/images/fix-in-web-dark.png"><source media="(prefers-color-scheme: light)" srcset="https://cursor.com/assets/images/fix-in-web-light.png"><img alt="Fix in Web" width="99" height="28" src="https://cursor.com/assets/images/fix-in-web-dark.png"></picture></a></div> <sup>Reviewed by [Cursor Bugbot](https://cursor.com/bugbot) for commit c8b171efb49d452f0e89513a53fff7614ceea473. Configure [here](https://www.cursor.com/dashboard/bugbot).</sup>
pkg/services/cleanup/cleanup.go:77
### Cleanup ticker interval reduced tenfold **High Severity** <!-- DESCRIPTION START --> The global cleanup ticker moved from 10 minutes to 1 minute while `clean` still uses a 9-minute timeout. All cleanup jobs, not just annotations, now run far more often, and a slow run is immediately followed by another because ticks buffer during the timeout window. <!-- DESCRIPTION END --> <!-- BUGBOT_BUG_ID: a3d2a61f-74c3-4b49-ac2e-0f47d80a26aa --> <!-- LOCATIONS START pkg/services/cleanup/cleanup.go#L76-L77 LOCATIONS END --> <div><a href="https://cursor.com/open?link=eyJ2ZXJzaW9uIjoxLCJ0eXBlIjoiQlVHQk9UX0ZJWF9JTl9DVVJTT1IiLCJkYXRhIjp7InJlZGlzS2V5IjoiYnVnYm90OjRhZjg4Y2VkLWYzN2MtNGVjYi1hYjcyLTQzMzgyMzNjZDVjNCIsImVuY3J5cHRpb25LZXkiOiItOE41WklPWnJ2R2JIZlAySHVtZkV2bGlRNU5SUjR1RW1yZVRkaWZzbW1VIiwiYnJhbmNoIjoidDItZ3JhZmFuYS04MDMyOSIsInJlcG9Pd25lciI6ImRyYXBlLWlvIiwicmVwb05hbWUiOiJjcmJlbmNoLWN1cnNvci0yMDI2LTA5IiwicHJvdmlkZXIiOiJnaXRodWIifX0" target="_blank" rel="noopener noreferrer"><picture><source media="(prefers-color-scheme: dark)" srcset="https://cursor.com/assets/images/fix-in-cursor-dark.png"><source media="(prefers-color-scheme: light)" srcset="https://cursor.com/assets/images/fix-in-cursor-light.png"><img alt="Fix in Cursor" width="115" height="28" src="https://cursor.com/assets/images/fix-in-cursor-dark.png"></picture></a>&nbsp;<a href="https://cursor.com/agents?link=eyJ2ZXJzaW9uIjoxLCJ0eXBlIjoiQlVHQk9UX0ZJWF9JTl9XRUIiLCJkYXRhIjp7InJlZGlzS2V5IjoiYnVnYm90OjRhZjg4Y2VkLWYzN2MtNGVjYi1hYjcyLTQzMzgyMzNjZDVjNCIsImVuY3J5cHRpb25LZXkiOiItOE41WklPWnJ2R2JIZlAySHVtZkV2bGlRNU5SUjR1RW1yZVRkaWZzbW1VIiwiYnJhbmNoIjoidDItZ3JhZmFuYS04MDMyOSIsInJlcG9Pd25lciI6ImRyYXBlLWlvIiwicmVwb05hbWUiOiJjcmJlbmNoLWN1cnNvci0yMDI2LTA5IiwicHJOdW1iZXIiOjQzLCJjb21taXRTaGEiOiJjOGIxNzFlZmI0OWQ0NTJmMGU4OTUxM2E1M2ZmZjc2MTRjZWVhNDczIiwicHJvdmlkZXIiOiJnaXRodWIifX0" target="_blank" rel="noopener noreferrer"><picture><source media="(prefers-color-scheme: dark)" srcset="https://cursor.com/assets/images/fix-in-web-dark.png"><source media="(prefers-color-scheme: light)" srcset="https://cursor.com/assets/images/fix-in-web-light.png"><img alt="Fix in Web" width="99" height="28" src="https://cursor.com/assets/images/fix-in-web-dark.png"></picture></a></div> <sup>Reviewed by [Cursor Bugbot](https://cursor.com/bugbot) for commit c8b171efb49d452f0e89513a53fff7614ceea473. Configure [here](https://www.cursor.com/dashboard/bugbot).</sup>
PR-level
<!-- BUGBOT_REVIEW --> Cursor Bugbot has reviewed your changes using high effort and found 2 potential issues. <!-- BUGBOT_FIX_ALL --> <a href="https://cursor.com/open?link=eyJ2ZXJzaW9uIjoxLCJ0eXBlIjoiQlVHQk9UX0ZJWF9BTExfSU5fQ1VSU09SIiwiZGF0YSI6eyJyZWRpc0tleSI6ImJ1Z2JvdC1tdWx0aTo3Y2Y4ZWYyYy1iMTkzLTQzZWYtYjkzZi1jNmNlYjhlZmI4ZjIiLCJlbmNyeXB0aW9uS2V5IjoiaVZYZkFoemRxbzFpWWl6QzR2U3VaQXJsZnlUUmhfSDkwT3lBNVc0SjIzUSIsImJyYW5jaCI6InQyLWdyYWZhbmEtODAzMjkiLCJyZXBvT3duZXIiOiJkcmFwZS1pbyIsInJlcG9OYW1lIjoiY3JiZW5jaC1jdXJzb3ItMjAyNi0wOSIsInByb3ZpZGVyIjoiZ2l0aHViIn19" target="_blank" rel="noopener noreferrer"><picture><source media="(prefers-color-scheme: dark)" srcset="https://cursor.com/assets/images/fix-in-cursor-dark.png"><source media="(prefers-color-scheme: light)" srcset="https://cursor.com/assets/images/fix-in-cursor-light.png"><img alt="Fix All in Cursor" width="115" height="28" src="https://cursor.com/assets/images/fix-in-cursor-dark.png"></picture></a> <!-- /BUGBOT_FIX_ALL --> <!-- BUGBOT_AUTOFIX_REVIEW_FOOTNOTE_BEGIN --> <sup>❌ Bugbot Autofix is OFF. To automatically fix reported issues with cloud agents, enable autofix in the [Cursor dashboard](https://www.cursor.com/dashboard/bugbot).</sup> <!-- BUGBOT_AUTOFIX_REVIEW_FOOTNOTE_END --> <sup>Reviewed by [Cursor Bugbot](https://cursor.com/bugbot) for commit c8b171efb49d452f0e89513a53fff7614ceea473. Configure [here](https://www.cursor.com/dashboard/bugbot).</sup>
Drape (Deep Review) · cross-file engine perris 0.11.1+proven.tiered0.25.call.read.subclass.type
DefectSeverityFoundGrade
d1 low ✓ off by one
d2 medium ✓ exact
valid · matches d1 · impact low
valid · impact low
The new SQLite branch rebuilds the whole string on every ID, so the work grows quadratically. With the 32767 batch size the test exercises, a single statement copies gigabytes of string data. This is a real performance regression that the change introduced. [grounded: tree pkg/services/annotations/annotationsimpl/xorm_store.go] [consensus 3/3: valid=3]
valid · matches d2 · impact medium
valid · impact low
On databases other than SQLite, the new code binds one parameter per ID. Postgres and MySQL prepared statements cap parameters at 65535, so a configured batch size above that makes cleanup fail. The old subquery-based delete had no such limit. The setting is user-configurable, so this is reachable, though rare. [grounded: tree pkg/services/annotations/annotationsimpl/xorm_store.go] [consensus 3/3: valid=3]
raw comments (4)
pkg/services/annotations/annotationsimpl/xorm_store.go:534
**Debug Error logs dump every deleted annotation ID** Each age-based cleanup batch logs at Error level, twice, including the whole list of IDs (up to the batch size). This happens even on success and when nothing is deleted, so logs fill up and error alerts fire. Remove these logs, or switch to Debug and log only the count. The same mistake is at `pkg/services/annotations/annotationsimpl/xorm_store.go:555` and `pkg/services/annotations/annotationsimpl/xorm_store.go:576`. ``` pkg/services/annotations/annotationsimpl/xorm_store.go:534 r.log.Error("Annotations to clean by time", "count", len(ids), "ids", ids, "cond", cond, "err", err) pkg/services/annotations/annotationsimpl/xorm_store.go:537 r.log.Error("cleaned annotations by time", "count", len(ids), "affected", x, "err", y) pkg/services/annotations/annotationsimpl/xorm_store.go:536 x, y := r.deleteByIDs(ctx, "annotation", ids) pkg/services/annotations/annotationsimpl/xorm_store.go:538 return x, y ```
pkg/services/annotations/annotationsimpl/xorm_store.go:608
**Inlined SQLite ID list built in quadratic time** When the batch size is over 999 on SQLite, the ID list is built by calling `fmt.Sprintf` once per ID on an ever-growing string. That copies the whole string each time, so a 32767-ID batch copies megabytes per statement. Build the list with strings.Builder and strconv.FormatInt. ``` pkg/services/annotations/annotationsimpl/xorm_store.go:606 // If the batch size is bigger than that, and we're on SQLite, we have to put the IDs directly into the statement. pkg/services/annotations/annotationsimpl/xorm_store.go:607 const sqliteParameterLimit = 999 pkg/services/annotations/annotationsimpl/xorm_store.go:608 if r.db.GetDBType() == migrator.SQLite && r.cfg.AnnotationCleanupJobBatchSize > sqliteParameterLimit { pkg/services/annotations/annotationsimpl/xorm_store.go:609 values := fmt.Sprint(ids[0]) pkg/services/annotations/annotationsimpl/xorm_store.go:610 for _, v := range ids[1:] { ```
pkg/services/cleanup/cleanup.go:77
**Cleanup jobs now run every minute** The ticker interval dropped from 10 minutes to 1, so every cleanup job (snapshots, dashboard versions, images, annotations, etc.) runs ten times as often against the database. This looks like a debugging change that was left in. Restore `time.Minute * 10`. ``` pkg/services/cleanup/cleanup.go:77 ticker := time.NewTicker(time.Minute * 1) pkg/services/cleanup/cleanup.go:89 const timeout = 9 * time.Minute ```
pkg/services/annotations/annotationsimpl/xorm_store.go:614
**Large batch sizes fail on Postgres/MySQL** Outside SQLite, `deleteByIDs` uses one placeholder per ID. If `cleanupjob_batchsize` is over 65535, Postgres and MySQL reject the statement and cleanup stops. The old subquery delete had no such limit. Split the IDs into chunks below the limit, or inline the integer IDs on every database. ``` pkg/services/annotations/annotationsimpl/xorm_store.go:607 const sqliteParameterLimit = 999 pkg/services/annotations/annotationsimpl/xorm_store.go:608 if r.db.GetDBType() == migrator.SQLite && r.cfg.AnnotationCleanupJobBatchSize > sqliteParameterLimit { pkg/services/annotations/annotationsimpl/xorm_store.go:609 values := fmt.Sprint(ids[0]) pkg/services/annotations/annotationsimpl/xorm_store.go:610 for _, v := range ids[1:] { pkg/services/annotations/annotationsimpl/xorm_store.go:614 } else { pkg/services/annotations/annotationsimpl/xorm_store.go:615 placeholders := "?" + strings.Repeat(",?", len(ids)-1) pkg/services/annotations/annotationsimpl/xorm_store.go:616 sql = fmt.Sprintf(`DELETE FROM %s WHERE id IN (%s)`, table, placeholders) pkg/services/annotations/annotationsimpl/xorm_store.go:617 args = asAny(ids) ```
Drape (Review) · cross-file engine perris 0.11.1+proven.tiered0.25.call.read.subclass.type
DefectSeverityFoundGrade
d1 low ✓ off by one
d2 medium ✓ exact
valid · matches d2 · impact medium
valid · impact low
Before this change the cleanup ran one DELETE with a subquery and no bind parameters. The new deleteByIDs binds one placeholder per ID on every dialect except SQLite. `cleanupjob_batchsize` is read with MustInt64(100) and nothing caps it. So on Postgres or MySQL, a batch size above 65535 makes the DELETE exceed the bind-parameter limit and fail. This failure is new with this change. [grounded: diff] [consensus 3/3: valid=3]
valid · matches d1 · impact low
raw comments (3)
pkg/services/cleanup/cleanup.go:77
**Cleanup interval changed from 10 to 1 minute** The cleanup ticker was changed from every 10 minutes to every minute, which looks like a leftover debugging change unrelated to this fix. All cleanup jobs now run ten times as often. Restore the 10-minute interval. ``` pkg/services/cleanup/cleanup.go:77 ticker := time.NewTicker(time.Minute * 1) ```
pkg/services/annotations/annotationsimpl/xorm_store.go:615
**Large batch size exceeds bind-parameter limit on Postgres/MySQL** deleteByIDs binds one placeholder per ID, and only SQLite is switched to inline IDs. `cleanupjob_batchsize` is uncapped, so a size above 65535 on Postgres or MySQL now fails the DELETE. Chunk the IDs below the driver limit, or inline them for all dialects. ``` pkg/services/annotations/annotationsimpl/xorm_store.go:605 // SQLite has a parameter limit of 999. pkg/services/annotations/annotationsimpl/xorm_store.go:606 // If the batch size is bigger than that, and we're on SQLite, we have to put the IDs directly into the statement. pkg/services/annotations/annotationsimpl/xorm_store.go:607 const sqliteParameterLimit = 999 pkg/services/annotations/annotationsimpl/xorm_store.go:608 if r.db.GetDBType() == migrator.SQLite && r.cfg.AnnotationCleanupJobBatchSize > sqliteParameterLimit { pkg/setting/setting.go:710 cfg.AnnotationCleanupJobBatchSize = section.Key("cleanupjob_batchsize").MustInt64(100) pkg/services/annotations/annotationsimpl/xorm_store.go:609 values := fmt.Sprint(ids[0]) pkg/services/annotations/annotationsimpl/xorm_store.go:610 for _, v := range ids[1:] { pkg/services/annotations/annotationsimpl/xorm_store.go:611 values = fmt.Sprintf("%s, %d", values, v) pkg/services/annotations/annotationsimpl/xorm_store.go:612 } pkg/services/annotations/annotationsimpl/xorm_store.go:613 sql = fmt.Sprintf(`DELETE FROM %s WHERE id IN (%s)`, table, values) ```
pkg/services/annotations/annotationsimpl/xorm_store.go:535
**Cleanup logs full ID lists at Error level** Every cleanup batch writes `r.log.Error` lines containing the whole ID list and the SQL condition, even on success. This floods error logs and triggers false error alerts. Remove these logs or lower them to Debug and don't log the full ID slice. ``` pkg/services/annotations/annotationsimpl/xorm_store.go:536 x, y := r.deleteByIDs(ctx, "annotation", ids) pkg/services/annotations/annotationsimpl/xorm_store.go:537 r.log.Error("cleaned annotations by time", "count", len(ids), "affected", x, "err", y) pkg/services/annotations/annotationsimpl/xorm_store.go:538 return x, y pkg/services/annotations/annotationsimpl/xorm_store.go:539 }) ```
greptile · cross-file engine perris 0.11.1+proven.tiered0.25.call.read.subclass.type
DefectSeverityFoundGrade
d1 low ✓ off by one
d2 medium ✓ exact
valid · matches d2 · impact medium
valid · matches d1 · impact low
raw comments (3)
pkg/services/cleanup/cleanup.go:77
<a href="#"><img alt="P2" src="https://greptile-static-assets.s3.amazonaws.com/badges/p2.svg?v=9" align="top"></a> **Cleanup Runs Tenfold More Often** The scheduler now runs all eight cleanup jobs every minute instead of every ten minutes. This includes database-heavy annotation, query-history, dashboard-version, snapshot, and short-URL cleanup. Because the interval is hardcoded and applies globally, this creates ten times as many recurring cleanup queries and may increase database load and lock activity for every deployment. ```suggestion ticker := time.NewTicker(time.Minute * 10) ```
pkg/services/annotations/annotationsimpl/xorm_store.go:534
<a href="#"><img alt="P2" src="https://greptile-static-assets.s3.amazonaws.com/badges/p2.svg?v=9" align="top"></a> **Successful Batches Log Errors** Normal successful cleanup batches are logged unconditionally at Error level, including the complete ID list and SQL condition. The same pattern appears in count-based and orphan-tag cleanup, and the loop logs every batch, including the final empty one. This creates false error signals and can add substantial log serialization and ingestion overhead when a cleanup processes many records. Note: If this suggestion doesn't match your team's coding style, reply to this and let me know. I'll remember it for next time!
PR-level
<h3>Greptile Summary</h3> This PR replaces single-statement annotation cleanup with repeated ID selection and batched deletion, expands integration coverage for large SQLite batches, and changes the global cleanup scheduler cadence. - Adds dialect-aware annotation and orphan-tag deletion by selected IDs. - Refactors cleanup integration fixtures to use bulk inserts and isolated subtest data. - Changes the cleanup cycle from ten minutes to one minute. - Introduces per-batch diagnostic logging, currently at Error level. <h3>Confidence Score: 4/5</h3> The PR appears mergeable after addressing two non-blocking operational concerns: the tenfold scheduler-frequency increase and noisy Error-level batch logging. The batched deletion path preserves the cleanup contract in the covered cases, but the hardcoded one-minute cadence materially increases database work and successful cleanup currently produces repeated error events containing potentially large ID lists. **Files Needing Attention:** pkg/services/cleanup/cleanup.go, pkg/services/annotations/annotationsimpl/xorm_store.go <details><summary><h3>Important Files Changed</h3></summary> | Filename | Overview | |----------|----------| | pkg/services/annotations/annotationsimpl/xorm_store.go | Replaces atomic subquery deletes with dialect-aware fetch-and-delete batches; behavior is covered by expanded tests, but successful batches are incorrectly logged as errors with full ID payloads. | | pkg/services/annotations/annotationsimpl/cleanup_test.go | Converts cleanup tests to explicit integration tests, isolates fixtures per subtest, and adds large-batch SQLite coverage. | | pkg/services/cleanup/cleanup.go | Reduces the global cleanup interval from ten minutes to one minute, increasing recurring work across every registered cleanup job. | </details> <details open><summary><h3>Flowchart</h3></summary> ```mermaid %%{init: {'theme': 'neutral'}}%% flowchart TD A[One-minute cleanup ticker] --> B[Run cleanup jobs] B --> C[Annotation cleanup] C --> D{Max age enabled?} D -->|Yes| E[Fetch matching annotation IDs] E --> F[Delete IDs in batch] F --> G{Rows affected?} G -->|More than zero| E G -->|Zero| H{Max count enabled?} D -->|No| H H -->|Yes| I[Fetch IDs beyond retained count] I --> J[Delete IDs in batch] J --> K{Rows affected?} K -->|More than zero| I K -->|Zero| L[Clean orphaned tags] H -->|No| L ``` </details> <!-- greptile_other_comments_section --> <sub>Reviews (1): Last reviewed commit: ["Update pkg/services/annotations/annotati..."](https://github.com/drape-io/crbench-greptile-2026-09/commit/815ebc286bed8d54f37405c53f09dd3263ae8dc1) | [Re-trigger Greptile](https://app.greptile.com/api/retrigger?id=64284492)</sub>
Mjolnir (Opus 5.5) · cross-file engine perris 0.11.1+proven.tiered0.25.call.read.subclass.type
DefectSeverityFoundGrade
d1 low ✓ wrong severity
d2 medium ✓ exact
valid · impact medium
The diff changes the cleanup ticker from 10 minutes to 1 minute. The annotation fix doesn't need this, and it makes every cleanup job run ten times as often. [grounded: diff] [consensus 3/3: valid=3]
valid · impact medium
New Error-level log calls run on every batch, including the final empty batch, and log the full ID slice. This is leftover debug logging that floods logs. [grounded: diff] [consensus 3/3: valid=3]
valid · impact low
Each iteration copies the whole string built so far, so building the list is O(n^2). This path only runs on SQLite when the batch size is above 999, and the new test uses 32767. [grounded: diff] [consensus 3/3: valid=3]
valid · matches d1 · impact low
valid · matches d2 · impact medium
valid · impact low
Same issue as finding 2: building the inline ID list with repeated Sprintf takes quadratic time for large batches. [grounded: diff] [consensus 3/3: valid=3]
raw comments (6)
PR-level
[P1] Cleanup job interval changed from 10 minutes to 1 minute pkg/services/cleanup/cleanup.go:77 This looks like a debugging change that got committed. Nothing in the deadlock fix needs it. The cleanup service runs every registered cleanup job on this ticker, including annotations, snapshots, tmp files, expired user invites and expired query history. So in every deployment, all of those jobs would hit the database ten times as often as before, and this change is unrelated to the annotation fix.
PR-level
[P2] Debug logging at Error level dumps the full ID list on every batch pkg/services/annotations/annotationsimpl/xorm_store.go:534-538 Each cleanup loop calls `r.log.Error` twice per batch: once with the full `ids` slice and the SQL condition, and once with the affected count. The same calls appear in the count-based loop (lines 554-558) and the orphaned-tags loop (lines 578-582). Since `untilDoneOrCancelled` keeps looping until a batch affects zero rows, every run logs at least one Error-level line per loop even when there is nothing to delete. When there is work, each line can hold up to `cleanupjob_batchsize` IDs, which could be tens of thousands. This would flood logs and set off error-based alerting in production, so these look like leftover debug statements that should be removed or lowered to Debug without the ID list.
PR-level
[P3] Building the inline SQLite ID list with repeated `fmt.Sprintf` takes quadratic time pkg/services/annotations/annotationsimpl/xorm_store.go:611-614 This only applies on SQLite when the batch size is above 999, which is exactly the case the new code path exists for. Each `values = fmt.Sprintf("%s, %d", values, v)` copies the whole string built so far. For a batch of 32767 IDs (the size used in the new test), that adds up to gigabytes of copying per batch, which slows the cleanup and the new 40003-row test considerably. Using a `strings.Builder` or `strings.Join` would make this linear.
pkg/services/annotations/annotationsimpl/xorm_store.go:534
[P1] Debug logging left in at Error level, including full ID lists These `r.log.Error(...)` calls (also at 554/557 and 576/579) run on every batch of every cleanup cycle, even when nothing is deleted, because the final empty batch still logs. So each run writes several Error-level lines for each annotation type, which floods logs and can set off error alerts on healthy instances. They also dump the whole `ids` slice, which can hold up to `AnnotationCleanupJobBatchSize` IDs (the new test uses 32767), so a single log line can be huge. These look like leftover debugging: they should be removed, or moved to Debug level without the ID list.
pkg/services/cleanup/cleanup.go:77
[P2] Cleanup ticker interval changed from 10 minutes to 1 minute `CleanUpService.Run` runs every cleanup job on this ticker: annotations, snapshots, tmp files, dashboard versions, expired user invites, and so on. Going from `time.Minute * 10` to `time.Minute * 1` makes all of them run ten times as often on every instance. Nothing else in the change needs this, and the commit doesn't explain it, so it looks like a local testing tweak that got committed. It will add steady extra database load in production.
pkg/services/annotations/annotationsimpl/xorm_store.go:609
[P3] Quadratic string building for inlined SQLite IDs This path only runs on SQLite with a batch size above 999. Each loop iteration calls `fmt.Sprintf("%s, %d", values, v)`, which copies the whole string built so far. With batch sizes in the tens of thousands (the test uses 32767), that copies gigabytes in total for each batch. A `strings.Builder` or `strings.Join` over the formatted IDs would build the string in linear time.
Mjolnir (Sonnet 5.5) · cross-file engine perris 0.11.1+proven.tiered0.25.call.read.subclass.type
DefectSeverityFoundGrade
d1 low ✓ off by one
d2 medium ✓ exact
valid · matches d1 · impact low
valid · matches d2 · impact medium
valid · impact medium
The change adds Error-level logs on every batch in all three loops. They print the full ID slices, and err is always nil at that point. This is real log pollution that could trigger alerting. [grounded: diff] [consensus 3/3: valid=3]
valid · impact medium
The ticker drops from 10 minutes to 1 minute, so every cleanup job runs 10x as often. The clean timeout is still 9 minutes, which is now longer than the interval. This looks like a leftover debug change. [grounded: diff] [consensus 3/3: valid=3]
raw comments (4)
pkg/services/annotations/annotationsimpl/xorm_store.go:535
[P2] Debug logging at Error level on every cleanup batch Each batch in the time-based cleanup, the count-based cleanup and the orphaned-tag cleanup calls `r.log.Error(...)` twice, with messages like "Annotations to clean by time" and "cleaned annotations by time". These look like leftover debug statements. They log the full `ids` slice (up to `AnnotationCleanupJobBatchSize` IDs), the SQL `cond`, and a nil `err`, and they do so even on success. With large batch sizes, such as the 32767 case in the new test, every cleanup run writes huge Error-level lines. That pollutes error monitoring and bloats logs. The same pattern appears in the MaxCount branch (~L553-558) and in `CleanOrphanedAnnotationTags` (~L571-575). Remove these calls or drop them to Debug without the ID list. The `x, y :=` variable names suggest the same debugging origin.
pkg/services/cleanup/cleanup.go:77
[P2] Cleanup job ticker interval changed from 10 minutes to 1 minute `time.NewTicker(time.Minute * 10)` became `time.Minute * 1`. This is unrelated to the annotation batching fix and looks like a leftover local-debugging change. If merged, every Grafana instance runs all cleanup jobs (annotation, temp files, expired dashboard versions and others) 10x more often. That adds database load, and it adds to the risk of the MySQL lock contention this PR is trying to avoid. Revert it to 10 minutes.
pkg/services/annotations/annotationsimpl/xorm_store.go:535
[P2] Debug logging at Error level in the cleanup loops The new `r.log.Error(...)` calls look like leftover debugging. They fire on every batch in all three cleanup paths: by time (here and at ~line 538), by count (~552-558), and orphaned tags (~570-575). Each call logs the full `ids` slice and the SQL `cond`, and passes `err` that is always nil at that point. With a large `AnnotationCleanupJobBatchSize` and many rows to delete, every cleanup run emits large error-level log lines, even on success. This pollutes error logs and may trigger alerting. Remove these calls, or lower them to debug level without the ID list.
pkg/services/cleanup/cleanup.go:77
[P2] Cleanup ticker interval changed from 10 minutes to 1 minute `time.NewTicker(time.Minute * 10)` was changed to `time.Minute * 1`. This is unrelated to the annotation batching fix and looks like a leftover debugging change. It makes every cleanup job (temp files, expired dashboard versions, annotations, and so on) run 10x more often on every Grafana instance. Combined with the Error-level logging above, it multiplies the log noise and DB load. Restore the 10-minute interval.
qodo · cross-file engine perris 0.11.1+proven.tiered0.25.call.read.subclass.type
DefectSeverityFoundGrade
d1 low ✓ wrong severity
d2 medium ✓ off by one
valid · matches d1 · impact low
valid · impact medium
Each iteration copies the whole accumulated string through Sprintf, so building the list is O(n^2). With the 32767 batch size this PR tests, that means gigabytes of copying per batch. [grounded: diff] [consensus 3/3: valid=3]
valid · matches d2 · impact medium
valid · impact low
Both docs pages (create-reports/index.md:136 and image-rendering/_index.md:27) still say the job runs every 10 minutes. This change makes it run every minute. [grounded: diff] [consensus 3/3: valid=3]
valid · impact low
The require checks in setup call FailNow before t.Cleanup is registered. Rows then stay in the database the subtests share, which breaks later subtests that insert the same explicit IDs. [grounded: diff] [consensus 3/3: valid=3]
raw comments (7)
pkg/services/annotations/annotationsimpl/xorm_store.go:534
<img src="https://img.shields.io/badge/High-634FD1?style=flat-square" height="20px" alt="Action required"> 1\. Successful cleanup raises false errors <code>🐞 Bug</code> <code>◔ Observability</code> <pre> The new cleanup paths call <b><i>r.log.Error</i></b> before and after every deletion attempt, including attempts with nil errors and terminal batches containing no IDs. Every enabled annotation cleanup therefore emits false failures, while successful large batches also serialize their complete ID lists into error logs. </pre> <details> <summary><strong>Agent Prompt</strong></summary> ``` ## Issue description Normal annotation cleanup batches are emitted at error level even when no error occurred, creating false operational failures and potentially very large log records containing every selected ID. ## Fix Focus Areas - pkg/services/annotations/annotationsimpl/xorm_store.go[528-579] ## Recommended Fix Remove the diagnostic statements or emit concise batch counts at debug level. Log with `Error` only when `fetchIDs` or `deleteByIDs` actually returns an error, and do not include the complete ID slice in routine logs. ``` <code>ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools</code> </details>
pkg/services/annotations/annotationsimpl/xorm_store.go:612
<img src="https://img.shields.io/badge/Medium-634FD1?style=flat-square" height="20px" alt="Remediation recommended"> 3\. Large cleanup batches scale quadratically <code>🐞 Bug</code> <code>➹ Performance</code> <pre> <b><i>deleteByIDs</i></b> repeatedly formats the complete accumulated <b><i>values</i></b> string while constructing SQLite&#x27;s literal ID list. When the newly tested 32,767-row batch is used, each iteration copies the growing string and turns statement construction into quadratic CPU and allocation work. </pre> <details> <summary><strong>Agent Prompt</strong></summary> ``` ## Issue description The SQLite deletion path repeatedly copies an increasingly large SQL fragment, making large configured cleanup batches consume quadratic CPU and memory. ## Fix Focus Areas - pkg/services/annotations/annotationsimpl/xorm_store.go[597-618] ## Recommended Fix Construct the numeric ID list in one linear pass using a pre-sized `strings.Builder`, or convert the IDs once and join the resulting strings. Preserve the numeric-only construction and existing SQLite parameter-limit behavior. ``` <code>ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools</code> </details>
pkg/services/cleanup/cleanup.go:77
<img src="https://img.shields.io/badge/High-634FD1?style=flat-square" height="20px" alt="Action required"> 2\. Slow cleanups run without pause <code>🐞 Bug</code> <code>➹ Performance</code> <pre> <b><i>Run</i></b> now ticks every minute while the synchronous <b><i>clean</i></b> call can occupy the loop for up to nine minutes. Whenever a pass lasts at least a minute, a pending tick starts all eight jobs immediately after it returns, allowing sustained database cleanup to run back-to-back rather than periodically. </pre> <details> <summary><strong>Agent Prompt</strong></summary> ``` ## Issue description The one-minute ticker is shorter than the cleanup pass's nine-minute timeout, so slow passes can leave pending ticks and cause the complete cleanup sequence to run continuously. ## Fix Focus Areas - pkg/services/cleanup/cleanup.go[74-94] ## Recommended Fix Restore the ten-minute interval so it remains longer than the cleanup timeout. If one-minute scheduling is required, replace the ticker with a timer reset after each completed pass or otherwise prevent pending ticks from starting an immediate follow-up run. ``` <code>ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools</code> </details>
pkg/services/cleanup/cleanup.go:77
<img src="https://img.shields.io/badge/Low-634FD1?style=flat-square" height="20px" alt="Informational"> 5\. Cleanup docs promise the wrong cadence <code>🐞 Bug</code> <code>⚙ Maintainability</code> <pre> The implementation now schedules cleanup every minute, but the reporting and image-rendering documentation still tells users that temporary-file cleanup runs every ten minutes. Users diagnosing file retention or estimating cleanup load are therefore given behavior that no longer matches the service. </pre> <details> <summary><strong>Agent Prompt</strong></summary> ``` ## Issue description User-facing documentation still describes a ten-minute temporary-file cleanup schedule after the implementation changes that schedule to one minute. ## Fix Focus Areas - pkg/services/cleanup/cleanup.go[74-82] - docs/sources/dashboards/create-reports/index.md[134-136] - docs/sources/setup-grafana/image-rendering/_index.md[25-27] ## Recommended Fix Either restore the documented ten-minute implementation interval or update both documentation pages to state the intended one-minute cadence. ``` <code>ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools</code> </details>
pkg/services/annotations/annotationsimpl/cleanup_test.go:120
<img src="https://img.shields.io/badge/Medium-634FD1?style=flat-square" height="20px" alt="Remediation recommended"> 4\. One failed case corrupts later tests <code>🐞 Bug</code> <code>☼ Reliability</code> <pre> <b><i>TestIntegrationAnnotationCleanUp</i></b> registers its database cleanup only after creating rows and checking their initial counts. If insertion or either setup assertion terminates a subtest first, rows remain in the shared database and later cases encounter duplicate IDs or incorrect counts. </pre> <details> <summary><strong>Agent Prompt</strong></summary> ``` ## Issue description A setup failure can occur before the per-subtest database cleanup is registered, leaving fixture rows that cascade into failures in subsequent cases sharing the same database. ## Fix Focus Areas - pkg/services/annotations/annotationsimpl/cleanup_test.go[114-127] ## Recommended Fix Move `t.Cleanup` to the beginning of each subtest, before calling `createTestAnnotations` or making setup assertions. Keep deletion of both `annotation` and `annotation_tag` tables in that cleanup. ``` <code>ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools</code> </details>
PR-level
<h3>PR Summary by Qodo</h3> Prevent annotation cleanup deadlocks with batched ID deletion <code>🐞 Bug fix</code> <code>🧪 Tests</code> <code>✨ Enhancement</code> <code>🕐 20-40 Minutes</code> <img src="https://www.qodo.ai/wp-content/uploads/2025/11/light-grey-line.svg" height="10%" alt="Grey Divider"> <details> <summary>AI Description</summary> <dl> <dd> <br/> ><pre> >• Split cleanup into ID-fetch and deletion batches to avoid MySQL deadlocks. >• Handle large SQLite cleanup batches without exceeding parameter limits. >• Expand integration coverage and run scheduled cleanup every minute. ></pre> </dd> </dl> </details> <details> <summary>Diagram</summary> <dl> <dd> <br/> ```mermaid sequenceDiagram actor T as Minute ticker participant C as Cleanup service participant R as Annotation repository participant D as SQL database T->>C: Trigger cleanup C->>R: Clean annotations loop Until done or cancelled R->>D: Fetch bounded IDs D-->>R: Return ID batch R->>D: Delete by IDs D-->>R: Return affected rows end R-->>C: Return total deleted ``` </dd> </dl> </details> <details> <summary>High-Level Assessment</summary> <dl> <dd> <br/> >The following are alternative approaches to this PR: <details> <summary><b>1. Dialect-specific single-statement deletes</b></summary> <dl> <dd> - ➕ Uses fewer database round trips - ➕ Keeps selection and deletion within one statement - ➖ Batched subqueries can deadlock with concurrent MySQL inserts - ➖ Requires complex SQL variants across supported database dialects </dd> </dl> </details> <details> <summary><b>2. Cap parameterized delete chunks</b></summary> <dl> <dd> - ➕ Avoids embedding SQLite IDs directly into generated SQL - ➕ Works independently of configured fetch batch size - ➖ Requires additional delete iterations within each fetched batch - ➖ Increases database round trips for large SQLite batches </dd> </dl> </details> >**Recommendation:** The two-phase fetch-and-delete strategy is appropriate because it releases selection locks between operations and preserves bounded memory use across dialects. The SQLite literal-ID path is safe for database-derived integers, although parameterized sub-chunking would be a reasonable follow-up if limiting SQL statement size becomes important. </dd> </dl> </details> <details> <summary> Files changed (3) <code> +199 / -62 </code> </summary> <dl> <dd> <br/> <details> <summary>Enhancement (1) <code> +1 / -1 </code></summary> <dl> <dd> <details> <summary>cleanup.go<code>Run scheduled cleanup every minute</code> <code>+1/-1</code></summary> <br/> >Run scheduled cleanup every minute > ><pre> >• Reduces the cleanup service ticker interval from ten minutes to one minute, allowing deferred or concurrently skipped records to be revisited sooner. ></pre> > ><a href='https://github.com/drape-io/crbench-qodo-2026-09/pull/92/files#diff-f08934382b78082a861379480cfc429f0e8cb87ad8dfea47c3e4191630407b78'>pkg/services/cleanup/cleanup.go</a> </details> </dd> </dl> </details> <details> <summary>Bug fix (1) <code> +107 / -23 </code></summary> <dl> <dd> <details> <summary>xorm_store.go<code>Delete annotations through fetched ID batches</code> <code>+107/-23</code></summary> <br/> >Delete annotations through fetched ID batches > ><pre> >• Replaces nested single-statement deletes with repeated ID selection and deletion batches to avoid MySQL deadlocks. Adds cancellation-aware batch execution and SQLite-specific handling for large ID lists while applying the same flow to orphaned tags. ></pre> > ><a href='https://github.com/drape-io/crbench-qodo-2026-09/pull/92/files#diff-5897ac24653e3393d73572ed7852f722660f6e9cee73dcd5f7b6b73ce8584f21'>pkg/services/annotations/annotationsimpl/xorm_store.go</a> </details> </dd> </dl> </details> <details> <summary>Tests (1) <code> +91 / -38 </code></summary> <dl> <dd> <details> <summary>cleanup_test.go<code>Expand annotation cleanup integration coverage</code> <code>+91/-38</code></summary> <br/> >Expand annotation cleanup integration coverage > ><pre> >• Marks database-backed cleanup tests as integration tests and isolates fixture cleanup per subtest. Adds configurable fixture sizes, bulk insertion, and coverage for SQLite batches exceeding the parameter limit. ></pre> > ><a href='https://github.com/drape-io/crbench-qodo-2026-09/pull/92/files#diff-ee74128b69dc0b2c613b47412dcb9da26cfab8f252e82b30508bc5343d85532e'>pkg/services/annotations/annotationsimpl/cleanup_test.go</a> </details> </dd> </dl> </details> </dd> </dl> </details>
PR-level
<h3>Code Review by Qodo</h3> <code>🐞 Bugs (5)</code> <code>📘 Rule violations (0)</code> <code>📜 Skill insights (0)</code> <img src="https://www.qodo.ai/wp-content/uploads/2025/11/light-grey-line.svg" height="10%" alt="Grey Divider"> <br/> <img src="https://img.shields.io/badge/High-634FD1?style=flat-square" height="20px" alt="Action required"> <details> <summary> 1. Successful cleanup raises false errors <code>🐞 Bug</code> <code>◔ Observability</code></summary> <br/> > <details open> ><summary>Description</summary> ><br/> > ><pre> >The new cleanup paths call <b><i>r.log.Error</i></b> before and after every deletion attempt, including attempts >with nil errors and terminal batches containing no IDs. Every enabled annotation cleanup therefore >emits false failures, while successful large batches also serialize their complete ID lists into >error logs. ></pre> ></details> > <details> ><summary>Code</summary> ><br/> > ><code>[pkg/services/annotations/annotationsimpl/xorm_store.go[534]](https://github.com/drape-io/crbench-qodo-2026-09/pull/92/files#diff-5897ac24653e3393d73572ed7852f722660f6e9cee73dcd5f7b6b73ce8584f21R534-R534)</code> > >```diff >+ r.log.Error("Annotations to clean by time", "count", len(ids), "ids", ids, "cond", cond, "err", err) >``` ></details> > <details> ><summary>Evidence</summary> ><br/> > ><pre> >All three cleanup variants unconditionally log successful fetches and deletes through <b><i>Error</i></b>; these >methods are reached by the regular cleanup service, and the new one-minute scheduler makes the >pollution recur more frequently. ></pre> > > <code>[pkg/services/annotations/annotationsimpl/xorm_store.go[528-579]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/services/annotations/annotationsimpl/xorm_store.go/#L528-L579)</code> > <code>[pkg/services/annotations/annotationsimpl/cleanup.go[35-57]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/services/annotations/annotationsimpl/cleanup.go/#L35-L57)</code> > <code>[pkg/services/cleanup/cleanup.go[74-82]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/services/cleanup/cleanup.go/#L74-L82)</code> ></details> > <details> ><summary>Agent prompt</summary> ><br/> > >``` >The issue below was found during a code review. Follow the provided context and guidance below and implement a solution > >## Issue description >Normal annotation cleanup batches are emitted at error level even when no error occurred, creating false operational failures and potentially very large log records containing every selected ID. > >## Fix Focus Areas >- pkg/services/annotations/annotationsimpl/xorm_store.go[528-579] > >## Recommended Fix >Remove the diagnostic statements or emit concise batch counts at debug level. Log with `Error` only when `fetchIDs` or `deleteByIDs` actually returns an error, and do not include the complete ID slice in routine logs. >``` > <code>ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools</code> ></details> <hr/> </details> <details> <summary> 2. Slow cleanups run without pause <code>🐞 Bug</code> <code>➹ Performance</code></summary> <br/> > <details open> ><summary>Description</summary> ><br/> > ><pre> ><b><i>Run</i></b> now ticks every minute while the synchronous <b><i>clean</i></b> call can occupy the loop for up to nine >minutes. Whenever a pass lasts at least a minute, a pending tick starts all eight jobs immediately >after it returns, allowing sustained database cleanup to run back-to-back rather than periodically. ></pre> ></details> > <details> ><summary>Code</summary> ><br/> > ><code>[pkg/services/cleanup/cleanup.go[77]](https://github.com/drape-io/crbench-qodo-2026-09/pull/92/files#diff-f08934382b78082a861379480cfc429f0e8cb87ad8dfea47c3e4191630407b78R77-R77)</code> > >```diff >+ ticker := time.NewTicker(time.Minute * 1) >``` ></details> > <details> ><summary>Evidence</summary> ><br/> > ><pre> >The ticker event synchronously invokes <b><i>clean</i></b>, whose timeout is nine minutes and whose body >executes eight cleanup jobs serially. Reducing the ticker period below that timeout removes the >previous guaranteed gap between bounded passes. ></pre> > > <code>[pkg/services/cleanup/cleanup.go[74-94]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/services/cleanup/cleanup.go/#L74-L94)</code> > <code>[pkg/services/cleanup/cleanup.go[96-118]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/services/cleanup/cleanup.go/#L96-L118)</code> ></details> > <details> ><summary>Agent prompt</summary> ><br/> > >``` >The issue below was found during a code review. Follow the provided context and guidance below and implement a solution > >## Issue description >The one-minute ticker is shorter than the cleanup pass's nine-minute timeout, so slow passes can leave pending ticks and cause the complete cleanup sequence to run continuously. > >## Fix Focus Areas >- pkg/services/cleanup/cleanup.go[74-94] > >## Recommended Fix >Restore the ten-minute interval so it remains longer than the cleanup timeout. If one-minute scheduling is required, replace the ticker with a timer reset after each completed pass or otherwise prevent pending ticks from starting an immediate follow-up run. >``` > <code>ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools</code> ></details> <hr/> </details> <br/> <img src="https://img.shields.io/badge/Medium-634FD1?style=flat-square" height="20px" alt="Remediation recommended"> <details> <summary> 3. Large cleanup batches scale quadratically <code>🐞 Bug</code> <code>➹ Performance</code></summary> <br/> > <details open> ><summary>Description</summary> ><br/> > ><pre> ><b><i>deleteByIDs</i></b> repeatedly formats the complete accumulated <b><i>values</i></b> string while constructing >SQLite&#x27;s literal ID list. When the newly tested 32,767-row batch is used, each iteration copies the >growing string and turns statement construction into quadratic CPU and allocation work. ></pre> ></details> > <details> ><summary>Code</summary> ><br/> > ><code>[pkg/services/annotations/annotationsimpl/xorm_store.go[R609-612]](https://github.com/drape-io/crbench-qodo-2026-09/pull/92/files#diff-5897ac24653e3393d73572ed7852f722660f6e9cee73dcd5f7b6b73ce8584f21R609-R612)</code> > >```diff >+ values := fmt.Sprint(ids[0]) >+ for _, v := range ids[1:] { >+ values = fmt.Sprintf("%s, %d", values, v) >+ } >``` ></details> > <details> ><summary>Evidence</summary> ><br/> > ><pre> >The configuration reader places no upper bound on the batch size, and this PR explicitly adds a >32,767-row batch test. The loop at the cited deletion path copies the entire previous string once >for every ID. ></pre> > > <code>[pkg/services/annotations/annotationsimpl/xorm_store.go[597-618]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/services/annotations/annotationsimpl/xorm_store.go/#L597-L618)</code> > <code>[pkg/services/annotations/annotationsimpl/cleanup_test.go[97-110]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/services/annotations/annotationsimpl/cleanup_test.go/#L97-L110)</code> > <code>[pkg/setting/setting.go[708-711]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/setting/setting.go/#L708-L711)</code> ></details> > <details> ><summary>Agent prompt</summary> ><br/> > >``` >The issue below was found during a code review. Follow the provided context and guidance below and implement a solution > >## Issue description >The SQLite deletion path repeatedly copies an increasingly large SQL fragment, making large configured cleanup batches consume quadratic CPU and memory. > >## Fix Focus Areas >- pkg/services/annotations/annotationsimpl/xorm_store.go[597-618] > >## Recommended Fix >Construct the numeric ID list in one linear pass using a pre-sized `strings.Builder`, or convert the IDs once and join the resulting strings. Preserve the numeric-only construction and existing SQLite parameter-limit behavior. >``` > <code>ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools</code> ></details> <hr/> </details> <details> <summary> 4. One failed case corrupts later tests <code>🐞 Bug</code> <code>☼ Reliability</code></summary> <br/> > <details open> ><summary>Description</summary> ><br/> > ><pre> ><b><i>TestIntegrationAnnotationCleanUp</i></b> registers its database cleanup only after creating rows and >checking their initial counts. If insertion or either setup assertion terminates a subtest first, >rows remain in the shared database and later cases encounter duplicate IDs or incorrect counts. ></pre> ></details> > <details> ><summary>Code</summary> ><br/> > ><code>[pkg/services/annotations/annotationsimpl/cleanup_test.go[R116-120]](https://github.com/drape-io/crbench-qodo-2026-09/pull/92/files#diff-ee74128b69dc0b2c613b47412dcb9da26cfab8f252e82b30508bc5343d85532eR116-R120)</code> > >```diff >+ createTestAnnotations(t, fakeSQL, test.createAnnotationsNum, test.createOldAnnotationsNum) >+ assertAnnotationCount(t, fakeSQL, "", int64(test.createAnnotationsNum)) >+ assertAnnotationTagCount(t, fakeSQL, 2*int64(test.createAnnotationsNum)) >+ >+ t.Cleanup(func() { >``` ></details> > <details> ><summary>Evidence</summary> ><br/> > ><pre> >All subtests share the database initialized once for the parent test, but cleanup is registered only >after fixture insertion and assertions. Fixture insertion uses <b><i>require.NoError</i></b>, which aborts the >subtest before registration when an insertion fails. ></pre> > > <code>[pkg/services/annotations/annotationsimpl/cleanup_test.go[23-23]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/services/annotations/annotationsimpl/cleanup_test.go/#L23-L23)</code> > <code>[pkg/services/annotations/annotationsimpl/cleanup_test.go[114-127]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/services/annotations/annotationsimpl/cleanup_test.go/#L114-L127)</code> > <code>[pkg/services/annotations/annotationsimpl/cleanup_test.go[273-291]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/services/annotations/annotationsimpl/cleanup_test.go/#L273-L291)</code> ></details> > <details> ><summary>Agent prompt</summary> ><br/> > >``` >The issue below was found during a code review. Follow the provided context and guidance below and implement a solution > >## Issue description >A setup failure can occur before the per-subtest database cleanup is registered, leaving fixture rows that cascade into failures in subsequent cases sharing the same database. > >## Fix Focus Areas >- pkg/services/annotations/annotationsimpl/cleanup_test.go[114-127] > >## Recommended Fix >Move `t.Cleanup` to the beginning of each subtest, before calling `createTestAnnotations` or making setup assertions. Keep deletion of both `annotation` and `annotation_tag` tables in that cleanup. >``` > <code>ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools</code> ></details> <hr/> </details> <br/> <img src="https://img.shields.io/badge/Low-634FD1?style=flat-square" height="20px" alt="Informational"> <details> <summary> 5. Cleanup docs promise the wrong cadence <code>🐞 Bug</code> <code>⚙ Maintainability</code></summary> <br/> > <details open> ><summary>Description</summary> ><br/> > ><pre> >The implementation now schedules cleanup every minute, but the reporting and image-rendering >documentation still tells users that temporary-file cleanup runs every ten minutes. Users diagnosing >file retention or estimating cleanup load are therefore given behavior that no longer matches the >service. ></pre> ></details> > <details> ><summary>Code</summary> ><br/> > ><code>[pkg/services/cleanup/cleanup.go[77]](https://github.com/drape-io/crbench-qodo-2026-09/pull/92/files#diff-f08934382b78082a861379480cfc429f0e8cb87ad8dfea47c3e4191630407b78R77-R77)</code> > >```diff >+ ticker := time.NewTicker(time.Minute * 1) >``` ></details> > <details> ><summary>Evidence</summary> ><br/> > ><pre> >The changed ticker controls the service that invokes temporary-file cleanup, while two user-facing >documentation pages explicitly retain the old ten-minute statement. ></pre> > > <code>[pkg/services/cleanup/cleanup.go[74-82]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/pkg/services/cleanup/cleanup.go/#L74-L82)</code> > <code>[docs/sources/dashboards/create-reports/index.md[134-136]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/docs/sources/dashboards/create-reports/index.md/#L134-L136)</code> > <code>[docs/sources/setup-grafana/image-rendering/_index.md[25-27]](https://github.com/drape-io/crbench-qodo-2026-09/blob/a7538f33772cd45fc18e13e0a49bc6ca92f97a55/docs/sources/setup-grafana/image-rendering/_index.md/#L25-L27)</code> ></details> > <details> ><summary>Agent prompt</summary> ><br/> > >``` >The issue below was found during a code review. Follow the provided context and guidance below and implement a solution > >## Issue description >User-facing documentation still describes a ten-minute temporary-file cleanup schedule after the implementation changes that schedule to one minute. > >## Fix Focus Areas >- pkg/services/cleanup/cleanup.go[74-82] >- docs/sources/dashboards/create-reports/index.md[134-136] >- docs/sources/setup-grafana/image-rendering/_index.md[25-27] > >## Recommended Fix >Either restore the documented ten-minute implementation interval or update both documentation pages to state the intended one-minute cadence. >``` > <code>ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools</code> ></details> <hr/> </details> <img src="https://www.qodo.ai/wp-content/uploads/2025/11/light-grey-line.svg" height="10%" alt="Grey Divider"> <!-- qodo-context:start --> <details><summary><strong>Context sources</strong></summary> <div>Review mode: <code>⚖️ Balanced</code>: Downgraded extended -&gt; standard: change is below the extended eligibility bar (hunks 12/18, lines 261/200; both must reach the floor). Router rationale: This changes core annotation cleanup behavior across multiple database paths and dialects, adds batching/cancellation logic, and alters cleanup scheduling, creating several independent, easy-to-miss correctness and concurrency risks.</div> <!-- qodo-context:end --> </details> <img src="https://www.qodo.ai/wp-content/uploads/2025/11/light-grey-line.svg" height="10%" alt="Grey Divider"> <!-- qodo-daily-tip:start --> <details> <summary><strong>Tip of the day</strong></summary> <br/> <pre>💡 Did you know, you can reply &#x27;qodo&#x27; on any finding to push back, ask questions, or dig deeper</pre> <a href="https://docs.qodo.ai/tips-and-tricks">More tips ↗</a> | <a href="https://app.qodo.ai/configurations?tab=display-preferences">Customize Qodo ↗</a> | <a href="https://docs.qodo.ai">Qodo docs ↗</a> </details> <img src="https://www.qodo.ai/wp-content/uploads/2025/11/light-grey-line.svg" height="10%" alt="Grey Divider"> <!-- qodo-daily-tip:end --> <!-- https://github.com/drape-io/crbench-qodo-2026-09/commit/a7538f33772cd45fc18e13e0a49bc6ca92f97a55 --> <a href="https://www.qodo.ai"><img src="https://www.qodo.ai/wp-content/uploads/2025/03/qodo-logo.svg" width="80" alt="Qodo Logo"></a>