1.22 upgrade migration fail, postgres 16 column not created - cannot revert

[jamiew0w]

Description

postgres 16.3/debian 12

when attempting upgrade:

routers/init.go:73:mustInitCtx() [F] code.gitea.io/gitea/routers/common.InitDBEngine(ctx) failed: migrate: migration[286]: Add support for SHA256 git repositories failed: pq: column "object_format_name" does not exist

when attempting to revert:

2024/06/01 07:30:37 ...ations/migrations.go:628:Migrate() [F] Migration Error: Your database (migration version: 286) is for a newer Gitea, newer database for this old Gitea release (280).
Gitea will exit to keep your database safe and unchanged. Please use the correct Gitea release, do not change the migration version manually (incorrect manual operation may lose data).

Gitea Version

1.22

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

No response

Operating System

debian

How are you running Gitea?

binary release

Database

PostgreSQL

[eeyrjmr]

what version of git do you have
also have you tried using git doctor to perform some repair tasks

[jamiew0w]

what version of git do you have also have you tried using git doctor to perform some repair tasks

Hey @eeyrjmr
I don't use docker - i've updated the issue to specify i run the binary. the git version is 2.39.2 - the most up-to-date version in debian https://packages.debian.org/bookworm/git

[eeyrjmr]

Sorry, I mean to write gitea doctor

gitea doctor check --all.

[jamiew0w]

[9] Check Database Version
 - [I] Expected database version: 299
 - [E] Error: migrate: Current database version 286 is not equal to the expected version 299. Please run "gitea [--config /path/to/app.ini] migrate" to update the database version during ensure up to date
ERROR

[10] Check consistency of database
 - [E] Model version on the database does not match the current Gitea version. Model consistency will not be checked until the database is upgraded
ERROR

[15] Check if hook files are up-to-date and executable
 - [E] Errors noted whilst checking delegate hooks.
ERROR

[17] Check old archives
 - [I] 0 old archives in repository need to be deleted
ERROR

[18] Check that all git repositories have receive.advertisePushOptions set to true
 - [E] Unable to EnablePushOptions: pq: column "default_wiki_branch" does not exist
ERROR

[21] Recalculate merge bases
 - [I] All 0 PRs in 0 repos have a correct mergebase
ERROR

[22] Synchronize repo HEADs
 - [E] Error when fixing repo HEADs: pq: column "default_wiki_branch" does not exist
 - [I] All 0 repos have their HEADs in the correct state
ERROR

[23] Check git-daemon-export-ok files
 - [E] Unable to checkDaemonExport: pq: column "default_wiki_branch" does not exist
ERROR

[24] Check commit-graphs
 - [E] Unable to checkCommitGraph: pq: column "default_wiki_branch" does not exist
ERROR

NB: gitea --config app.ini migrate also fails:

2024/06/01 11:54:41 cmd/migrate.go:33:runMigrate() [I] AppPath: /usr/local/bin/gitea
2024/06/01 11:54:41 cmd/migrate.go:34:runMigrate() [I] AppWorkPath: /var/lib/gitea
2024/06/01 11:54:41 cmd/migrate.go:35:runMigrate() [I] Custom path: /var/lib/gitea/custom
2024/06/01 11:54:41 cmd/migrate.go:36:runMigrate() [I] Log path: /var/lib/gitea/log
2024/06/01 11:54:41 cmd/migrate.go:37:runMigrate() [I] Configuration file: /etc/gitea/app.ini
2024/06/01 11:54:41 ...2@v2.27.1/command.go:272:Run() [I] PING DATABASE postgres
2024/06/01 11:54:42 ...ations/migrations.go:688:Migrate() [I] Migration[286]: Add support for SHA256 git repositories
2024/06/01 11:54:42 cmd/migrate.go:40:runMigrate() [F] Failed to initialize ORM engine: migrate: migration[286]: Add support for SHA256 git repositories failed: pq: column "object_format_name" does not exist

I should point out too that obviously everything was running fine prior and I update gitea pretty frequently. No config changes have been made, though we did migrate from mysql -> postgres about 2 or 3 months ago.

