github-starred/gitea
2
0
Fork 0
mirror of https://github.com/go-gitea/gitea.git synced 2026-03-19 14:42:41 -05:00
Code Issues 2.6k Packages Projects Releases 100 Wiki Activity

error 500 on creating a repo, about repository_pkey not unique after update #6871

New Issue
Closed
opened 2025-11-02 07:09:34 -06:00 by GiteaMirror · 6 comments
GiteaMirror commented 2025-11-02 07:09:34 -06:00
Owner
Copy Link

Originally created by @thepra on GitHub (Feb 15, 2021).

  • Gitea version (or commit ref):1.13.2
  • Git version:2.25.1
  • Operating system:Ubuntu 20.04
    Binary downloaded from gitea and replaced the old version(stopping gitea before this)

I'm running Gitea with systemd and it's gitea.service

  • Database (use [x]):
    • PostgreSQL
    • MySQL
    • MSSQL
    • SQLite
  • Can you reproduce the bug at https://try.gitea.io:
    • Yes (provide example URL)
    • No
  • Log gist:

2021/02/15 18:23:43 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] INSERT INTO "repository" ("owner_id","owner_name","lower_name","name","description","website","original_service_type","original_url","default_branch","num_watches","num_stars","num_forks","num_issues","num_closed_issues","num_pulls","num_closed_pulls","num_milestones","num_closed_milestones","num_projects","num_closed_projects","is_private","is_empty","is_archived","is_mirror","status","is_fork","fork_id","is_template","template_id","size","is_fsck_enabled","close_issues_via_commit_in_any_branch","topics","trust_model","avatar","created_unix","updated_unix") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37) RETURNING "id" [1 thepra thepradevhr thepradevhr geeg 0 0 0 0 0 0 0 0 0 0 0 0 true false false false 0 false 0 false 0 0 true false null default 1613409823 1613409823] - 1.131673ms
2021/02/15 18:23:43 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] ROLLBACK [] - 122.501µs
2021/02/15 18:23:43 routers/repo/repo.go:183:handleCreateError() [E] CreatePost: pq: duplicate key value violates unique constraint "repository_pkey"

After fixing thanks to @zeripath in https://github.com/go-gitea/gitea/issues/14539#issuecomment-779396983 I found that another table has a similar issue through the logs:

2021/02/15 20:41:56 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] INSERT INTO "repo_unit" ("repo_id","type","config","created_unix") VALUES ($1, $2, $3, $4),($5, $6, $7, $8),($9, $10, $11, $12),($13, $14, $15, $16),($17, $18, $19, $20),($21, $22, $23, $24) [29 UnitTypeCode 1613418116 29 UnitTypeIssues {"EnableTimetracker":true,"AllowOnlyContributorsToTrackTime":true,"EnableDependencies":true} 1613418116 29 UnitTypePullRequests {"IgnoreWhitespaceConflicts":false,"AllowMerge":true,"AllowRebase":true,"AllowRebaseMerge":true,"AllowSquash":true} 1613418116 29 UnitTypeReleases 1613418116 29 UnitTypeWiki 1613418116 29 UnitTypeProjects 1613418116] - 516.059µs
2021/02/15 20:41:56 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] ROLLBACK [] - 154.286µs
2021/02/15 20:41:56 routers/repo/repo.go:183:handleCreateError() [E] CreatePost: pq: duplicate key value violates unique constraint "repo_unit_pkey"

Description

After updating to 1.13.2 through stopping gitea, replacing the gitea binary and starting it again I notices that creating a new repository is impossible because the primary keys indices are not aligned, giving the wrong generated ID for the records, it's not only the repository table but also the next called one repo_unit.
At this point what can I do to sync all the indices of the tables?
Is there any command to apply this fix?

