{"record":{"id":"88633f90d7202f4e","repo":"mattermost-community/focalboard","slug":"cannot-commit-unique-ids-transaction-w","errorCode":null,"errorMessage":"cannot commit unique IDs transaction: %w","messagePattern":"cannot commit unique IDs transaction: %w","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"server/services/store/sqlstore/data_migrations.go","lineNumber":128,"sourceCode":"\t\t\tnewID := utils.NewID(model.BlockType2IDType(block.Type))\n\t\t\tif err := s.replaceBlockID(tx, block.ID, newID, block.WorkspaceID); err != nil {\n\t\t\t\tif rollbackErr := tx.Rollback(); rollbackErr != nil {\n\t\t\t\t\ts.logger.Error(\"Unique IDs transaction rollback error\", mlog.Err(rollbackErr), mlog.String(\"methodName\", \"replaceBlockID\"))\n\t\t\t\t}\n\t\t\t\treturn fmt.Errorf(\"cannot replace blockID %s: %w\", block.ID, err)\n\t\t\t}\n\t\t}\n\t}\n\n\tif err := s.setSystemSetting(tx, UniqueIDsMigrationKey, strconv.FormatBool(true)); err != nil {\n\t\tif rollbackErr := tx.Rollback(); rollbackErr != nil {\n\t\t\ts.logger.Error(\"Unique IDs transaction rollback error\", mlog.Err(rollbackErr), mlog.String(\"methodName\", \"setSystemSetting\"))\n\t\t}\n\t\treturn fmt.Errorf(\"cannot mark migration as completed: %w\", err)\n\t}\n\n\tif err := tx.Commit(); err != nil {\n\t\treturn fmt.Errorf(\"cannot commit unique IDs transaction: %w\", err)\n\t}\n\n\ts.logger.Debug(\"Unique IDs migration finished successfully\")\n\treturn nil\n}\n\n// RunCategoryUUIDIDMigration takes care of deriving the categories\n// from the boards and its memberships. The name references UUID\n// because of the preexisting purpose of this migration, and has been\n// preserved for compatibility with already migrated instances.\nfunc (s *SQLStore) RunCategoryUUIDIDMigration() error {\n\tsetting, err := s.GetSystemSetting(CategoryUUIDIDMigrationKey)\n\tif err != nil {\n\t\treturn fmt.Errorf(\"cannot get migration state: %w\", err)\n\t}\n\n\t// If the migration is already completed, do not run it again.\n\tif hasAlreadyRun, _ := strconv.ParseBool(setting); hasAlreadyRun {","sourceCodeStart":110,"sourceCodeEnd":146,"githubUrl":"https://github.com/mattermost-community/focalboard/blob/a84bbb65e32edf972856b329417096ac413518e9/server/services/store/sqlstore/data_migrations.go#L110-L146","documentation":"Error from SQLStore.RunUniqueIDsMigration (server/services/store/sqlstore/data_migrations.go:128). This is the last step of the migration: tx.Commit(). If committing fails, all in-transaction ID replacements are discarded by the DB and the error is wrapped as 'cannot commit unique IDs transaction'.","triggerScenarios":"Running RunUniqueIDsMigration when tx.Commit() fails: connection dropped during the long transaction (network blip, proxy idle timeout), serialization/deadlock detected at commit time, or the DB server restarting mid-migration.","commonSituations":"Very large block tables making the transaction span minutes/hours across flaky network links; cloud DB proxies (RDS Proxy, PgBouncer) terminating idle transactions; failover of the primary DB during startup migrations.","solutions":["Check the wrapped cause; connection-loss errors mean simply retrying the migration after connectivity is restored (the flag was never set, so it will run again).","Shorten the transaction: run the migration on a healthy, low-traffic DB or on a restored copy, then deploy.","Increase proxy/load-balancer idle timeouts for the migration connection.","Verify DB server stability (failover logs) and rerun; commit failure leaves no partial state due to atomic rollback.","Monitor the migration with the 'Unique IDs migration finished successfully' debug log to confirm completion on retry."],"exampleFix":"// before\nerr := store.RunUniqueIDsMigration() // fails at commit after 30min txn\n// after\n// pre-check size, run in maintenance window, then retry idempotently:\nfor i := 0; i < 3; i++ {\n\tif err := store.RunUniqueIDsMigration(); err == nil {\n\t\tbreak\n\t} else if i == 2 {\n\t\tlog.Fatal(err)\n\t}\n\ttime.Sleep(30 * time.Second)\n}","handlingStrategy":"retry","validationCode":"if err := db.PingContext(ctx); err != nil { return err } // verify stable connection before starting long migration","typeGuard":"func isCommitFailedError(err error) bool {\n\treturn err != nil && strings.Contains(err.Error(), \"cannot commit unique IDs transaction\")\n}","tryCatchPattern":"for attempt := 0; attempt < 3; attempt++ {\n\terr := store.RunUniqueIDsMigration()\n\tif err == nil { break }\n\tif !isCommitFailedError(err) || attempt == 2 { return err }\n\ttime.Sleep(30 * time.Second) // atomic rollback makes retry safe\n}","preventionTips":["Keep migration transactions short via maintenance windows","Increase LB/proxy idle timeouts for long transactions","Check DB failover/stability logs after a commit failure","Remember commit failure means nothing was applied — retry is safe"],"tags":["database","migration","transaction-commit","focalboard"],"backgroundTag":"transaction-commit-failed","analyzedSha":"a84bbb65e32edf972856b329417096ac413518e9","analyzedAt":"2026-08-30T09:22:20.720Z","schemaVersion":2},"datasetVersion":"2026-08-30T13:17:10.514Z"}