[eeyrjmr]

Could you run the doctor command with 1.22
Aspects of the migration has completed so there is no way back (besides backups).

gitea doctor check --all --fix

Also when you say you converted mysql to postgress 2-3 months ago, had you upgraded gitea between then?
I wonder if the changing dB wasn't compete .

Gitea doctor also has a command to recreate tables

[jamiew0w]

Could you run the doctor command with 1.22

I was fairly sure that was with 1.22, but I might have swapped it back and forgotten, here's the new output - though I think it's the same (without anything omitted this time)

[1] Garbage collect LFS
 - [I] Found 0 (0 B) LFS file(s)
OK

[2] Check paths and basic configuration
 - [I] Configuration File Path:    "/etc/gitea/app.ini"
 - [I] Repository Root Path:       "/var/lib/gitea/data/gitea-repositories"
 - [I] Data Root Path:             "/var/lib/gitea/data"
 - [I] Custom File Root Path:      "/var/lib/gitea/custom"
 - [I] Work directory:             "/var/lib/gitea"
 - [I] Log Root Path:              "/var/lib/gitea/log"
OK

[3] Check if there are orphaned archives in storage
 - [I] Found 0 (0 B) repo archive(s)
OK

[4] Check if there are orphaned attachments in storage
 - [I] Found 1 (16 KiB) attachment(s)
OK

[5] Check if there are orphaned avatars in storage
 - [I] Found 13 (315 KiB) avatar(s)
 - [I] Found 0 (0 B) repo avatar(s)
OK

[6] Check if there are orphaned lfs files in storage
 - [I] Found 0 (0 B) LFS file(s)
OK

[7] Check if there are orphaned package blobs in storage
 - [I] Found 0 (0 B) package blob(s)
OK

[8] Check if there are orphaned storage files
 - [I] Found 1 (16 KiB) attachment(s)
 - [I] Found 0 (0 B) LFS file(s)
 - [I] Found 13 (315 KiB) avatar(s)
 - [I] Found 0 (0 B) repo avatar(s)
 - [I] Found 0 (0 B) repo archive(s)
 - [I] Found 0 (0 B) package blob(s)
OK

[9] Check Database Version
 - [I] Expected database version: 299
 - [W] Got Error: migrate: Current database version 286 is not equal to the expected version 299. Please run "gitea [--config /path/to/app.ini] migrate" to update the database version during ensure up to date
 - [W] Attempting to migrate to the latest DB version to fix this.
 - [E] Error: migrate: migration[286]: Add support for SHA256 git repositories failed: pq: column "object_format_name" does not exist during migration
ERROR

[10] Check consistency of database
 - [E] Model version on the database does not match the current Gitea version. Model consistency will not be checked until the database is upgraded
ERROR

[11] Check if user with wrong type exist
OK

[12] Check if OpenSSH authorized_keys file is up-to-date
OK

[13] Deleted all content related to orphaned repos
OK

[14] Check if SCRIPT_TYPE is available
 - [I] ScriptType bash is on the current PATH at /usr/bin/bash
OK

[15] Check if hook files are up-to-date and executable
 - [E] Errors noted whilst checking delegate hooks.
ERROR

[16] Recalculate Stars number for all user
 - [I] Updated User Stars numbers.
OK

[17] Check old archives
 - [I] 0 / 0 old archives in repository deleted
ERROR

[18] Check that all git repositories have receive.advertisePushOptions set to true
 - [E] Unable to EnablePushOptions: pq: column "default_wiki_branch" does not exist
ERROR