Originally created by @thepra on GitHub (Feb 15, 2021). - Gitea version (or commit ref):1.13.2 - Git version:2.25.1 - Operating system:Ubuntu 20.04 Binary downloaded from gitea and replaced the old version(stopping gitea before this) <!-- Please include information on whether you built gitea yourself, used one of our downloads or are using some other package --> <!-- Please also tell us how you are running gitea, e.g. if it is being run from docker, a command-line, systemd etc. ---> I'm running Gitea with systemd and it's `gitea.service` <!-- If you are using a package or systemd tell us what distribution you are using --> - Database (use `[x]`): - [x] PostgreSQL - [ ] MySQL - [ ] MSSQL - [ ] SQLite - Can you reproduce the bug at https://try.gitea.io: - [ ] Yes (provide example URL) - [x] No - Log gist: > 2021/02/15 18:23:43 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] INSERT INTO "repository" ("owner_id","owner_name","lower_name","name","description","website","original_service_type","original_url","default_branch","num_watches","num_stars","num_forks","num_issues","num_closed_issues","num_pulls","num_closed_pulls","num_milestones","num_closed_milestones","num_projects","num_closed_projects","is_private","is_empty","is_archived","is_mirror","status","is_fork","fork_id","is_template","template_id","size","is_fsck_enabled","close_issues_via_commit_in_any_branch","topics","trust_model","avatar","created_unix","updated_unix") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37) RETURNING "id" [1 thepra thepradevhr thepradevhr geeg 0 0 0 0 0 0 0 0 0 0 0 0 true false false false 0 false 0 false 0 0 true false null default 1613409823 1613409823] - 1.131673ms > 2021/02/15 18:23:43 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] ROLLBACK [] - 122.501µs > 2021/02/15 18:23:43 routers/repo/repo.go:183:handleCreateError() [E] CreatePost: pq: duplicate key value violates unique constraint "repository_pkey" After fixing thanks to @zeripath in https://github.com/go-gitea/gitea/issues/14539#issuecomment-779396983 I found that another table has a similar issue through the logs: > 2021/02/15 20:41:56 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] INSERT INTO "repo_unit" ("repo_id","type","config","created_unix") VALUES ($1, $2, $3, $4),($5, $6, $7, $8),($9, $10, $11, $12),($13, $14, $15, $16),($17, $18, $19, $20),($21, $22, $23, $24) [29 UnitTypeCode <nil> 1613418116 29 UnitTypeIssues {"EnableTimetracker":true,"AllowOnlyContributorsToTrackTime":true,"EnableDependencies":true} 1613418116 29 UnitTypePullRequests {"IgnoreWhitespaceConflicts":false,"AllowMerge":true,"AllowRebase":true,"AllowRebaseMerge":true,"AllowSquash":true} 1613418116 29 UnitTypeReleases <nil> 1613418116 29 UnitTypeWiki <nil> 1613418116 29 UnitTypeProjects <nil> 1613418116] - 516.059µs > 2021/02/15 20:41:56 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] ROLLBACK [] - 154.286µs > 2021/02/15 20:41:56 routers/repo/repo.go:183:handleCreateError() [E] CreatePost: pq: duplicate key value violates unique constraint "repo_unit_pkey" <!-- It really is important to provide pertinent logs --> <!-- Please read https://docs.gitea.io/en-us/logging-configuration/#debugging-problems --> <!-- In addition, if your problem relates to git commands set `RUN_MODE=dev` at the top of app.ini --> ## Description After updating to 1.13.2 through stopping gitea, replacing the gitea binary and starting it again I notices that creating a new repository is impossible because the primary keys indices are not aligned, giving the wrong generated ID for the records, it's not only the `repository` table but also the next called one `repo_unit`. At this point what can I do to sync all the indices of the tables? Is there any command to apply this fix?
GiteaMirror commented 2025-11-02 07:09:35 -06:00
Author
Owner
Copy Link

@zeripath commented on GitHub (Feb 15, 2021):

Your problem is that your postgres db seems to have the incorrect values in its sequences.

You'll need to run something like:

-- Get Max ID from table
SELECT MAX(id) FROM table;

-- Get Next ID from table
SELECT nextval('table_id_seq');

