starred/headscale
1
0
Fork 0
mirror of https://github.com/juanfont/headscale.git synced 2026-01-11 20:00:28 +01:00
Code Issues 98 Packages Projects Releases 10 Wiki Activity

[Bug] sqlite3 migration failed: UNIQUE constraint failed #1139

New Issue
Closed
opened 2025-12-29 02:28:30 +01:00 by adam · 5 comments
adam commented 2025-12-29 02:28:30 +01:00
Owner
Copy Link

Originally created by @ryanlim on GitHub (Nov 8, 2025).

Is this a support request?

  • This is not a support request

Is there an existing issue for this?

  • I have searched the existing issues

Current Behavior

headscale will not start on v0.27 after a migration from v0.26.1. This is the docker log entries:

Attaching to headscale
headscale  | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582077,"message":"Opening database"}
headscale  | {"level":"info","time":1762582077,"message":"Starting schema recreation with table renaming"}
headscale  | {"level":"fatal","error":"copying data: constraint failed: UNIQUE constraint failed: nodes.id (1555)","time":1762582077,"message":"Migration failed: copying data: constraint failed: UNIQUE constraint failed: nodes.id (1555)"}
headscale exited with code 1 (restarting)
headscale  | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582078,"message":"Opening database"}
headscale  | {"level":"info","time":1762582078,"message":"Starting schema recreation with table renaming"}
headscale  | {"level":"fatal","error":"renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)","time":1762582078,"message":"Migration failed: renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)"}
headscale exited with code 1 (restarting)
headscale  | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582079,"message":"Opening database"}
headscale  | {"level":"info","time":1762582079,"message":"Starting schema recreation with table renaming"}
headscale  | {"level":"fatal","error":"renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)","time":1762582079,"message":"Migration failed: renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)"}
headscale exited with code 1 (restarting)

Expected Behavior

Update docker image from v0.26.1 to v0.27 with migration completing successfully. headscale starts without crashlooping.

Steps To Reproduce

  • OS:Debian 12
  • Headscale version:
    Docker for Headscale
    version:headscale/headscale:latest
  • Tailscale version: 1.90.4

Environment

- OS: Ubuntu 24.04.3 LTS + Docker version 28.5.1, build e180ab8
- Headscale version: v0.27
- Tailscale version: 1.90.4

Runtime environment

  • Headscale is behind a (reverse) proxy
  • Headscale runs in a container

Debug information

Attaching to headscale
headscale  | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582077,"message":"Opening database"}
headscale  | {"level":"info","time":1762582077,"message":"Starting schema recreation with table renaming"}
headscale  | {"level":"fatal","error":"copying data: constraint failed: UNIQUE constraint failed: nodes.id (1555)","time":1762582077,"message":"Migration failed: copying data: constraint failed: UNIQUE constraint failed: nodes.id (1555)"}
headscale exited with code 1 (restarting)
headscale  | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582078,"message":"Opening database"}
headscale  | {"level":"info","time":1762582078,"message":"Starting schema recreation with table renaming"}
headscale  | {"level":"fatal","error":"renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)","time":1762582078,"message":"Migration failed: renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)"}
headscale exited with code 1 (restarting)
headscale  | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582079,"message":"Opening database"}
headscale  | {"level":"info","time":1762582079,"message":"Starting schema recreation with table renaming"}
headscale  | {"level":"fatal","error":"renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)","time":1762582079,"message":"Migration failed: renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)"}
headscale exited with code 1 (restarting)
Originally created by @ryanlim on GitHub (Nov 8, 2025). ### Is this a support request? - [x] This is not a support request ### Is there an existing issue for this? - [x] I have searched the existing issues ### Current Behavior headscale will not start on v0.27 after a migration from v0.26.1. This is the docker log entries: ``` Attaching to headscale headscale | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582077,"message":"Opening database"} headscale | {"level":"info","time":1762582077,"message":"Starting schema recreation with table renaming"} headscale | {"level":"fatal","error":"copying data: constraint failed: UNIQUE constraint failed: nodes.id (1555)","time":1762582077,"message":"Migration failed: copying data: constraint failed: UNIQUE constraint failed: nodes.id (1555)"} headscale exited with code 1 (restarting) headscale | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582078,"message":"Opening database"} headscale | {"level":"info","time":1762582078,"message":"Starting schema recreation with table renaming"} headscale | {"level":"fatal","error":"renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)","time":1762582078,"message":"Migration failed: renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)"} headscale exited with code 1 (restarting) headscale | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582079,"message":"Opening database"} headscale | {"level":"info","time":1762582079,"message":"Starting schema recreation with table renaming"} headscale | {"level":"fatal","error":"renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)","time":1762582079,"message":"Migration failed: renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)"} headscale exited with code 1 (restarting) ``` ### Expected Behavior Update docker image from v0.26.1 to v0.27 with migration completing successfully. headscale starts without crashlooping. ### Steps To Reproduce - OS:Debian 12 - Headscale version: Docker for Headscale version:headscale/headscale:latest - Tailscale version: 1.90.4 ### Environment ```markdown - OS: Ubuntu 24.04.3 LTS + Docker version 28.5.1, build e180ab8 - Headscale version: v0.27 - Tailscale version: 1.90.4 ``` ### Runtime environment - [x] Headscale is behind a (reverse) proxy - [x] Headscale runs in a container ### Debug information ``` Attaching to headscale headscale | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582077,"message":"Opening database"} headscale | {"level":"info","time":1762582077,"message":"Starting schema recreation with table renaming"} headscale | {"level":"fatal","error":"copying data: constraint failed: UNIQUE constraint failed: nodes.id (1555)","time":1762582077,"message":"Migration failed: copying data: constraint failed: UNIQUE constraint failed: nodes.id (1555)"} headscale exited with code 1 (restarting) headscale | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582078,"message":"Opening database"} headscale | {"level":"info","time":1762582078,"message":"Starting schema recreation with table renaming"} headscale | {"level":"fatal","error":"renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)","time":1762582078,"message":"Migration failed: renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)"} headscale exited with code 1 (restarting) headscale | {"level":"info","database":"sqlite3","path":"/etc/headscale/db.sqlite","time":1762582079,"message":"Opening database"} headscale | {"level":"info","time":1762582079,"message":"Starting schema recreation with table renaming"} headscale | {"level":"fatal","error":"renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)","time":1762582079,"message":"Migration failed: renaming table users to users_old: SQL logic error: there is already another table or index with this name: users_old (1)"} headscale exited with code 1 (restarting) ```
adam added the questionbug labels 2025-12-29 02:28:30 +01:00
adam closed this issue 2025-12-29 02:28:30 +01:00
adam commented 2025-12-29 02:28:31 +01:00
Author
Owner
Copy Link

