Upgrade to 1.16.2 failed , due to database migration issue

[mscherer]

Gitea Version

1.16.2

Git Version

git-2.35.1-1.fc35.x86_64

Operating System

Fedora

How are you running Gitea?

I am using the upstream binary from github on amd64.

Database

PostgreSQL

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Description

I just upgraded to 0.16.2 using a script. Upon restarting gitea, the following error message is in the log:

ORM engine initialization attempt #9/10...
PING DATABASE postgres
[SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = $2 [public version] - 3.887384ms
[SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2 AND column_name = $3 [public version id] - 11.158022ms
[SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2 AND column_name = $3 [public version version] - 4.770591ms
[SQL] SELECT "id", "version" FROM "version" WHERE "id"=$1 LIMIT 1 [1] - 1.022646ms
Migration[210]: v208 was completely broken - remigrate
[SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 [public] - 1.358975ms
[SQL] SELECT column_name, column_default, is_nullable, data_type, character_maximum_length, description,
	    CASE WHEN p.contype = 'p' THEN true ELSE false END AS primarykey,
	    CASE WHEN p.contype = 'u' THEN true ELSE false END AS uniquekey
	FROM pg_attribute f
	    JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid
	    LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
	    LEFT JOIN pg_description de ON f.attrelid=de.objoid AND f.attnum=de.objsubid
	    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
	    LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
	    LEFT JOIN pg_class AS g ON p.confrelid = g.oid
	    LEFT JOIN INFORMATION_SCHEMA.COLUMNS s ON s.column_name=f.attname AND c.relname=s.table_name
	WHERE n.nspname= s.table_schema AND c.relkind = 'r'::char AND c.relname = $1 AND s.table_schema = $2 AND f.attnum > 0 ORDER BY f.attnum; [webauthn_credential public] - 17.430342ms
[SQL] SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1 AND schemaname=$2 [webauthn_credential public] - 3.02103ms
[SQL] ALTER TABLE webauthn_credential ALTER COLUMN credential_id TYPE VARCHAR(410) [] - 59.767144ms
[SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = $2 [public u2f_registration] - 1.216782ms
[SQL] SELECT "id", "name", "user_id", "raw", "counter", "created_unix", "updated_unix" FROM "u2f_registration" ORDER BY id LIMIT 50 [] - 1.203239ms
[SQL] BEGIN TRANSACTION [] - 587.856µs
[SQL] SELECT "id", "name", "lower_name", "user_id", "credential_id", "public_key", "attestation_type", "aaguid", "sign_count", "clone_warning", "created_unix", "updated_unix" FROM "webauthn_credential" WHERE (id = $1) AND "id"=$2 LIMIT 1 [3 3] - 1.55455ms
[SQL] INSERT INTO "webauthn_credential" ("id","name","lower_name","user_id","credential_id","public_key","attestation_type","aaguid","sign_count","clone_warning","created_unix","updated_unix") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) RETURNING "id" [3 windgrace windgrace 1 JTBNMQTUS9GJ8010V19V0V3HD1M05JRO1356MNHH8IHK63G12Q42O6V05UH69DIAU76G0SK7KSQFLDVI9JI95N5MSJMQ670S4J2RFDG= [4 59 207 52 44 18 208 10 107 36 59 116 25 7 38 19 36 172 123 163 185 30 180 158 35 198 190 223 68 41 148 152 252 107 27 24 128 245 134 152 56 166 186 112 70 31 52 87 31 232 39 27 133 113 47 9 154 43 172 230 80 239 170 241 230] fido-u2f [] 99 false 1645739712 1645739712] - 1.027492ms
[SQL] ROLLBACK [] - 227.833µs
ORM engine initialization attempt #9/10 failed. Error: migrate: migration[210]: v208 was completely broken - remigrate failed: unable to (re)insert webauthn_credential[3]. Error: pq: duplicate key value violates unique constraint "UQE_webauthn_credential_s"

Screenshots

No response

[mscherer]

Likely related to #18770 and #18771

[mscherer]

And I upgraded from 1.16.1 before (and all the way from 1.14.6)

[mscherer]

So, I guess what happened is that as I removed the keys (I have 2 u2f keys) last time (#18477 (comment)), they were still present in the DB.

I have added the 2 same keys again after the upgrade, so the current status is that:

• webauthn_credential has 2 entries.
• u2f_registration has 2 entries as well.

And so the migration do not seems to take that case in account.

[mscherer]

As I needed my forge to be working, the solution I used was to connect to Postgres, and run:

DELETE FROM u2f_registration WHERE user_id = 1;

But the issue was related to having 2 entries in the u2f_registration table with the same exact name/user as the 2 webauthn entries.

[beedaddy]

Sadly, I can confirm the issue.

[lunny]

So there are also a same name key in the table but with different id from u2f table.

LowerName       string `xorm:"unique(s)"`
UserID          int64  `xorm:"INDEX unique(s)"`