github-starred/gitea
2
0
Fork 0
mirror of https://github.com/go-gitea/gitea.git synced 2026-03-12 10:39:38 -05:00
Code Issues 2.6k Packages Projects Releases 100 Wiki Activity

DUMP command line command works wrong #2033

New Issue
Closed
opened 2025-11-02 04:21:45 -06:00 by GiteaMirror · 14 comments
GiteaMirror commented 2025-11-02 04:21:45 -06:00
Owner
Copy Link

Originally created by @e1fueg0 on GitHub (Jul 9, 2018).

The generated SQL code looks like the following:

CREATE TABLE IF NOT EXISTS "public_key" <skipped>;
<skipped>
SELECT setval('table_id_seq', COALESCE((SELECT MAX(id) FROM "public_key"), 1), false);

1.it doesn't create an appropriate sequence.
2.it always passes table_id_seq to setval() regardless of what table it creates.

Originally created by @e1fueg0 on GitHub (Jul 9, 2018). The generated SQL code looks like the following: ```sql CREATE TABLE IF NOT EXISTS "public_key" <skipped>; <skipped> SELECT setval('table_id_seq', COALESCE((SELECT MAX(id) FROM "public_key"), 1), false); ``` 1.it doesn't create an appropriate sequence. 2.it always passes *table_id_seq* to **setval()** regardless of what table it creates.
GiteaMirror added the issue/confirmedtype/bug labels 2025-11-02 04:21:45 -06:00
GiteaMirror commented 2025-11-02 04:21:46 -06:00
Author
Owner
Copy Link

@techknowlogick commented on GitHub (Jul 9, 2018):

@elfuegobiz which DB are you using?

@techknowlogick commented on GitHub (Jul 9, 2018): @elfuegobiz which DB are you using?
GiteaMirror commented 2025-11-02 04:21:46 -06:00
Author
Owner
Copy Link

@e1fueg0 commented on GitHub (Jul 9, 2018):

Sorry, forgot to write this. PostgreSQL 9.2.23 (Centos 7).

@e1fueg0 commented on GitHub (Jul 9, 2018): Sorry, forgot to write this. PostgreSQL 9.2.23 (Centos 7).
GiteaMirror commented 2025-11-02 04:21:46 -06:00
Author
Owner
Copy Link

@e1fueg0 commented on GitHub (Jul 9, 2018):

Looks like sequences are created automatically, so the actual bug is wrong sequence name in setval().
And my Gitea version is 1.4.3.

@e1fueg0 commented on GitHub (Jul 9, 2018): Looks like sequences are created automatically, so the actual bug is wrong sequence name in **setval()**. And my Gitea version is 1.4.3.
GiteaMirror commented 2025-11-02 04:21:46 -06:00
Author
Owner
Copy Link

@e1fueg0 commented on GitHub (Jul 9, 2018):

And this also looks like a bug. I dealt with the sequences at last and restored the dump, but got this in gitea.log:

[...itea/routers/init.go:60 GlobalInit()] [E] Failed to initialize ORM engine: sync database struct error: Unknown col is_active in index is_active of table user, columns []

@e1fueg0 commented on GitHub (Jul 9, 2018): And this also looks like a bug. I dealt with the sequences at last and restored the dump, but got this in gitea.log: > [...itea/routers/init.go:60 GlobalInit()] [E] Failed to initialize ORM engine: sync database struct error: Unknown col is_active in index is_active of table user, columns []
GiteaMirror commented 2025-11-02 04:21:47 -06:00
Author
Owner
Copy Link

@e1fueg0 commented on GitHub (Jul 9, 2018):

And after I let it create a fresh db and restored the dump again, and wanted to import a new repo I got this:

[...routers/repo/repo.go:146 handleCreateError()] [E] MigratePost: pq: duplicate key value violates unique constraint "repository_pkey"
[...routers/repo/repo.go:146 handleCreateError()] [E] MigratePost: pq: duplicate key value violates unique constraint "repo_unit_pkey"