-- Set Next ID Value to MAX ID
SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));

for each table in postgres.

There is no command to do this.

@zeripath commented on GitHub (Feb 15, 2021): Your problem is that your postgres db seems to have the incorrect values in its sequences. You'll need to run something like: ``` -- Get Max ID from table SELECT MAX(id) FROM table; -- Get Next ID from table SELECT nextval('table_id_seq'); -- Set Next ID Value to MAX ID SELECT setval('table_id_seq', (SELECT MAX(id) FROM table)); ``` for each table in postgres. There is no command to do this.
GiteaMirror commented 2025-11-02 07:09:35 -06:00
Author
Owner
Copy Link

@zeripath commented on GitHub (Feb 15, 2021):

Have you run gitea dump at some point and then reimported the from that dump?

My suspicion is that the dump is not setting the sequences up correctly.

@zeripath commented on GitHub (Feb 15, 2021): Have you run gitea dump at some point and then reimported the from that dump? My suspicion is that the dump is not setting the sequences up correctly.
GiteaMirror commented 2025-11-02 07:09:35 -06:00
Author
Owner
Copy Link

@thepra commented on GitHub (Feb 15, 2021):

Have you run gitea dump at some point and then reimported the from that dump?

No, I didn't reimport any dump yet, just made them for backup purposes.

So ok, i'll go for each table with such indexes(Webmin helps here is seeing them) and update the MAX ID.
@zeripath I guess when it comes to binary updates there's nothing really checking/figuring this things out before/after any eventual migration applied(if there's any)?

@thepra commented on GitHub (Feb 15, 2021): > Have you run gitea dump at some point and then reimported the from that dump? No, I didn't reimport any dump yet, just made them for backup purposes. So ok, i'll go for each table with such indexes(Webmin helps here is seeing them) and update the MAX ID. @zeripath I guess when it comes to binary updates there's nothing really checking/figuring this things out before/after any eventual migration applied(if there's any)?
GiteaMirror commented 2025-11-02 07:09:35 -06:00
Author
Owner
Copy Link

@zeripath commented on GitHub (Feb 15, 2021):

I'm really not sure what's caused the sequences to disappear like this - maybe they were in the wrong schema?

gitea doctor recreate-table might help?

@zeripath commented on GitHub (Feb 15, 2021): I'm really not sure what's caused the sequences to disappear like this - maybe they were in the wrong schema? `gitea doctor recreate-table` might help?
GiteaMirror commented 2025-11-02 07:09:36 -06:00
Author
Owner
Copy Link

@thepra commented on GitHub (Feb 15, 2021):

