DUMP command line command works wrong

[e1fueg0]

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.

[techknowlogick]

@elfuegobiz which DB are you using?

[e1fueg0]

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

[e1fueg0]

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]

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]

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.

[seanenck]

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.

[max-wittig]

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.

[stale]

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.

[belminf]

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.

[cwerner1]

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.

[raucao]

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

[robindegen]

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

[danielemoroni]

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

[lunny]

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