{"record":{"id":"af0d49fb5b980424","repo":"juanfont/headscale","slug":"creating-prefix-index-w","errorCode":null,"errorMessage":"creating prefix index: %w","messagePattern":"creating prefix index: %w","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"critical","filePath":"hscontrol/db/db.go","lineNumber":537,"sourceCode":"\t\t\t\t\t\terr := tx.Migrator().AddColumn(&types.PreAuthKey{}, \"prefix\")\n\t\t\t\t\t\tif err != nil {\n\t\t\t\t\t\t\treturn fmt.Errorf(\"adding prefix column: %w\", err)\n\t\t\t\t\t\t}\n\t\t\t\t\t}\n\n\t\t\t\t\t// Check and add hash column if it doesn't exist\n\t\t\t\t\tif !tx.Migrator().HasColumn(&types.PreAuthKey{}, \"hash\") {\n\t\t\t\t\t\terr := tx.Migrator().AddColumn(&types.PreAuthKey{}, \"hash\")\n\t\t\t\t\t\tif err != nil {\n\t\t\t\t\t\t\treturn fmt.Errorf(\"adding hash column: %w\", err)\n\t\t\t\t\t\t}\n\t\t\t\t\t}\n\n\t\t\t\t\t// Create partial unique index to allow multiple legacy keys (NULL/empty prefix)\n\t\t\t\t\t// while enforcing uniqueness for new bcrypt-based keys\n\t\t\t\t\terr := tx.Exec(\"CREATE UNIQUE INDEX IF NOT EXISTS idx_pre_auth_keys_prefix ON pre_auth_keys(prefix) WHERE prefix IS NOT NULL AND prefix != ''\").Error\n\t\t\t\t\tif err != nil {\n\t\t\t\t\t\treturn fmt.Errorf(\"creating prefix index: %w\", err)\n\t\t\t\t\t}\n\n\t\t\t\t\treturn nil\n\t\t\t\t},\n\t\t\t\tRollback: func(db *gorm.DB) error { return nil },\n\t\t\t},\n\t\t\t{\n\t\t\t\tID: \"202511122344-remove-newline-index\",\n\t\t\t\tMigrate: func(tx *gorm.DB) error {\n\t\t\t\t\t// Reformat multi-line indexes to single-line for consistency\n\t\t\t\t\t// This migration drops and recreates the three user identity indexes\n\t\t\t\t\t// to match the single-line format expected by schema validation\n\n\t\t\t\t\t// Drop existing multi-line indexes\n\t\t\t\t\tdropIndexes := []string{\n\t\t\t\t\t\t`DROP INDEX IF EXISTS idx_provider_identifier`,\n\t\t\t\t\t\t`DROP INDEX IF EXISTS idx_name_provider_identifier`,\n\t\t\t\t\t\t`DROP INDEX IF EXISTS idx_name_no_provider_identifier`,","sourceCodeStart":519,"sourceCodeEnd":555,"githubUrl":"https://github.com/juanfont/headscale/blob/565fd254d06c4c7f9a8cad1714a43445c79ba420/hscontrol/db/db.go#L519-L555","documentation":"Migration '202511011637-preauthkey-bcrypt' fails to create the partial unique index idx_pre_auth_keys_prefix on pre_auth_keys(prefix). The most common cause is existing duplicate non-empty prefix values violating uniqueness; other causes are missing CREATE INDEX privilege, lock contention, or an index with the same name on a different table. IF NOT EXISTS protects only against the index already existing, not against constraint violations.","triggerScenarios":"Legacy pre_auth_keys rows that already contain a prefix column (e.g. from a partially applied earlier attempt) with duplicated prefix strings; CREATE UNIQUE INDEX ... WHERE prefix IS NOT NULL AND prefix != '' then aborts with a unique-constraint violation from the database.","commonSituations":"Re-running an upgrade after a crashed earlier migration attempt that added prefixes but not the index; hand-edited databases where prefixes were copied between keys; DB user without INDEX privilege on PostgreSQL.","solutions":["Check the wrapped error - if it reports a duplicate key value, find offending rows: SELECT prefix, count(*) FROM pre_auth_keys WHERE prefix IS NOT NULL AND prefix != '' GROUP BY prefix HAVING count(*) > 1","Deduplicate: keep the newest key per prefix and clear prefix/hash on the obsolete duplicates (or delete them) via a manual SQL session, then restart headscale","If the cause is permissions/locking instead, grant CREATE INDEX or serialize access as with the AddColumn errors","Restore from a pre-upgrade backup and re-run the migration cleanly if the data was hand-modified"],"exampleFix":"-- before: duplicates block the unique index\nSELECT prefix, count(*) FROM pre_auth_keys WHERE prefix IS NOT NULL AND prefix != '' GROUP BY prefix HAVING count(*) > 1;\n-- after: neutralize duplicates, then restart headscale\nUPDATE pre_auth_keys SET prefix = NULL, hash = NULL WHERE id NOT IN (SELECT max(id) FROM pre_auth_keys WHERE prefix IS NOT NULL GROUP BY prefix);","handlingStrategy":"validation","validationCode":"// Pre-flight: would the partial unique index succeed?\nrows, err := db.Query(`SELECT prefix FROM pre_auth_keys\n\tWHERE prefix IS NOT NULL AND prefix != ''\n\tGROUP BY prefix HAVING count(*) > 1`)\nif err != nil { return err }\nif rows.Next() {\n\treturn errors.New(\"duplicate pre_auth_keys.prefix values would violate the unique index\")\n}","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Never hand-copy prefix values between pre-auth keys","After any failed upgrade, inspect partial schema state before retrying","Test upgrades on a copy of the production database first"],"tags":["database","migration","unique-index","gorm","preauthkey"],"backgroundTag":null,"analyzedSha":"565fd254d06c4c7f9a8cad1714a43445c79ba420","analyzedAt":"2026-08-15T13:12:30.133Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}