In other words, the dump looks to be completely unusable.

@e1fueg0 commented on GitHub (Jul 9, 2018): And after I let it create a fresh db and restored the dump again, and wanted to import a new repo I got this: > [...routers/repo/repo.go:146 handleCreateError()] [E] MigratePost: pq: duplicate key value violates unique constraint "repository_pkey" > [...routers/repo/repo.go:146 handleCreateError()] [E] MigratePost: pq: duplicate key value violates unique constraint "repo_unit_pkey" In other words, the dump looks to be completely unusable.
GiteaMirror commented 2025-11-02 04:21:47 -06:00
Author
Owner
Copy Link

@ghost commented on GitHub (Nov 28, 2018):

This just occurred to me on 1.5.3 using gitea dump (sequence not created), I'm going to just do a postgres pg_dump instead.

@ghost commented on GitHub (Nov 28, 2018): This just occurred to me on 1.5.3 using `gitea dump` (sequence not created), I'm going to just do a postgres `pg_dump` instead.
GiteaMirror commented 2025-11-02 04:21:47 -06:00
Author
Owner
Copy Link

@max-wittig commented on GitHub (Dec 23, 2018):

This is an essential feature that really needs to work. Otherwise it should just be removed imo.
I'm having the same problems with the restore.

@max-wittig commented on GitHub (Dec 23, 2018): This is an essential feature that really needs to work. Otherwise it should just be removed imo. I'm having the same problems with the restore.
GiteaMirror commented 2025-11-02 04:21:47 -06:00
Author
Owner
Copy Link

@stale[bot] commented on GitHub (Feb 21, 2019):

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

@stale[bot] commented on GitHub (Feb 21, 2019): This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.
GiteaMirror commented 2025-11-02 04:21:48 -06:00
Author
Owner
Copy Link

@belminf commented on GitHub (Mar 3, 2019):

This issue essentially means that a restore does not work without a bit of elbow grease.

The reason this is happening is that Postgres keeps track of the last used ID for tables via "sequences" in order to properly assign IDs to new objects. E.g., repository table's last assigned ID is kept track in repository_id_seq. This is broken after a recovery as sequences are not restored to their proper last IDs.

To fix this, you could reset each table's sequence properly. As of 1.7.3 (current version I'm running), there are 54 tables with sequences. To reset them to their proper value, run the following SQL:

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

That fixed my issue. Hope this helps others.

