mattermost-community/focalboard · error

cannot commit unique IDs transaction: %w

Error message

cannot commit unique IDs transaction: %w

What it means

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'.

Source

Thrown at server/services/store/sqlstore/data_migrations.go:128

			newID := utils.NewID(model.BlockType2IDType(block.Type))
			if err := s.replaceBlockID(tx, block.ID, newID, block.WorkspaceID); err != nil {
				if rollbackErr := tx.Rollback(); rollbackErr != nil {
					s.logger.Error("Unique IDs transaction rollback error", mlog.Err(rollbackErr), mlog.String("methodName", "replaceBlockID"))
				}
				return fmt.Errorf("cannot replace blockID %s: %w", block.ID, err)
			}
		}
	}

	if err := s.setSystemSetting(tx, UniqueIDsMigrationKey, strconv.FormatBool(true)); err != nil {
		if rollbackErr := tx.Rollback(); rollbackErr != nil {
			s.logger.Error("Unique IDs transaction rollback error", mlog.Err(rollbackErr), mlog.String("methodName", "setSystemSetting"))
		}
		return fmt.Errorf("cannot mark migration as completed: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("cannot commit unique IDs transaction: %w", err)
	}

	s.logger.Debug("Unique IDs migration finished successfully")
	return nil
}

// RunCategoryUUIDIDMigration takes care of deriving the categories
// from the boards and its memberships. The name references UUID
// because of the preexisting purpose of this migration, and has been
// preserved for compatibility with already migrated instances.
func (s *SQLStore) RunCategoryUUIDIDMigration() error {
	setting, err := s.GetSystemSetting(CategoryUUIDIDMigrationKey)
	if err != nil {
		return fmt.Errorf("cannot get migration state: %w", err)
	}

	// If the migration is already completed, do not run it again.
	if hasAlreadyRun, _ := strconv.ParseBool(setting); hasAlreadyRun {

View on GitHub (pinned to a84bbb65e3)

Solutions

1. 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).
2. Shorten the transaction: run the migration on a healthy, low-traffic DB or on a restored copy, then deploy.
3. Increase proxy/load-balancer idle timeouts for the migration connection.
4. Verify DB server stability (failover logs) and rerun; commit failure leaves no partial state due to atomic rollback.
5. Monitor the migration with the 'Unique IDs migration finished successfully' debug log to confirm completion on retry.

Example fix

// before
err := store.RunUniqueIDsMigration() // fails at commit after 30min txn
// after
// pre-check size, run in maintenance window, then retry idempotently:
for i := 0; i < 3; i++ {
	if err := store.RunUniqueIDsMigration(); err == nil {
		break
	} else if i == 2 {
		log.Fatal(err)
	}
	time.Sleep(30 * time.Second)
}

if err := db.PingContext(ctx); err != nil { return err } // verify stable connection before starting long migration

func isCommitFailedError(err error) bool {
	return err != nil && strings.Contains(err.Error(), "cannot commit unique IDs transaction")
}

for attempt := 0; attempt < 3; attempt++ {
	err := store.RunUniqueIDsMigration()
	if err == nil { break }
	if !isCommitFailedError(err) || attempt == 2 { return err }
	time.Sleep(30 * time.Second) // atomic rollback makes retry safe
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• cannot commit category UUIDs transaction: %w
• cannot get migration state: %w
• cannot get blocks with same ID: %w
• cannot replace blockID %s: %w
• cannot mark migration as completed: %w

AI-assisted analysis of mattermost-community/focalboard@a84bbb65e3 (2026-08-30). Data as JSON: /api/errors/88633f90d7202f4e. Report an issue: GitHub.