SELECT setval('version_id_seq', (SELECT MAX(id) FROM version));
SELECT setval('public_key_id_seq', (SELECT MAX(id) FROM public_key));
SELECT setval('access_token_id_seq', (SELECT MAX(id) FROM access_token));
SELECT setval('repository_id_seq', (SELECT MAX(id) FROM repository));
SELECT setval('deploy_key_id_seq', (SELECT MAX(id) FROM deploy_key));
SELECT setval('collaboration_id_seq', (SELECT MAX(id) FROM collaboration));
SELECT setval('access_id_seq', (SELECT MAX(id) FROM access));
SELECT setval('upload_id_seq', (SELECT MAX(id) FROM upload));
SELECT setval('watch_id_seq', (SELECT MAX(id) FROM watch));
SELECT setval('star_id_seq', (SELECT MAX(id) FROM star));
SELECT setval('follow_id_seq', (SELECT MAX(id) FROM follow));
SELECT setval('action_id_seq', (SELECT MAX(id) FROM action));
SELECT setval('issue_id_seq', (SELECT MAX(id) FROM issue));
SELECT setval('pull_request_id_seq', (SELECT MAX(id) FROM pull_request));
SELECT setval('comment_id_seq', (SELECT MAX(id) FROM comment));
SELECT setval('attachment_id_seq', (SELECT MAX(id) FROM attachment));
SELECT setval('label_id_seq', (SELECT MAX(id) FROM label));
SELECT setval('issue_label_id_seq', (SELECT MAX(id) FROM issue_label));
SELECT setval('milestone_id_seq', (SELECT MAX(id) FROM milestone));
SELECT setval('mirror_id_seq', (SELECT MAX(id) FROM mirror));
SELECT setval('release_id_seq', (SELECT MAX(id) FROM release));
SELECT setval('login_source_id_seq', (SELECT MAX(id) FROM login_source));
SELECT setval('webhook_id_seq', (SELECT MAX(id) FROM webhook));
SELECT setval('hook_task_id_seq', (SELECT MAX(id) FROM hook_task));
SELECT setval('team_id_seq', (SELECT MAX(id) FROM team));
SELECT setval('org_user_id_seq', (SELECT MAX(id) FROM org_user));
SELECT setval('team_user_id_seq', (SELECT MAX(id) FROM team_user));
SELECT setval('team_repo_id_seq', (SELECT MAX(id) FROM team_repo));
SELECT setval('notice_id_seq', (SELECT MAX(id) FROM notice));
SELECT setval('email_address_id_seq', (SELECT MAX(id) FROM email_address));
SELECT setval('notification_id_seq', (SELECT MAX(id) FROM notification));
SELECT setval('issue_user_id_seq', (SELECT MAX(id) FROM issue_user));
SELECT setval('lfs_meta_object_id_seq', (SELECT MAX(id) FROM lfs_meta_object));
SELECT setval('two_factor_id_seq', (SELECT MAX(id) FROM two_factor));
SELECT setval('gpg_key_id_seq', (SELECT MAX(id) FROM gpg_key));
SELECT setval('repo_unit_id_seq', (SELECT MAX(id) FROM repo_unit));
SELECT setval('repo_redirect_id_seq', (SELECT MAX(id) FROM repo_redirect));
SELECT setval('protected_branch_id_seq', (SELECT MAX(id) FROM protected_branch));
SELECT setval('user_open_id_id_seq', (SELECT MAX(id) FROM user_open_id));
SELECT setval('issue_watch_id_seq', (SELECT MAX(id) FROM issue_watch));
SELECT setval('commit_status_id_seq', (SELECT MAX(id) FROM commit_status));
SELECT setval('stopwatch_id_seq', (SELECT MAX(id) FROM stopwatch));
SELECT setval('tracked_time_id_seq', (SELECT MAX(id) FROM tracked_time));
SELECT setval('deleted_branch_id_seq', (SELECT MAX(id) FROM deleted_branch));
SELECT setval('repo_indexer_status_id_seq', (SELECT MAX(id) FROM repo_indexer_status));
SELECT setval('issue_dependency_id_seq', (SELECT MAX(id) FROM issue_dependency));
SELECT setval('lfs_lock_id_seq', (SELECT MAX(id) FROM lfs_lock));
SELECT setval('reaction_id_seq', (SELECT MAX(id) FROM reaction));
SELECT setval('issue_assignees_id_seq', (SELECT MAX(id) FROM issue_assignees));
SELECT setval('u2f_registration_id_seq', (SELECT MAX(id) FROM u2f_registration));
SELECT setval('team_unit_id_seq', (SELECT MAX(id) FROM team_unit));
SELECT setval('review_id_seq', (SELECT MAX(id) FROM review));
SELECT setval('oauth2_application_id_seq', (SELECT MAX(id) FROM oauth2_application));
SELECT setval('oauth2_authorization_code_id_seq', (SELECT MAX(id) FROM oauth2_authorization_code));
SELECT setval('oauth2_grant_id_seq', (SELECT MAX(id) FROM oauth2_grant));
SELECT setval('task_id_seq', (SELECT MAX(id) FROM task));
SELECT setval('language_stat_id_seq', (SELECT MAX(id) FROM language_stat));
SELECT setval('project_id_seq', (SELECT MAX(id) FROM project));
SELECT setval('project_issue_id_seq', (SELECT MAX(id) FROM project_issue));
SELECT setval('project_board_id_seq', (SELECT MAX(id) FROM project_board));
SELECT setval('topic_id_seq', (SELECT MAX(id) FROM topic));

