{"record":{"id":"8af76fc1c36f05a0","repo":"juanfont/headscale","slug":"copying-data-w","errorCode":null,"errorMessage":"copying data: %w","messagePattern":"copying data: %w","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"critical","filePath":"hscontrol/db/db.go","lineNumber":408,"sourceCode":"             FROM pre_auth_keys_old`,\n\n\t\t\t\t\t\t`INSERT INTO api_keys (id, prefix, hash, expiration, last_seen, created_at)\n             SELECT id, prefix, hash, expiration, last_seen, created_at\n             FROM api_keys_old`,\n\n\t\t\t\t\t\t`INSERT INTO nodes (id, machine_key, node_key, disco_key, endpoints, host_info, ipv4, ipv6, hostname, given_name, user_id, register_method, forced_tags, auth_key_id, last_seen, expiry, approved_routes, created_at, updated_at, deleted_at)\n             SELECT id, machine_key, node_key, disco_key, endpoints, host_info, ipv4, ipv6, hostname, given_name, user_id, register_method, forced_tags, auth_key_id, last_seen, expiry, approved_routes, created_at, updated_at, deleted_at\n             FROM nodes_old`,\n\n\t\t\t\t\t\t`INSERT INTO policies (id, data, created_at, updated_at, deleted_at)\n             SELECT id, data, created_at, updated_at, deleted_at\n             FROM policies_old`,\n\t\t\t\t\t}\n\n\t\t\t\t\tfor _, copySQL := range dataCopySQL {\n\t\t\t\t\t\terr := tx.Exec(copySQL).Error\n\t\t\t\t\t\tif err != nil {\n\t\t\t\t\t\t\treturn fmt.Errorf(\"copying data: %w\", err)\n\t\t\t\t\t\t}\n\t\t\t\t\t}\n\n\t\t\t\t\t// Create indexes\n\t\t\t\t\tindexes := []string{\n\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)\",","sourceCodeStart":390,"sourceCodeEnd":426,"githubUrl":"https://github.com/juanfont/headscale/blob/565fd254d06c4c7f9a8cad1714a43445c79ba420/hscontrol/db/db.go#L390-L426","documentation":"The SQLite schema-recreation migration failed at the INSERT INTO ... SELECT * FROM <table>_old data-copy step. With new empty tables in place, copying rows back failed - most commonly a UNIQUE constraint violation (idx_api_keys_prefix, idx_provider_identifier, or the partial unique index on user names) triggered by duplicate legacy data, or type/NOT NULL violations against the stricter new schema.","triggerScenarios":"Legacy data containing two users with the same name where provider_identifier is NULL (violates idx_name_no_provider_identifier), duplicate API key prefixes, or NULL/oversized values that the new schema rejects.","commonSituations":"Very old databases (pre-uniqueness-constraint era) holding duplicate usernames or key prefixes; data imported from other tools.","solutions":["Read the wrapped error - it names the constraint/table (e.g. 'UNIQUE constraint failed: users.name').","Restore from backup, deduplicate the offending rows manually (rename or delete duplicates) with sqlite3, then retry the upgrade.","Find duplicates: SELECT name, COUNT(*) FROM users WHERE provider_identifier IS NULL GROUP BY name HAVING COUNT(*)>1.","After cleanup, re-run headscale so the migration re-executes from the start."],"exampleFix":"-- before: duplicate local users block migration\nSELECT name, COUNT(*) FROM users WHERE provider_identifier IS NULL GROUP BY name HAVING COUNT(*) > 1;\n\n-- after: rename the stale duplicate, then re-run the migration\nUPDATE users SET name = name || '-old' WHERE id = <duplicate_id>;","handlingStrategy":"validation","validationCode":"-- Pre-upgrade duplicate checks on a backup copy\nSELECT name, COUNT(*) FROM users WHERE provider_identifier IS NULL GROUP BY name HAVING COUNT(*) > 1;\nSELECT prefix, COUNT(*) FROM api_keys GROUP BY prefix HAVING COUNT(*) > 1;\nSELECT provider_identifier, COUNT(*) FROM users WHERE provider_identifier IS NOT NULL GROUP BY provider_identifier HAVING COUNT(*) > 1;","typeGuard":null,"tryCatchPattern":"if _, err := db.NewHeadscaleDatabase(cfg); err != nil {\n    if strings.Contains(err.Error(), \"copying data\") && strings.Contains(err.Error(), \"UNIQUE\") {\n        // restore backup, deduplicate rows named in the error, re-run the upgrade\n    }\n}","preventionTips":["Run the duplicate-detection queries on a copy of the DB before the v0.27.0 upgrade.","Delete stale test/duplicate users and keys regularly.","Never re-import user rows from external dumps without checking uniqueness."],"tags":["database","sqlite","migration","unique-constraint","data-migration"],"backgroundTag":null,"analyzedSha":"565fd254d06c4c7f9a8cad1714a43445c79ba420","analyzedAt":"2026-08-15T13:12:30.133Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}