{"record":{"id":"d81768c4f06105ca","repo":"juanfont/headscale","slug":"creating-index-w","errorCode":null,"errorMessage":"creating index: %w","messagePattern":"creating index: %w","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"critical","filePath":"hscontrol/db/db.go","lineNumber":432,"sourceCode":"\t\t\t\t\t\t\"CREATE INDEX idx_users_deleted_at ON users(deleted_at)\",\n\t\t\t\t\t\t`CREATE UNIQUE INDEX idx_provider_identifier ON users(\n  provider_identifier\n) WHERE provider_identifier IS NOT NULL`,\n\t\t\t\t\t\t`CREATE UNIQUE INDEX idx_name_provider_identifier ON users(\n  name,\n  provider_identifier\n)`,\n\t\t\t\t\t\t`CREATE UNIQUE INDEX idx_name_no_provider_identifier ON users(\n  name\n) WHERE provider_identifier IS NULL`,\n\t\t\t\t\t\t\"CREATE UNIQUE INDEX idx_api_keys_prefix ON api_keys(prefix)\",\n\t\t\t\t\t\t\"CREATE INDEX idx_policies_deleted_at ON policies(deleted_at)\",\n\t\t\t\t\t}\n\n\t\t\t\t\tfor _, indexSQL := range indexes {\n\t\t\t\t\t\terr := tx.Exec(indexSQL).Error\n\t\t\t\t\t\tif err != nil {\n\t\t\t\t\t\t\treturn fmt.Errorf(\"creating index: %w\", err)\n\t\t\t\t\t\t}\n\t\t\t\t\t}\n\n\t\t\t\t\t// Drop old tables only after everything succeeds\n\t\t\t\t\tfor _, table := range tablesToRename {\n\t\t\t\t\t\terr := tx.Exec(\"DROP TABLE IF EXISTS \" + table + \"_old\").Error\n\t\t\t\t\t\tif err != nil {\n\t\t\t\t\t\t\tlog.Warn().Str(\"table\", table+\"_old\").Err(err).Msg(\"failed to drop old table, but migration succeeded\")\n\t\t\t\t\t\t}\n\t\t\t\t\t}\n\n\t\t\t\t\tlog.Info().Msg(\"schema recreation completed successfully\")\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// v0.27.1","sourceCodeStart":414,"sourceCodeEnd":450,"githubUrl":"https://github.com/juanfont/headscale/blob/565fd254d06c4c7f9a8cad1714a43445c79ba420/hscontrol/db/db.go#L414-L450","documentation":"The final step of the SQLite schema-recreation migration failed creating the canonical indexes, including UNIQUE indexes on api_keys(prefix), users.provider_identifier, and the partial unique index on users.name for local accounts. Unlike the data-copy step, this fails when the CREATE INDEX statement itself conflicts - most often because a leftover index with the same name survived, or (for unique indexes) duplicate data slipped in.","triggerScenarios":"CREATE UNIQUE INDEX rejected due to an existing index of the same name from a partially-completed earlier run, or duplicate values in the column after the data copy (forked/legacy data).","commonSituations":"Retry after a crashed migration leaving renamed tables AND old indexes; duplicate provider identifiers surviving the 202505141324 cleanup in edge cases.","solutions":["Check the wrapped error: 'index idx_... already exists' vs 'UNIQUE constraint failed'.","For a name collision, open the DB with sqlite3 and DROP the leftover index, then restart headscale.","For duplicate data, restore from backup, deduplicate rows, and retry the migration.","If the migration otherwise completed, verify table data counts against users_old/nodes_old before dropping anything manually."],"exampleFix":"-- before: retry fails with 'index idx_provider_identifier already exists'\n\n-- after: drop the leftover index, then restart headscale\nDROP INDEX IF EXISTS idx_provider_identifier;\n-- then: headscale serve / restart the server to re-run the migration","handlingStrategy":"fallback","validationCode":"-- Pre-upgrade: confirm no leftover indexes from prior failed runs\nSELECT name FROM sqlite_master WHERE type='index' AND name IN\n ('idx_users_deleted_at','idx_provider_identifier','idx_name_provider_identifier',\n  'idx_name_no_provider_identifier','idx_api_keys_prefix','idx_policies_deleted_at');","typeGuard":null,"tryCatchPattern":"if _, err := db.NewHeadscaleDatabase(cfg); err != nil {\n    if strings.Contains(err.Error(), \"creating index\") {\n        if strings.Contains(err.Error(), \"already exists\") {\n            // DROP the leftover index via sqlite3, then restart headscale\n        } else if strings.Contains(err.Error(), \"UNIQUE\") {\n            // duplicate column data: restore backup, deduplicate, retry\n        }\n    }\n}","preventionTips":["After a failed upgrade attempt, restore the backup instead of retrying against residual indexes.","Verify uniqueness of api_keys.prefix and users identifiers before the schema-recreation migration.","Confirm the server starts and serves requests before deleting the pre-upgrade backup."],"tags":["database","sqlite","migration","index","unique-constraint"],"backgroundTag":null,"analyzedSha":"565fd254d06c4c7f9a8cad1714a43445c79ba420","analyzedAt":"2026-08-15T13:12:30.133Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}