I'm gonna share the queries in case someone needs them.

I don't know if it would help, I did already run all the sets as suggested and I've no more issues, so I'm closing the issue.

@thepra commented on GitHub (Feb 15, 2021): ``` SELECT setval('version_id_seq', (SELECT MAX(id) FROM version)); SELECT setval('public_key_id_seq', (SELECT MAX(id) FROM public_key)); SELECT setval('access_token_id_seq', (SELECT MAX(id) FROM access_token)); SELECT setval('repository_id_seq', (SELECT MAX(id) FROM repository)); SELECT setval('deploy_key_id_seq', (SELECT MAX(id) FROM deploy_key)); SELECT setval('collaboration_id_seq', (SELECT MAX(id) FROM collaboration)); SELECT setval('access_id_seq', (SELECT MAX(id) FROM access)); SELECT setval('upload_id_seq', (SELECT MAX(id) FROM upload)); SELECT setval('watch_id_seq', (SELECT MAX(id) FROM watch)); SELECT setval('star_id_seq', (SELECT MAX(id) FROM star)); SELECT setval('follow_id_seq', (SELECT MAX(id) FROM follow)); SELECT setval('action_id_seq', (SELECT MAX(id) FROM action)); SELECT setval('issue_id_seq', (SELECT MAX(id) FROM issue)); SELECT setval('pull_request_id_seq', (SELECT MAX(id) FROM pull_request)); SELECT setval('comment_id_seq', (SELECT MAX(id) FROM comment)); SELECT setval('attachment_id_seq', (SELECT MAX(id) FROM attachment)); SELECT setval('label_id_seq', (SELECT MAX(id) FROM label)); SELECT setval('issue_label_id_seq', (SELECT MAX(id) FROM issue_label)); SELECT setval('milestone_id_seq', (SELECT MAX(id) FROM milestone)); SELECT setval('mirror_id_seq', (SELECT MAX(id) FROM mirror)); SELECT setval('release_id_seq', (SELECT MAX(id) FROM release)); SELECT setval('login_source_id_seq', (SELECT MAX(id) FROM login_source)); SELECT setval('webhook_id_seq', (SELECT MAX(id) FROM webhook)); SELECT setval('hook_task_id_seq', (SELECT MAX(id) FROM hook_task)); SELECT setval('team_id_seq', (SELECT MAX(id) FROM team)); SELECT setval('org_user_id_seq', (SELECT MAX(id) FROM org_user)); SELECT setval('team_user_id_seq', (SELECT MAX(id) FROM team_user)); SELECT setval('team_repo_id_seq', (SELECT MAX(id) FROM team_repo)); SELECT setval('notice_id_seq', (SELECT MAX(id) FROM notice)); SELECT setval('email_address_id_seq', (SELECT MAX(id) FROM email_address)); SELECT setval('notification_id_seq', (SELECT MAX(id) FROM notification)); SELECT setval('issue_user_id_seq', (SELECT MAX(id) FROM issue_user)); SELECT setval('lfs_meta_object_id_seq', (SELECT MAX(id) FROM lfs_meta_object)); SELECT setval('two_factor_id_seq', (SELECT MAX(id) FROM two_factor)); SELECT setval('gpg_key_id_seq', (SELECT MAX(id) FROM gpg_key)); SELECT setval('repo_unit_id_seq', (SELECT MAX(id) FROM repo_unit)); SELECT setval('repo_redirect_id_seq', (SELECT MAX(id) FROM repo_redirect)); SELECT setval('protected_branch_id_seq', (SELECT MAX(id) FROM protected_branch)); SELECT setval('user_open_id_id_seq', (SELECT MAX(id) FROM user_open_id)); SELECT setval('issue_watch_id_seq', (SELECT MAX(id) FROM issue_watch)); SELECT setval('commit_status_id_seq', (SELECT MAX(id) FROM commit_status)); SELECT setval('stopwatch_id_seq', (SELECT MAX(id) FROM stopwatch)); SELECT setval('tracked_time_id_seq', (SELECT MAX(id) FROM tracked_time)); SELECT setval('deleted_branch_id_seq', (SELECT MAX(id) FROM deleted_branch)); SELECT setval('repo_indexer_status_id_seq', (SELECT MAX(id) FROM repo_indexer_status)); SELECT setval('issue_dependency_id_seq', (SELECT MAX(id) FROM issue_dependency)); SELECT setval('lfs_lock_id_seq', (SELECT MAX(id) FROM lfs_lock)); SELECT setval('reaction_id_seq', (SELECT MAX(id) FROM reaction)); SELECT setval('issue_assignees_id_seq', (SELECT MAX(id) FROM issue_assignees)); SELECT setval('u2f_registration_id_seq', (SELECT MAX(id) FROM u2f_registration)); SELECT setval('team_unit_id_seq', (SELECT MAX(id) FROM team_unit)); SELECT setval('review_id_seq', (SELECT MAX(id) FROM review)); SELECT setval('oauth2_application_id_seq', (SELECT MAX(id) FROM oauth2_application)); SELECT setval('oauth2_authorization_code_id_seq', (SELECT MAX(id) FROM oauth2_authorization_code)); SELECT setval('oauth2_grant_id_seq', (SELECT MAX(id) FROM oauth2_grant)); SELECT setval('task_id_seq', (SELECT MAX(id) FROM task)); SELECT setval('language_stat_id_seq', (SELECT MAX(id) FROM language_stat)); SELECT setval('project_id_seq', (SELECT MAX(id) FROM project)); SELECT setval('project_issue_id_seq', (SELECT MAX(id) FROM project_issue)); SELECT setval('project_board_id_seq', (SELECT MAX(id) FROM project_board)); SELECT setval('topic_id_seq', (SELECT MAX(id) FROM topic)); ``` I'm gonna share the queries in case someone needs them. I don't know if it would help, I did already run all the `set`s as suggested and I've no more issues, so I'm closing the issue.
GiteaMirror commented 2025-11-02 07:09:36 -06:00
Author
Owner
Copy Link