@nblock commented on GitHub (Nov 8, 2025):

Please provide your schema when still on 0.26.1: sqlite3 -readonly /path/to/db.sqlite .schema

@nblock commented on GitHub (Nov 8, 2025): Please provide your schema when still on 0.26.1: `sqlite3 -readonly /path/to/db.sqlite .schema`
adam commented 2025-12-29 02:28:31 +01:00
Author
Owner
Copy Link

@ryanlim commented on GitHub (Nov 8, 2025):

Sure. Here is the schema from 0.26.1


CREATE TABLE `migrations` (`id` text,PRIMARY KEY (`id`));
CREATE TABLE `pre_auth_keys` (`id` integer PRIMARY KEY AUTOINCREMENT,`key` text,`user_id` integer,`reusable` numeric,`ephemeral` numeric DEFAULT false,`used` numeric DEFAULT false,`created_at` datetime,`expiration` datetime, `tags` text,CONSTRAINT `fk_pre_auth_keys_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE `api_keys` (`id` integer PRIMARY KEY AUTOINCREMENT,`prefix` text,`hash` blob,`created_at` datetime,`expiration` datetime,`last_seen` datetime);
CREATE TABLE IF NOT EXISTS "nodes"  (`id` integer PRIMARY KEY AUTOINCREMENT,`machine_key` text,`node_key` text,`disco_key` text,`endpoints` text,`host_info` text,`ipv4` text,`ipv6` text,`hostname` text,`given_name` varchar(63),`user_id` integer,`register_method` text,`forced_tags` text,`auth_key_id` integer,`last_seen` datetime,`expiry` datetime,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime, `approved_routes` text,CONSTRAINT `fk_nodes_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,CONSTRAINT `fk_nodes_auth_key` FOREIGN KEY (`auth_key_id`) REFERENCES `pre_auth_keys`(`id`) ON DELETE SET NULL);
CREATE TABLE `policies` (`id` integer PRIMARY KEY AUTOINCREMENT,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`data` text);
CREATE TABLE IF NOT EXISTS "users"  (`id` integer PRIMARY KEY AUTOINCREMENT,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`name` text, `display_name` text, `email` text, `provider_identifier` text, `provider` text, `profile_pic_url` text);
CREATE UNIQUE INDEX `idx_api_keys_prefix` ON `api_keys`(`prefix`);
CREATE INDEX `idx_policies_deleted_at` ON `policies`(`deleted_at`);
CREATE INDEX `idx_users_deleted_at` ON `users`(`deleted_at`);
CREATE UNIQUE INDEX idx_provider_identifier ON users (provider_identifier) WHERE provider_identifier IS NOT NULL;
CREATE UNIQUE INDEX idx_name_provider_identifier ON users (name,provider_identifier);
CREATE UNIQUE INDEX idx_name_no_provider_identifier ON users (name) WHERE provider_identifier IS NULL;
@ryanlim commented on GitHub (Nov 8, 2025): Sure. Here is the schema from 0.26.1 ``` CREATE TABLE `migrations` (`id` text,PRIMARY KEY (`id`)); CREATE TABLE `pre_auth_keys` (`id` integer PRIMARY KEY AUTOINCREMENT,`key` text,`user_id` integer,`reusable` numeric,`ephemeral` numeric DEFAULT false,`used` numeric DEFAULT false,`created_at` datetime,`expiration` datetime, `tags` text,CONSTRAINT `fk_pre_auth_keys_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE); CREATE TABLE sqlite_sequence(name,seq); CREATE TABLE `api_keys` (`id` integer PRIMARY KEY AUTOINCREMENT,`prefix` text,`hash` blob,`created_at` datetime,`expiration` datetime,`last_seen` datetime); CREATE TABLE IF NOT EXISTS "nodes" (`id` integer PRIMARY KEY AUTOINCREMENT,`machine_key` text,`node_key` text,`disco_key` text,`endpoints` text,`host_info` text,`ipv4` text,`ipv6` text,`hostname` text,`given_name` varchar(63),`user_id` integer,`register_method` text,`forced_tags` text,`auth_key_id` integer,`last_seen` datetime,`expiry` datetime,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime, `approved_routes` text,CONSTRAINT `fk_nodes_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,CONSTRAINT `fk_nodes_auth_key` FOREIGN KEY (`auth_key_id`) REFERENCES `pre_auth_keys`(`id`) ON DELETE SET NULL); CREATE TABLE `policies` (`id` integer PRIMARY KEY AUTOINCREMENT,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`data` text); CREATE TABLE IF NOT EXISTS "users" (`id` integer PRIMARY KEY AUTOINCREMENT,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`name` text, `display_name` text, `email` text, `provider_identifier` text, `provider` text, `profile_pic_url` text); CREATE UNIQUE INDEX `idx_api_keys_prefix` ON `api_keys`(`prefix`); CREATE INDEX `idx_policies_deleted_at` ON `policies`(`deleted_at`); CREATE INDEX `idx_users_deleted_at` ON `users`(`deleted_at`); CREATE UNIQUE INDEX idx_provider_identifier ON users (provider_identifier) WHERE provider_identifier IS NOT NULL; CREATE UNIQUE INDEX idx_name_provider_identifier ON users (name,provider_identifier); CREATE UNIQUE INDEX idx_name_no_provider_identifier ON users (name) WHERE provider_identifier IS NULL; ```
adam commented 2025-12-29 02:28:31 +01:00
Author
Owner
Copy Link

@nblock commented on GitHub (Nov 9, 2025):

Could you please send us a copy of your 0.26.1 database? My e-mail can be found in the commit logs, kradalby's in his github profile. Thank you.

@nblock commented on GitHub (Nov 9, 2025): Could you please send us a copy of your 0.26.1 database? My e-mail can be found in the commit logs, kradalby's in his github profile. Thank you.
adam commented 2025-12-29 02:28:31 +01:00
Author
Owner
Copy Link

@ryanlim commented on GitHub (Nov 11, 2025):

After debugging this with @nblock, it turns out that there were duplicate entries in the nodes table. Removing them manually from the database allowed the migration to continue successfully.

@ryanlim commented on GitHub (Nov 11, 2025): After debugging this with @nblock, it turns out that there were duplicate entries in the nodes table. Removing them manually from the database allowed the migration to continue successfully.
adam commented 2025-12-29 02:28:31 +01:00
Author
Owner
Copy Link

@nblock commented on GitHub (Nov 11, 2025):

The duplicate nodes likely appeared after migrating from CLI registration to OIDC. The following query can be used to find such duplicate nodes:

 select id, hostname, given_name, machine_key, node_key from nodes group by machine_key, node_key having count(*) > 1;

Then, delete duplicate rows with the oldest last_seen entry:

delete from nodes where given_name='NODE_NAME' and last_seen='2025-09-30 15:35:31.660896604+00:00';
@nblock commented on GitHub (Nov 11, 2025): The duplicate nodes likely appeared after migrating from CLI registration to OIDC. The following query can be used to find such duplicate nodes: ```sql select id, hostname, given_name, machine_key, node_key from nodes group by machine_key, node_key having count(*) > 1; ``` Then, delete duplicate rows with the oldest `last_seen` entry: ```sql delete from nodes where given_name='NODE_NAME' and last_seen='2025-09-30 15:35:31.660896604+00:00'; ```
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
CLI
DERP
DNS
Nix
OIDC
SSH
bug
database
documentation
duplicate
enhancement
faq
good first issue
grants
help wanted
might-come
needs design doc
needs investigation
no-stale-bot
out of scope
performance
policy 📝
pull-request
Mirrored from GitHub Pull Request
 question
regression
routes
stale
tags
tailscale-feature-gap
well described ❤️
wontfix
No Label bug question
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/headscale#1139
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?