[19] Check for incorrectly dumped repo_units (See #16961)
 - [I] Fixed 0 broken repo_units
OK

[20] Check for incorrect can_create_org_repo for org owner teams
 - [I] Fixed 0 teams with incorrect can_create_org_repo
OK

[21] Recalculate merge bases
 - [I] 0 PR mergebases updated of 0 PRs total in 0 repos
ERROR

[22] Synchronize repo HEADs
 - [E] Error when fixing repo HEADs: pq: column "default_wiki_branch" does not exist
 - [I] Out of 0 repos, HEADs for 0 are now fixed and HEADS for 0 are still broken
ERROR

[23] Check git-daemon-export-ok files
 - [E] Unable to checkDaemonExport: pq: column "default_wiki_branch" does not exist
ERROR

[24] Check commit-graphs
 - [E] Unable to checkCommitGraph: pq: column "default_wiki_branch" does not exist
ERROR

[25] Check if users has an valid email address
 - [I] All users have a valid e-mail.
OK

[26] Check if users have a valid username
 - [I] All users have a valid username.
OK

All done (checks: 26).

Also when you say you converted mysql to postgress 2-3 months ago, had you upgraded gitea between then?

There's a slim chance we converted mysql > postgres right after the 1.21.11 release which would explain why we haven't had issues since. I don't recall any issues with that migration at the time either.

Gitea doctor also has a command to recreate tables

gitea doctor recreate-table --config app.ini
2024/06/01 12:15:37 ...2@v2.27.1/command.go:272:Run() [I] PING DATABASE postgres
Command error: migrate: Current database version 286 is not equal to the expected version 299. Please run "gitea [--config /path/to/app.ini] migrate" to update the database version

gitea migrate:

2024/06/01 12:16:05 cmd/migrate.go:40:runMigrate() [F] Failed to initialize ORM engine: migrate: migration[286]: Add support for SHA256 git repositories failed: pq: column "object_format_name" does not exist

I'm happy to leave it "broken" as is for a few hours if you want me to test a few things, there's no urgency and I'm happy to do whatever is needed.

[eeyrjmr]

gitea doctor recreate-table --config app.ini
2024/06/01 12:15:37 ...2@v2.27.1/command.go:272:Run() [I] PING DATABASE postgres
Command error: migrate: Current database version 286 is not equal to the expected version 299. Please run "gitea [--config /path/to/app.ini] migrate" to update the database version

This is the correct command to attempt to recreate the tables but you appear to be using the previous version again.

[jamiew0w]

This is the correct command to attempt to recreate the tables but you appear to be using the previous version again.

Nah, it's 1.22. To be sure I set chmod -x on the old binaries, plus specified the newly downloaded release from github: EG ./gitea just in case. Here's the output showing the version number too:

gitea:/etc/gitea$ ./gitea -v
Gitea version 1.22.0 built with GNU Make 4.3, go1.22.3 : bindata, sqlite, sqlite_unlock_notify
gitea:/etc/gitea$ ./gitea doctor recreate-table --config app.ini
2024/06/01 12:35:55 ...2@v2.27.1/command.go:272:Run() [I] PING DATABASE postgres
Command error: migrate: Current database version 286 is not equal to the expected version 299. Please run "gitea [--config /path/to/app.ini] migrate" to update the database version

[jamiew0w]

have you accidently tried the 1.23-dev builds? reading the error the current database version is 299 but you are running a gitea version that can only talk 286

Grabbed it straight from here: https://github.com/go-gitea/gitea/releases/tag/v1.22.0

gitea-1.22.0-linux-amd64

As a note, if we have to start over it's not the end of the world. I'm assuming that part of the migration didn't work correctly, or it wasn't performed correctly. Spinning up a new VM and copying stuff over would be a pain, but that's life sometimes :)
I just wanna be mindful of wasting your guys time (thanks for your help thus far too!)

edit; I could always restore and test with the 1.22.0-rc1 too, though I doubt there'd be much difference. It doesn't look like anyone else has reported this issue too, so it must be a mistake on our end.

[eeyrjmr]

Command error: migrate: Current database version 286 is not equal to the expected version 299. Please run "gitea [--config /path/to/app.ini] migrate" to update the database version

so this is saying the present db is 286 (ie 1.21) and its trying to migrate to 299 (ie 1.22)

gitea/models/migrations/migrations.go

Lines 546 to 590 in ab458ce

	// Gitea 1.21.0 ends at 280
	// v280 -> v281
	NewMigration("Rename user themes", v1_22.RenameUserThemes),
	// v281 -> v282
	NewMigration("Add auth_token table", v1_22.CreateAuthTokenTable),
	// v282 -> v283
	NewMigration("Add Index to pull_auto_merge.doer_id", v1_22.AddIndexToPullAutoMergeDoerID),
	// v283 -> v284
	NewMigration("Add combined Index to issue_user.uid and issue_id", v1_22.AddCombinedIndexToIssueUser),
	// v284 -> v285
	NewMigration("Add ignore stale approval column on branch table", v1_22.AddIgnoreStaleApprovalsColumnToProtectedBranchTable),
	// v285 -> v286
	NewMigration("Add PreviousDuration to ActionRun", v1_22.AddPreviousDurationToActionRun),
	// v286 -> v287
	NewMigration("Add support for SHA256 git repositories", v1_22.AdjustDBForSha256),
	// v287 -> v288
	NewMigration("Use Slug instead of ID for Badges", v1_22.UseSlugInsteadOfIDForBadges),
	// v288 -> v289
	NewMigration("Add user_blocking table", v1_22.AddUserBlockingTable),
	// v289 -> v290
	NewMigration("Add default_wiki_branch to repository table", v1_22.AddDefaultWikiBranch),
	// v290 -> v291
	NewMigration("Add PayloadVersion to HookTask", v1_22.AddPayloadVersionToHookTaskTable),
	// v291 -> v292
	NewMigration("Add Index to attachment.comment_id", v1_22.AddCommentIDIndexofAttachment),
	// v292 -> v293
	NewMigration("Ensure every project has exactly one default column - No Op", noopMigration),
	// v293 -> v294
	NewMigration("Ensure every project has exactly one default column", v1_22.CheckProjectColumnsConsistency),
	// Gitea 1.22.0-rc0 ends at 294
	// v294 -> v295
	NewMigration("Add unique index for project issue table", v1_22.AddUniqueIndexForProjectIssue),
	// v295 -> v296
	NewMigration("Add commit status summary table", v1_22.AddCommitStatusSummary),
	// v296 -> v297
	NewMigration("Add missing field of commit status summary table", v1_22.AddCommitStatusSummary2),
	// v297 -> v298
	NewMigration("Add everyone_access_mode for repo_unit", v1_22.AddRepoUnitEveryoneAccessMode),
	// v298 -> v299
	NewMigration("Drop wrongly created table o_auth2_application", v1_22.DropWronglyCreatedTable),
	// Gitea 1.22.0-rc1 ends at 299

but aspects of the db are inconsistent with itself so it can't complete migration.

You the tried to run your original (earlier) version and hit
2024/06/01 07:30:37 ...ations/migrations.go:628:Migrate() [F] Migration Error: Your database (migration version: 286) is for a newer Gitea, newer database for this old Gitea release (280).

and 280 is 1.21

so the migrator replays the upgrade process v280 -> v281 -> v282 ... to ensure all steps are completed. Something failed for you and it was stuck at v286 -> v287, which is the AdjustDBForSha256, which would align with the error message you are seeing.

**NewMigration("Add support for SHA256 git repositories", v1_22.AdjustDBForSha256),**

might need a maintainers help with accessing the db and adjusting aspects of the db to allow the migration to continue.

[eeyrjmr]

https://github.com/go-gitea/gitea/blob/ab458ce10be59669c810ba43af41f2ba2e72ea5b/models/migrations/v1_22/v286.go#L84C1-L97C2

This is the migration code and the likely error part...

[lunny]

The only way I can imagine to fix it is below but I cannot reproduce it in my local macOS + postgres 16(docker based)

-_, err := x.Exec("UPDATE repository set object_format_name = 'sha1' WHERE object_format_name = '' or object_format_name IS NULL")
+_, err := x.Exec("UPDATE repository set `object_format_name` = 'sha1' WHERE `object_format_name` = '' or `object_format_name` IS NULL")