@belminf commented on GitHub (Mar 3, 2019): This issue essentially means that a restore does not work without a bit of elbow grease. The reason this is happening is that Postgres keeps track of the last used ID for tables via "sequences" in order to properly assign IDs to new objects. E.g., `repository` table's last assigned ID is kept track in `repository_id_seq`. This is broken after a recovery as sequences are not restored to their proper last IDs. To fix this, you could reset each table's sequence properly. As of 1.7.3 (current version I'm running), there are 54 tables with sequences. To reset them to their proper value, run the following SQL: ```sql SELECT SETVAL('public.access_id_seq', COALESCE(MAX(id), 1) ) FROM public.access; SELECT SETVAL('public.access_token_id_seq', COALESCE(MAX(id), 1) ) FROM public.access_token; SELECT SETVAL('public.action_id_seq', COALESCE(MAX(id), 1) ) FROM public.action; SELECT SETVAL('public.attachment_id_seq', COALESCE(MAX(id), 1) ) FROM public.attachment; SELECT SETVAL('public.collaboration_id_seq', COALESCE(MAX(id), 1) ) FROM public.collaboration; SELECT SETVAL('public.comment_id_seq', COALESCE(MAX(id), 1) ) FROM public.comment; SELECT SETVAL('public.commit_status_id_seq', COALESCE(MAX(id), 1) ) FROM public.commit_status; SELECT SETVAL('public.deleted_branch_id_seq', COALESCE(MAX(id), 1) ) FROM public.deleted_branch; SELECT SETVAL('public.deploy_key_id_seq', COALESCE(MAX(id), 1) ) FROM public.deploy_key; SELECT SETVAL('public.email_address_id_seq', COALESCE(MAX(id), 1) ) FROM public.email_address; SELECT SETVAL('public.follow_id_seq', COALESCE(MAX(id), 1) ) FROM public.follow; SELECT SETVAL('public.gpg_key_id_seq', COALESCE(MAX(id), 1) ) FROM public.gpg_key; SELECT SETVAL('public.hook_task_id_seq', COALESCE(MAX(id), 1) ) FROM public.hook_task; SELECT SETVAL('public.issue_assignees_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue_assignees; SELECT SETVAL('public.issue_dependency_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue_dependency; SELECT SETVAL('public.issue_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue; SELECT SETVAL('public.issue_label_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue_label; SELECT SETVAL('public.issue_user_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue_user; SELECT SETVAL('public.issue_watch_id_seq', COALESCE(MAX(id), 1) ) FROM public.issue_watch; SELECT SETVAL('public.label_id_seq', COALESCE(MAX(id), 1) ) FROM public.label; SELECT SETVAL('public.lfs_lock_id_seq', COALESCE(MAX(id), 1) ) FROM public.lfs_lock; SELECT SETVAL('public.lfs_meta_object_id_seq', COALESCE(MAX(id), 1) ) FROM public.lfs_meta_object; SELECT SETVAL('public.login_source_id_seq', COALESCE(MAX(id), 1) ) FROM public.login_source; SELECT SETVAL('public.milestone_id_seq', COALESCE(MAX(id), 1) ) FROM public.milestone; SELECT SETVAL('public.mirror_id_seq', COALESCE(MAX(id), 1) ) FROM public.mirror; SELECT SETVAL('public.notice_id_seq', COALESCE(MAX(id), 1) ) FROM public.notice; SELECT SETVAL('public.notification_id_seq', COALESCE(MAX(id), 1) ) FROM public.notification; SELECT SETVAL('public.org_user_id_seq', COALESCE(MAX(id), 1) ) FROM public.org_user; SELECT SETVAL('public.protected_branch_id_seq', COALESCE(MAX(id), 1) ) FROM public.protected_branch; SELECT SETVAL('public.public_key_id_seq', COALESCE(MAX(id), 1) ) FROM public.public_key; SELECT SETVAL('public.pull_request_id_seq', COALESCE(MAX(id), 1) ) FROM public.pull_request; SELECT SETVAL('public.reaction_id_seq', COALESCE(MAX(id), 1) ) FROM public.reaction; SELECT SETVAL('public.release_id_seq', COALESCE(MAX(id), 1) ) FROM public.release; SELECT SETVAL('public.repo_indexer_status_id_seq', COALESCE(MAX(id), 1) ) FROM public.repo_indexer_status; SELECT SETVAL('public.repo_redirect_id_seq', COALESCE(MAX(id), 1) ) FROM public.repo_redirect; SELECT SETVAL('public.repo_unit_id_seq', COALESCE(MAX(id), 1) ) FROM public.repo_unit; SELECT SETVAL('public.repository_id_seq', COALESCE(MAX(id), 1) ) FROM public.repository; SELECT SETVAL('public.review_id_seq', COALESCE(MAX(id), 1) ) FROM public.review; SELECT SETVAL('public.star_id_seq', COALESCE(MAX(id), 1) ) FROM public.star; SELECT SETVAL('public.stopwatch_id_seq', COALESCE(MAX(id), 1) ) FROM public.stopwatch; SELECT SETVAL('public.team_id_seq', COALESCE(MAX(id), 1) ) FROM public.team; SELECT SETVAL('public.team_repo_id_seq', COALESCE(MAX(id), 1) ) FROM public.team_repo; SELECT SETVAL('public.team_unit_id_seq', COALESCE(MAX(id), 1) ) FROM public.team_unit; SELECT SETVAL('public.team_user_id_seq', COALESCE(MAX(id), 1) ) FROM public.team_user; SELECT SETVAL('public.topic_id_seq', COALESCE(MAX(id), 1) ) FROM public.topic; SELECT SETVAL('public.tracked_time_id_seq', COALESCE(MAX(id), 1) ) FROM public.tracked_time; SELECT SETVAL('public.two_factor_id_seq', COALESCE(MAX(id), 1) ) FROM public.two_factor; SELECT SETVAL('public.u2f_registration_id_seq', COALESCE(MAX(id), 1) ) FROM public.u2f_registration; SELECT SETVAL('public.upload_id_seq', COALESCE(MAX(id), 1) ) FROM public.upload; SELECT SETVAL('public.user_id_seq', COALESCE(MAX(id), 1) ) FROM public."user"; SELECT SETVAL('public.user_open_id_id_seq', COALESCE(MAX(id), 1) ) FROM public.user_open_id; SELECT SETVAL('public.version_id_seq', COALESCE(MAX(id), 1) ) FROM public.version; SELECT SETVAL('public.watch_id_seq', COALESCE(MAX(id), 1) ) FROM public.watch; SELECT SETVAL('public.webhook_id_seq', COALESCE(MAX(id), 1) ) FROM public.webhook; ``` That fixed my issue. Hope this helps others.
GiteaMirror commented 2025-11-02 04:21:48 -06:00
Author
Owner
Copy Link

@cwerner1 commented on GitHub (Aug 19, 2019):

Today I had the same Issue, while migration fon mariadb to postgresql.
I dumped the database with gitea dump --database postgres
After the import I couldn't create a new repository.

After googling the error message, I found https://wiki.postgresql.org/wiki/Fixing_Sequences
which helped a with the problem.
There is a generic SQL Script, with recreates all the sequences for all tables.

@cwerner1 commented on GitHub (Aug 19, 2019): Today I had the same Issue, while migration fon mariadb to postgresql. I dumped the database with `gitea dump --database postgres` After the import I couldn't create a new repository. After googling the error message, I found [https://wiki.postgresql.org/wiki/Fixing_Sequences](https://wiki.postgresql.org/wiki/Fixing_Sequences) which helped a with the problem. There is a generic SQL Script, with recreates all the sequences for all tables.
GiteaMirror commented 2025-11-02 04:21:48 -06:00
Author
Owner
Copy Link

@raucao commented on GitHub (Jun 2, 2020):

Just updating this issue to confirm that the bug still exists when migrating from MySQL to PostgreSQL.

@raucao commented on GitHub (Jun 2, 2020): Just updating this issue to confirm that the bug still exists when migrating from MySQL to PostgreSQL.
GiteaMirror commented 2025-11-02 04:21:48 -06:00
Author
Owner
Copy Link

@robindegen commented on GitHub (Jun 20, 2020):

Also confirming issue exists with latest version. Belminf's workaround fixed the problem

@robindegen commented on GitHub (Jun 20, 2020): Also confirming issue exists with latest version. Belminf's workaround fixed the problem
GiteaMirror commented 2025-11-02 04:21:48 -06:00
Author
Owner
Copy Link

@danielemoroni commented on GitHub (Sep 24, 2020):

Same here. Gitea 1.12.3, migrating from MariaDB to PostgreSQL. I fixed it using Belminf's workaround, upgraded to the 59 tables with sequences that are currently in the schema

@danielemoroni commented on GitHub (Sep 24, 2020): Same here. Gitea 1.12.3, migrating from MariaDB to PostgreSQL. I fixed it using Belminf's workaround, upgraded to the 59 tables with sequences that are currently in the schema
GiteaMirror commented 2025-11-02 04:21:49 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (May 19, 2022):

I think this has been resolved but if not, feel free to reopen it.

@lunny commented on GitHub (May 19, 2022): I think this has been resolved but if not, feel free to reopen it.
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 issue/confirmed type/bug
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#2033
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?