@zeripath commented on GitHub (Feb 15, 2021):

Thanks!

It would probably be beneficial to add this to a doctor command given you are the second person with this problem.

@zeripath commented on GitHub (Feb 15, 2021): Thanks! It would probably be beneficial to add this to a doctor command given you are the second person with this problem.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
$20
$250
$50
$500
backport/done
💎 Bounty
docs-update-needed
good first issue
hacktoberfest
issue/bounty
issue/confirmed
issue/critical
issue/duplicate
issue/needs-feedback
issue/not-a-bug
issue/regression
issue/stale
issue/workaround
lgtm/need 2
modifies/api
modifies/translation
outdated/backport/v1.18
outdated/theme/markdown
outdated/theme/timetracker
performance/bigrepo
performance/cpu
performance/memory
performance/speed
pr/breaking
proposal/accepted
proposal/rejected
pr/wip
pull-request
Mirrored from GitHub Pull Request
 reviewed/wontfix
💰 Rewarded
skip-changelog
status/blocked
topic/accessibility
topic/api
topic/authentication
topic/build
topic/code-linting
topic/commit-signing
topic/content-rendering
topic/deployment
topic/distribution
topic/federation
topic/gitea-actions
topic/issues
topic/lfs
topic/mobile
topic/moderation
topic/packages
topic/pr
topic/projects
topic/repo
topic/repo-migration
topic/security
topic/theme
topic/ui
topic/ui-interaction
topic/ux
topic/webhooks
topic/wiki
type/bug
type/deprecation
type/docs
type/enhancement
type/feature
type/miscellaneous
type/proposal
type/question
type/refactoring
type/summary
type/testing
type/upstream
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/gitea#6871
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?