Postgresql ssequences not updated correctly upon dump import #1199

Open
opened 2022-07-11 17:09:29 +00:00 by xeruf · 38 comments

I, more or less accidentally, setup my instance to use the default SQLite DB, but I'd like to use Postgres.

Is there an easy way to migrate everything over?

https://vikunja.io/docs/config-options/#type

I, more or less accidentally, setup my instance to use the default SQLite DB, but I'd like to use Postgres. Is there an easy way to migrate everything over? https://vikunja.io/docs/config-options/#type
Owner

You could try the vikunja dump and vikunj restore commands. Basically:

  1. Dump everything
  2. Stop the api service
  3. Change the configuration to use postgres instead
  4. Run vikunja restore on the dump created earlier

Another option would be pgloader.

You could try the [`vikunja dump`](https://vikunja.io/docs/cli/#dump) and [`vikunj restore`](https://vikunja.io/docs/cli/#restore) commands. Basically: 1. Dump everything 2. Stop the api service 3. Change the configuration to use postgres instead 4. Run `vikunja restore` on the dump created earlier Another option would be [pgloader](https://pgloader.readthedocs.io/en/latest/ref/sqlite.html).
konrad added the
question
label 2022-07-11 17:40:41 +00:00
Author

I dumped SQLite and restored to postgres, now there is this error:

2022-07-13T10:44:21.627674613Z: CRITICAL	▶ cmd/func10 2f3 could not restore table data for table notifications: pq: invalid input syntax for type json

Seems fine as pending notifications don't really matter, but I don't know if everything else was restored correctly now.

I dumped SQLite and restored to postgres, now there is this error: 2022-07-13T10:44:21.627674613Z: CRITICAL ▶ cmd/func10 2f3 could not restore table data for table notifications: pq: invalid input syntax for type json Seems fine as pending notifications don't really matter, but I don't know if everything else was restored correctly now.
Author

Apparently it did not restore some things such as teams.

When I tried to restore it without notifications, I got the following error:

2022/07/13 10:54:58 Using config file: /etc/vikunja/config.yml
2022-07-13T10:54:58.83258347Z: INFO	▶ migration/Migrate 048 Ran all migrations successfully.
2022-07-13T10:54:58.833200492Z: INFO	▶ models/RegisterReminderCron 049 Mailer is disabled, not sending reminders per mail
2022-07-13T10:54:58.833387512Z: INFO	▶ models/RegisterOverdueReminderCron 04a Mailer is disabled, not sending overdue per mail
2022-07-13T10:54:58.833926465Z: WARNING	▶ dump/Restore 04b Restoring a dump will wipe your current installation!
2022-07-13T10:54:58.834189096Z: WARNING	▶ dump/Restore 04c To confirm, please type 'Yes, I understand' and confirm with enter:
panic: runtime error: slice bounds out of range [:-5]

goroutine 1 [running]:
code.vikunja.io/api/pkg/modules/dump.Restore(0x7ffe3c38b31a, 0x8, 0x0, 0x0)
	/go/src/code.vikunja.io/api/pkg/modules/dump/restore.go:72 +0x2156
code.vikunja.io/api/pkg/cmd.glob..func10(0x1d454e0, 0xc0004c8390, 0x1, 0x1)
	/go/src/code.vikunja.io/api/pkg/cmd/restore.go:38 +0x49
github.com/spf13/cobra.(*Command).execute(0x1d454e0, 0xc0004c8370, 0x1, 0x1, 0x1d454e0, 0xc0004c8370)
	/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:860 +0x2c2
github.com/spf13/cobra.(*Command).ExecuteC(0x1d44d60, 0xc000000180, 0x200000003, 0xc000000180)
	/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:974 +0x375
github.com/spf13/cobra.(*Command).Execute(...)
	/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:902
code.vikunja.io/api/pkg/cmd.Execute()
	/go/src/code.vikunja.io/api/pkg/cmd/cmd.go:44 +0x31
main.main()
	/go/src/code.vikunja.io/api/main.go:22 +0x25
command terminated with exit code 2
Apparently it did not restore some things such as teams. When I tried to restore it without notifications, I got the following error: ```sh 2022/07/13 10:54:58 Using config file: /etc/vikunja/config.yml 2022-07-13T10:54:58.83258347Z: INFO ▶ migration/Migrate 048 Ran all migrations successfully. 2022-07-13T10:54:58.833200492Z: INFO ▶ models/RegisterReminderCron 049 Mailer is disabled, not sending reminders per mail 2022-07-13T10:54:58.833387512Z: INFO ▶ models/RegisterOverdueReminderCron 04a Mailer is disabled, not sending overdue per mail 2022-07-13T10:54:58.833926465Z: WARNING ▶ dump/Restore 04b Restoring a dump will wipe your current installation! 2022-07-13T10:54:58.834189096Z: WARNING ▶ dump/Restore 04c To confirm, please type 'Yes, I understand' and confirm with enter: panic: runtime error: slice bounds out of range [:-5] goroutine 1 [running]: code.vikunja.io/api/pkg/modules/dump.Restore(0x7ffe3c38b31a, 0x8, 0x0, 0x0) /go/src/code.vikunja.io/api/pkg/modules/dump/restore.go:72 +0x2156 code.vikunja.io/api/pkg/cmd.glob..func10(0x1d454e0, 0xc0004c8390, 0x1, 0x1) /go/src/code.vikunja.io/api/pkg/cmd/restore.go:38 +0x49 github.com/spf13/cobra.(*Command).execute(0x1d454e0, 0xc0004c8370, 0x1, 0x1, 0x1d454e0, 0xc0004c8370) /go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:860 +0x2c2 github.com/spf13/cobra.(*Command).ExecuteC(0x1d44d60, 0xc000000180, 0x200000003, 0xc000000180) /go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:974 +0x375 github.com/spf13/cobra.(*Command).Execute(...) /go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:902 code.vikunja.io/api/pkg/cmd.Execute() /go/src/code.vikunja.io/api/pkg/cmd/cmd.go:44 +0x31 main.main() /go/src/code.vikunja.io/api/main.go:22 +0x25 command terminated with exit code 2 ```
Author

Managed to work around the issue by trimming notifications.json to [], rezipping and reimporting :)

Managed to work around the issue by trimming `notifications.json` to `[]`, rezipping and reimporting :)
Author

Now new users can't sign up via OIDC, the reimport probably screwed up the provider info:

2022-07-13T11:48:17.287756418Z: ERROR   ▶ openid/HandleCallback 18a1 Error creating new user for provider Stackspin: pq: duplicate key value violates unique constraint "namespaces_pkey"
2022-07-13T11:48:17.287935509Z: ERROR   ▶ openid/HandleCallback 18a2 pq: duplicate key value violates unique constraint "namespaces_pkey"
Now new users can't sign up via OIDC, the reimport probably screwed up the provider info: ```sh 2022-07-13T11:48:17.287756418Z: ERROR ▶ openid/HandleCallback 18a1 Error creating new user for provider Stackspin: pq: duplicate key value violates unique constraint "namespaces_pkey" 2022-07-13T11:48:17.287935509Z: ERROR ▶ openid/HandleCallback 18a2 pq: duplicate key value violates unique constraint "namespaces_pkey" ```
Owner

Now new users can't sign up via OIDC, the reimport probably screwed up the provider info:

IIRC the namespaces_pkey constraint is the one from the namespace id. Can you check if the sequence in postgres is correct?

The provider info is only stored in config.

> Now new users can't sign up via OIDC, the reimport probably screwed up the provider info: IIRC the `namespaces_pkey` constraint is the one from the namespace id. Can you check if the sequence in postgres is correct? The provider info is only stored in config.
Author
vikunja=> \d+ namespaces
                                                             Table "public.namespaces"
   Column    |            Type             | Collation | Nullable |                Default                 | Storage  | Stats target | Description
-------------+-----------------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
 id          | bigint                      |           | not null | nextval('namespaces_id_seq'::regclass) | plain    |              |
 title       | character varying(250)      |           | not null |                                        | extended |              |
 description | text                        |           |          |                                        | extended |              |
 owner_id    | bigint                      |           | not null |                                        | plain    |              |
 hex_color   | character varying(6)        |           |          |                                        | extended |              |
 is_archived | boolean                     |           | not null | false                                  | plain    |              |
 created     | timestamp without time zone |           | not null |                                        | plain    |              |
 updated     | timestamp without time zone |           | not null |                                        | plain    |              |
Indexes:
    "namespaces_pkey" PRIMARY KEY, btree (id)
    "UQE_namespaces_id" UNIQUE, btree (id)
    "IDX_namespaces_owner_id" btree (owner_id)

vikunja=> SELECT * from namespaces;
 id |    title     |        description        | owner_id | hex_color | is_archived |       created       |       updated
----+--------------+---------------------------+----------+-----------+-------------+---------------------+---------------------
  1 | REDACTED  | REDACTED's namespace.  |        1 |           | f           | 2022-07-05 13:50:20 | 2022-07-05 13:50:20
  2 | ● Projects   |                           |        1 |           | f           | 2022-07-06 05:24:12 | 2022-07-08 07:53:47
  3 |  ▲ Customers |                           |        1 |           | f           | 2022-07-06 05:24:17 | 2022-07-08 07:54:07
  4 | REDACTED  | REDACTED's namespace.  |        2 |           | f           | 2022-07-06 08:30:40 | 2022-07-06 08:30:40
  5 | REDACTED | REDACTED's namespace. |        3 |           | f           | 2022-07-06 10:44:40 | 2022-07-06 10:44:40
  6 | REDACTED  | REDACTED's namespace.  |        4 |           | f           | 2022-07-08 12:50:28 | 2022-07-08 12:50:28
  7 |  REDACTED        |                           |        1 |           | f           | 2022-07-09 01:04:44 | 2022-07-09 01:04:44
  8 | REDACTED  | REDACTED's namespace.  |        5 |           | f           | 2022-07-09 05:05:38 | 2022-07-09 05:05:38
  9 | REDACTED  | REDACTED's namespace.  |        6 |           | f           | 2022-07-09 15:55:10 | 2022-07-09 15:55:10
(9 rows)

It would be good if we could fix this in time for our meeting tomorrow, even just with a temporary workaround, but I don't see where the problem is coming from.

The issue seems to be creating a new namespace for a new user, which seems to use an id which is already in use - maybe the counter wasn't set appropriately with the import?

```sql vikunja=> \d+ namespaces Table "public.namespaces" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+-----------------------------+-----------+----------+----------------------------------------+----------+--------------+------------- id | bigint | | not null | nextval('namespaces_id_seq'::regclass) | plain | | title | character varying(250) | | not null | | extended | | description | text | | | | extended | | owner_id | bigint | | not null | | plain | | hex_color | character varying(6) | | | | extended | | is_archived | boolean | | not null | false | plain | | created | timestamp without time zone | | not null | | plain | | updated | timestamp without time zone | | not null | | plain | | Indexes: "namespaces_pkey" PRIMARY KEY, btree (id) "UQE_namespaces_id" UNIQUE, btree (id) "IDX_namespaces_owner_id" btree (owner_id) vikunja=> SELECT * from namespaces; id | title | description | owner_id | hex_color | is_archived | created | updated ----+--------------+---------------------------+----------+-----------+-------------+---------------------+--------------------- 1 | REDACTED | REDACTED's namespace. | 1 | | f | 2022-07-05 13:50:20 | 2022-07-05 13:50:20 2 | ● Projects | | 1 | | f | 2022-07-06 05:24:12 | 2022-07-08 07:53:47 3 | ▲ Customers | | 1 | | f | 2022-07-06 05:24:17 | 2022-07-08 07:54:07 4 | REDACTED | REDACTED's namespace. | 2 | | f | 2022-07-06 08:30:40 | 2022-07-06 08:30:40 5 | REDACTED | REDACTED's namespace. | 3 | | f | 2022-07-06 10:44:40 | 2022-07-06 10:44:40 6 | REDACTED | REDACTED's namespace. | 4 | | f | 2022-07-08 12:50:28 | 2022-07-08 12:50:28 7 | ■ REDACTED | | 1 | | f | 2022-07-09 01:04:44 | 2022-07-09 01:04:44 8 | REDACTED | REDACTED's namespace. | 5 | | f | 2022-07-09 05:05:38 | 2022-07-09 05:05:38 9 | REDACTED | REDACTED's namespace. | 6 | | f | 2022-07-09 15:55:10 | 2022-07-09 15:55:10 (9 rows) ``` It would be good if we could fix this in time for our meeting tomorrow, even just with a temporary workaround, but I don't see where the problem is coming from. The issue seems to be creating a new namespace for a new user, which seems to use an id which is already in use - maybe the counter wasn't set appropriately with the import?
Owner

Can you enable database logging to see if postgres generates the wrong id or Vikunja?

Set log.database to stdout and log.databaselevel to debug.

Are you able to create a new namespace with an existing user?

Can you enable database logging to see if postgres generates the wrong id or Vikunja? Set [`log.database`](https://vikunja.io/docs/config-options/#database-2) to `stdout` and [`log.databaselevel`](https://vikunja.io/docs/config-options/#databaselevel) to `debug`. Are you able to create a new namespace with an existing user?
Author

No, I can't create new namespaces at all actually.

I'll set the debug parameters and report back.

No, I can't create new namespaces at all actually. I'll set the debug parameters and report back.
Author

Here is the output:

2022-07-13T20:02:06.138389003Z: INFO    ▶ [DATABASE] 1fd [SQL] INSERT INTO "namespaces" ("title","description","owner_id","hex_color","is_archived","created","updated") VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING "id" [title  1  false 2022-07-13 20:02:06 2022-07-13 20:02:06] - 2.263494ms
2022-07-13T20:02:06.138462256Z: ERROR   ▶ handler/CreateWeb 1fe pq: duplicate key value violates unique constraint "namespaces_pkey"
Here is the output: ```sh 2022-07-13T20:02:06.138389003Z: INFO ▶ [DATABASE] 1fd [SQL] INSERT INTO "namespaces" ("title","description","owner_id","hex_color","is_archived","created","updated") VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING "id" [title 1 false 2022-07-13 20:02:06 2022-07-13 20:02:06] - 2.263494ms 2022-07-13T20:02:06.138462256Z: ERROR ▶ handler/CreateWeb 1fe pq: duplicate key value violates unique constraint "namespaces_pkey" ```
Author
This seems similar: https://stackoverflow.com/questions/24149065/auto-increment-issues-postgresql
Author

Fixed it with the following query derived from the aforementioned SO post:

SELECT setval('namespaces_id_seq', 9) FROM namespaces;

Found the correct sequence to use in:

\d+ namespaces
Fixed it with the following query derived from the aforementioned SO post: SELECT setval('namespaces_id_seq', 9) FROM namespaces; Found the correct sequence to use in: \d+ namespaces
Owner

Whooo glad you figured it out! 🎉

Do new users now work?

Whooo glad you figured it out! 🎉 Do new users now work?
Author

Yes, new namespaces, new users, all fine :)

But nevertheless this should be fixed in the importer ;)

Yes, new namespaces, new users, all fine :) But nevertheless this should be fixed in the importer ;)
Owner

I wonder why this didn't work though since all the importer really does is doing INSERT statements which should in theory use the sequence (wait, is the sequence actually incremented when records with an id are inserted?). I'll take another look.

I wonder why this didn't work though since all the importer really does is doing `INSERT` statements which should in theory use the sequence (wait, is the sequence actually incremented when records with an id are inserted?). I'll take another look.
Owner

Pushed a few changes to the restore functionality, most importantly 54348c5891. Now we're restoring the sequences when importing into a postgres database.

596d2bf676 and 6e15d46a93 should fix the other problems you encountered.

Going to close this now as everything should be resolved, please ping if you have other problems.

Pushed a few changes to the restore functionality, most importantly https://kolaente.dev/vikunja/api/commit/54348c5891bf182719a49c3ded89e113266825b7. Now we're restoring the sequences when importing into a postgres database. https://kolaente.dev/vikunja/api/commit/596d2bf676c4ac06536004f396c7aad71be92420 and https://kolaente.dev/vikunja/api/commit/6e15d46a93655bdb89e92d94069536b9496ea7b1 should fix the other problems you encountered. Going to close this now as everything should be resolved, please ping if you have other problems.
Author

I now upgraded to unstable and reimported to apply the fixes, but whenever I login I find everything empty...

I now upgraded to unstable and reimported to apply the fixes, but whenever I login I find everything empty...
Author
2022-07-18T12:05:13.623784645Z: INFO	▶ [DATABASE] 0d0 [SQL] SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1 AND schemaname=$2 [notifications public] - 2.45637ms
2022-07-18T12:05:13.624880135Z: INFO	▶ [DATABASE] 0d1 [SQL] BEGIN TRANSACTION [] - 931.808µs
2022-07-18T12:05:13.627437247Z: INFO	▶ [DATABASE] 0d2 [SQL] DROP TABLE IF EXISTS "migration" [] - 2.489708ms
2022-07-18T12:05:13.630154588Z: INFO	▶ [DATABASE] 0d3 [SQL] COMMIT [] - 2.670251ms
2022-07-18T12:05:13.631293043Z: INFO	▶ [DATABASE] 0d4 [SQL] BEGIN TRANSACTION [] - 1.039ms
2022-07-18T12:05:13.637685402Z: INFO	▶ [DATABASE] 0d5 [SQL] DROP TABLE IF EXISTS "lists" [] - 6.324888ms
2022-07-18T12:05:13.639967798Z: INFO	▶ [DATABASE] 0d6 [SQL] COMMIT [] - 2.197979ms
2022-07-18T12:05:13.64032176Z: INFO	▶ [DATABASE] 0d7 [SQL] BEGIN TRANSACTION [] - 269.431µs
2022-07-18T12:05:13.644241888Z: INFO	▶ [DATABASE] 0d8 [SQL] DROP TABLE IF EXISTS "tasks" [] - 3.851681ms
2022-07-18T12:05:13.646803709Z: INFO	▶ [DATABASE] 0d9 [SQL] COMMIT [] - 2.476706ms
2022-07-18T12:05:13.647149803Z: INFO	▶ [DATABASE] 0da [SQL] BEGIN TRANSACTION [] - 273.924µs
2022-07-18T12:05:13.649384874Z: INFO	▶ [DATABASE] 0db [SQL] DROP TABLE IF EXISTS "teams" [] - 2.165432ms
2022-07-18T12:05:13.651247633Z: INFO	▶ [DATABASE] 0dc [SQL] COMMIT [] - 1.780151ms
2022-07-18T12:05:13.651594255Z: INFO	▶ [DATABASE] 0dd [SQL] BEGIN TRANSACTION [] - 258.273µs
2022-07-18T12:05:13.653428869Z: INFO	▶ [DATABASE] 0de [SQL] DROP TABLE IF EXISTS "team_members" [] - 1.78194ms
2022-07-18T12:05:13.655264262Z: INFO	▶ [DATABASE] 0df [SQL] COMMIT [] - 1.768487ms
2022-07-18T12:05:13.655547651Z: INFO	▶ [DATABASE] 0e0 [SQL] BEGIN TRANSACTION [] - 238.251µs
2022-07-18T12:05:13.657619697Z: INFO	▶ [DATABASE] 0e1 [SQL] DROP TABLE IF EXISTS "team_lists" [] - 2.032303ms
2022-07-18T12:05:13.659090547Z: INFO	▶ [DATABASE] 0e2 [SQL] COMMIT [] - 1.401696ms
2022-07-18T12:05:13.659350833Z: INFO	▶ [DATABASE] 0e3 [SQL] BEGIN TRANSACTION [] - 196.632µs
2022-07-18T12:05:13.661352773Z: INFO	▶ [DATABASE] 0e4 [SQL] DROP TABLE IF EXISTS "team_namespaces" [] - 1.931915ms
2022-07-18T12:05:13.663014791Z: INFO	▶ [DATABASE] 0e5 [SQL] COMMIT [] - 1.590623ms
2022-07-18T12:05:13.663426836Z: INFO	▶ [DATABASE] 0e6 [SQL] BEGIN TRANSACTION [] - 313.385µs
2022-07-18T12:05:13.665785686Z: INFO	▶ [DATABASE] 0e7 [SQL] DROP TABLE IF EXISTS "namespaces" [] - 2.248866ms
2022-07-18T12:05:13.668353951Z: INFO	▶ [DATABASE] 0e8 [SQL] COMMIT [] - 2.465408ms
2022-07-18T12:05:13.668957369Z: INFO	▶ [DATABASE] 0e9 [SQL] BEGIN TRANSACTION [] - 521.561µs
2022-07-18T12:05:13.673346139Z: INFO	▶ [DATABASE] 0ea [SQL] DROP TABLE IF EXISTS "users_lists" [] - 4.169101ms
2022-07-18T12:05:13.678752167Z: INFO	▶ [DATABASE] 0eb [SQL] COMMIT [] - 5.341121ms
2022-07-18T12:05:13.679227003Z: INFO	▶ [DATABASE] 0ec [SQL] BEGIN TRANSACTION [] - 388.596µs
2022-07-18T12:05:13.681613197Z: INFO	▶ [DATABASE] 0ed [SQL] DROP TABLE IF EXISTS "users_namespaces" [] - 2.31179ms
2022-07-18T12:05:13.694386741Z: INFO	▶ [DATABASE] 0ee [SQL] COMMIT [] - 12.735776ms
2022-07-18T12:05:13.694736962Z: INFO	▶ [DATABASE] 0ef [SQL] BEGIN TRANSACTION [] - 290.952µs
2022-07-18T12:05:13.696397466Z: INFO	▶ [DATABASE] 0f0 [SQL] DROP TABLE IF EXISTS "task_assignees" [] - 1.577204ms
2022-07-18T12:05:13.698025001Z: INFO	▶ [DATABASE] 0f1 [SQL] COMMIT [] - 1.563112ms
2022-07-18T12:05:13.698887983Z: INFO	▶ [DATABASE] 0f2 [SQL] BEGIN TRANSACTION [] - 775.412µs
2022-07-18T12:05:13.700761277Z: INFO	▶ [DATABASE] 0f3 [SQL] DROP TABLE IF EXISTS "labels" [] - 1.781526ms
2022-07-18T12:05:13.702446187Z: INFO	▶ [DATABASE] 0f4 [SQL] COMMIT [] - 1.619004ms
2022-07-18T12:05:13.703272248Z: INFO	▶ [DATABASE] 0f5 [SQL] BEGIN TRANSACTION [] - 737.967µs
2022-07-18T12:05:13.704924212Z: INFO	▶ [DATABASE] 0f6 [SQL] DROP TABLE IF EXISTS "label_tasks" [] - 1.564722ms
2022-07-18T12:05:13.709520519Z: INFO	▶ [DATABASE] 0f7 [SQL] COMMIT [] - 4.527972ms
2022-07-18T12:05:13.710642348Z: INFO	▶ [DATABASE] 0f8 [SQL] BEGIN TRANSACTION [] - 1.0551ms
2022-07-18T12:05:13.712215294Z: INFO	▶ [DATABASE] 0f9 [SQL] DROP TABLE IF EXISTS "task_reminders" [] - 1.513318ms
2022-07-18T12:05:13.713646286Z: INFO	▶ [DATABASE] 0fa [SQL] COMMIT [] - 1.394522ms
2022-07-18T12:05:13.713912165Z: INFO	▶ [DATABASE] 0fb [SQL] BEGIN TRANSACTION [] - 223.705µs
2022-07-18T12:05:13.716255932Z: INFO	▶ [DATABASE] 0fc [SQL] DROP TABLE IF EXISTS "link_shares" [] - 2.275606ms
2022-07-18T12:05:13.718682355Z: INFO	▶ [DATABASE] 0fd [SQL] COMMIT [] - 2.390813ms
2022-07-18T12:05:13.719377134Z: INFO	▶ [DATABASE] 0fe [SQL] BEGIN TRANSACTION [] - 656.612µs
2022-07-18T12:05:13.720694703Z: INFO	▶ [DATABASE] 0ff [SQL] DROP TABLE IF EXISTS "task_relations" [] - 1.229264ms
2022-07-18T12:05:13.722376088Z: INFO	▶ [DATABASE] 100 [SQL] COMMIT [] - 1.528494ms
2022-07-18T12:05:13.723143504Z: INFO	▶ [DATABASE] 101 [SQL] BEGIN TRANSACTION [] - 730.597µs
2022-07-18T12:05:13.724394131Z: INFO	▶ [DATABASE] 102 [SQL] DROP TABLE IF EXISTS "task_attachments" [] - 1.209135ms
2022-07-18T12:05:13.72569622Z: INFO	▶ [DATABASE] 103 [SQL] COMMIT [] - 1.269279ms
2022-07-18T12:05:13.72593805Z: INFO	▶ [DATABASE] 104 [SQL] BEGIN TRANSACTION [] - 208.364µs
2022-07-18T12:05:13.727680578Z: INFO	▶ [DATABASE] 105 [SQL] DROP TABLE IF EXISTS "task_comments" [] - 1.7053ms
2022-07-18T12:05:13.730589794Z: INFO	▶ [DATABASE] 106 [SQL] COMMIT [] - 2.864382ms
2022-07-18T12:05:13.731032772Z: INFO	▶ [DATABASE] 107 [SQL] BEGIN TRANSACTION [] - 348.627µs
2022-07-18T12:05:13.73344454Z: INFO	▶ [DATABASE] 108 [SQL] DROP TABLE IF EXISTS "buckets" [] - 2.370863ms
2022-07-18T12:05:13.735130747Z: INFO	▶ [DATABASE] 109 [SQL] COMMIT [] - 1.61198ms
2022-07-18T12:05:13.735418787Z: INFO	▶ [DATABASE] 10a [SQL] BEGIN TRANSACTION [] - 223.931µs
2022-07-18T12:05:13.738363143Z: INFO	▶ [DATABASE] 10b [SQL] DROP TABLE IF EXISTS "unsplash_photos" [] - 2.901843ms
2022-07-18T12:05:13.739862814Z: INFO	▶ [DATABASE] 10c [SQL] COMMIT [] - 1.457357ms
2022-07-18T12:05:13.740153725Z: INFO	▶ [DATABASE] 10d [SQL] BEGIN TRANSACTION [] - 201.246µs
2022-07-18T12:05:13.742244606Z: INFO	▶ [DATABASE] 10e [SQL] DROP TABLE IF EXISTS "saved_filters" [] - 2.028721ms
2022-07-18T12:05:13.743997159Z: INFO	▶ [DATABASE] 10f [SQL] COMMIT [] - 1.690667ms
2022-07-18T12:05:13.744287263Z: INFO	▶ [DATABASE] 110 [SQL] BEGIN TRANSACTION [] - 223.797µs
2022-07-18T12:05:13.74624955Z: INFO	▶ [DATABASE] 111 [SQL] DROP TABLE IF EXISTS "subscriptions" [] - 1.902203ms
2022-07-18T12:05:13.747803215Z: INFO	▶ [DATABASE] 112 [SQL] COMMIT [] - 1.487339ms
2022-07-18T12:05:13.748109026Z: INFO	▶ [DATABASE] 113 [SQL] BEGIN TRANSACTION [] - 264.934µs
2022-07-18T12:05:13.748952852Z: INFO	▶ [DATABASE] 114 [SQL] DROP TABLE IF EXISTS "favorites" [] - 801.252µs
2022-07-18T12:05:13.750092028Z: INFO	▶ [DATABASE] 115 [SQL] COMMIT [] - 1.104428ms
2022-07-18T12:05:13.750353339Z: INFO	▶ [DATABASE] 116 [SQL] BEGIN TRANSACTION [] - 196.631µs
2022-07-18T12:05:13.751950806Z: INFO	▶ [DATABASE] 117 [SQL] DROP TABLE IF EXISTS "files" [] - 1.560791ms
2022-07-18T12:05:13.753323447Z: INFO	▶ [DATABASE] 118 [SQL] COMMIT [] - 1.340187ms
2022-07-18T12:05:13.753655182Z: INFO	▶ [DATABASE] 119 [SQL] BEGIN TRANSACTION [] - 291.145µs
2022-07-18T12:05:13.755041661Z: INFO	▶ [DATABASE] 11a [SQL] DROP TABLE IF EXISTS "migration_status" [] - 1.281484ms
2022-07-18T12:05:13.756390668Z: INFO	▶ [DATABASE] 11b [SQL] COMMIT [] - 1.277741ms
2022-07-18T12:05:13.756655071Z: INFO	▶ [DATABASE] 11c [SQL] BEGIN TRANSACTION [] - 201.612µs
2022-07-18T12:05:13.759755294Z: INFO	▶ [DATABASE] 11d [SQL] DROP TABLE IF EXISTS "users" [] - 3.039349ms
2022-07-18T12:05:13.762028726Z: INFO	▶ [DATABASE] 11e [SQL] COMMIT [] - 1.937379ms
2022-07-18T12:05:13.762732477Z: INFO	▶ [DATABASE] 11f [SQL] BEGIN TRANSACTION [] - 669.572µs
2022-07-18T12:05:13.764499921Z: INFO	▶ [DATABASE] 120 [SQL] DROP TABLE IF EXISTS "totp" [] - 1.684014ms
2022-07-18T12:05:13.766584016Z: INFO	▶ [DATABASE] 121 [SQL] COMMIT [] - 1.998243ms
2022-07-18T12:05:13.767673342Z: INFO	▶ [DATABASE] 122 [SQL] BEGIN TRANSACTION [] - 932.825µs
2022-07-18T12:05:13.769645281Z: INFO	▶ [DATABASE] 123 [SQL] DROP TABLE IF EXISTS "user_tokens" [] - 1.913834ms
2022-07-18T12:05:13.771687568Z: INFO	▶ [DATABASE] 124 [SQL] COMMIT [] - 1.909405ms
2022-07-18T12:05:13.772359797Z: INFO	▶ [DATABASE] 125 [SQL] BEGIN TRANSACTION [] - 599.28µs
2022-07-18T12:05:13.774248991Z: INFO	▶ [DATABASE] 126 [SQL] DROP TABLE IF EXISTS "notifications" [] - 1.814279ms
2022-07-18T12:05:13.77617556Z: INFO	▶ [DATABASE] 127 [SQL] COMMIT [] - 1.840644ms
2022-07-18T12:05:13.776229444Z: INFO	▶ dump/Restore 128 Wiped database.
2022-07-18T12:05:13.777599217Z: CRITICAL	▶ cmd/func10 12a could not create db structure: xormigrate: Tried to migrate to an ID that doesn't exist
```sh 2022-07-18T12:05:13.623784645Z: INFO ▶ [DATABASE] 0d0 [SQL] SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1 AND schemaname=$2 [notifications public] - 2.45637ms 2022-07-18T12:05:13.624880135Z: INFO ▶ [DATABASE] 0d1 [SQL] BEGIN TRANSACTION [] - 931.808µs 2022-07-18T12:05:13.627437247Z: INFO ▶ [DATABASE] 0d2 [SQL] DROP TABLE IF EXISTS "migration" [] - 2.489708ms 2022-07-18T12:05:13.630154588Z: INFO ▶ [DATABASE] 0d3 [SQL] COMMIT [] - 2.670251ms 2022-07-18T12:05:13.631293043Z: INFO ▶ [DATABASE] 0d4 [SQL] BEGIN TRANSACTION [] - 1.039ms 2022-07-18T12:05:13.637685402Z: INFO ▶ [DATABASE] 0d5 [SQL] DROP TABLE IF EXISTS "lists" [] - 6.324888ms 2022-07-18T12:05:13.639967798Z: INFO ▶ [DATABASE] 0d6 [SQL] COMMIT [] - 2.197979ms 2022-07-18T12:05:13.64032176Z: INFO ▶ [DATABASE] 0d7 [SQL] BEGIN TRANSACTION [] - 269.431µs 2022-07-18T12:05:13.644241888Z: INFO ▶ [DATABASE] 0d8 [SQL] DROP TABLE IF EXISTS "tasks" [] - 3.851681ms 2022-07-18T12:05:13.646803709Z: INFO ▶ [DATABASE] 0d9 [SQL] COMMIT [] - 2.476706ms 2022-07-18T12:05:13.647149803Z: INFO ▶ [DATABASE] 0da [SQL] BEGIN TRANSACTION [] - 273.924µs 2022-07-18T12:05:13.649384874Z: INFO ▶ [DATABASE] 0db [SQL] DROP TABLE IF EXISTS "teams" [] - 2.165432ms 2022-07-18T12:05:13.651247633Z: INFO ▶ [DATABASE] 0dc [SQL] COMMIT [] - 1.780151ms 2022-07-18T12:05:13.651594255Z: INFO ▶ [DATABASE] 0dd [SQL] BEGIN TRANSACTION [] - 258.273µs 2022-07-18T12:05:13.653428869Z: INFO ▶ [DATABASE] 0de [SQL] DROP TABLE IF EXISTS "team_members" [] - 1.78194ms 2022-07-18T12:05:13.655264262Z: INFO ▶ [DATABASE] 0df [SQL] COMMIT [] - 1.768487ms 2022-07-18T12:05:13.655547651Z: INFO ▶ [DATABASE] 0e0 [SQL] BEGIN TRANSACTION [] - 238.251µs 2022-07-18T12:05:13.657619697Z: INFO ▶ [DATABASE] 0e1 [SQL] DROP TABLE IF EXISTS "team_lists" [] - 2.032303ms 2022-07-18T12:05:13.659090547Z: INFO ▶ [DATABASE] 0e2 [SQL] COMMIT [] - 1.401696ms 2022-07-18T12:05:13.659350833Z: INFO ▶ [DATABASE] 0e3 [SQL] BEGIN TRANSACTION [] - 196.632µs 2022-07-18T12:05:13.661352773Z: INFO ▶ [DATABASE] 0e4 [SQL] DROP TABLE IF EXISTS "team_namespaces" [] - 1.931915ms 2022-07-18T12:05:13.663014791Z: INFO ▶ [DATABASE] 0e5 [SQL] COMMIT [] - 1.590623ms 2022-07-18T12:05:13.663426836Z: INFO ▶ [DATABASE] 0e6 [SQL] BEGIN TRANSACTION [] - 313.385µs 2022-07-18T12:05:13.665785686Z: INFO ▶ [DATABASE] 0e7 [SQL] DROP TABLE IF EXISTS "namespaces" [] - 2.248866ms 2022-07-18T12:05:13.668353951Z: INFO ▶ [DATABASE] 0e8 [SQL] COMMIT [] - 2.465408ms 2022-07-18T12:05:13.668957369Z: INFO ▶ [DATABASE] 0e9 [SQL] BEGIN TRANSACTION [] - 521.561µs 2022-07-18T12:05:13.673346139Z: INFO ▶ [DATABASE] 0ea [SQL] DROP TABLE IF EXISTS "users_lists" [] - 4.169101ms 2022-07-18T12:05:13.678752167Z: INFO ▶ [DATABASE] 0eb [SQL] COMMIT [] - 5.341121ms 2022-07-18T12:05:13.679227003Z: INFO ▶ [DATABASE] 0ec [SQL] BEGIN TRANSACTION [] - 388.596µs 2022-07-18T12:05:13.681613197Z: INFO ▶ [DATABASE] 0ed [SQL] DROP TABLE IF EXISTS "users_namespaces" [] - 2.31179ms 2022-07-18T12:05:13.694386741Z: INFO ▶ [DATABASE] 0ee [SQL] COMMIT [] - 12.735776ms 2022-07-18T12:05:13.694736962Z: INFO ▶ [DATABASE] 0ef [SQL] BEGIN TRANSACTION [] - 290.952µs 2022-07-18T12:05:13.696397466Z: INFO ▶ [DATABASE] 0f0 [SQL] DROP TABLE IF EXISTS "task_assignees" [] - 1.577204ms 2022-07-18T12:05:13.698025001Z: INFO ▶ [DATABASE] 0f1 [SQL] COMMIT [] - 1.563112ms 2022-07-18T12:05:13.698887983Z: INFO ▶ [DATABASE] 0f2 [SQL] BEGIN TRANSACTION [] - 775.412µs 2022-07-18T12:05:13.700761277Z: INFO ▶ [DATABASE] 0f3 [SQL] DROP TABLE IF EXISTS "labels" [] - 1.781526ms 2022-07-18T12:05:13.702446187Z: INFO ▶ [DATABASE] 0f4 [SQL] COMMIT [] - 1.619004ms 2022-07-18T12:05:13.703272248Z: INFO ▶ [DATABASE] 0f5 [SQL] BEGIN TRANSACTION [] - 737.967µs 2022-07-18T12:05:13.704924212Z: INFO ▶ [DATABASE] 0f6 [SQL] DROP TABLE IF EXISTS "label_tasks" [] - 1.564722ms 2022-07-18T12:05:13.709520519Z: INFO ▶ [DATABASE] 0f7 [SQL] COMMIT [] - 4.527972ms 2022-07-18T12:05:13.710642348Z: INFO ▶ [DATABASE] 0f8 [SQL] BEGIN TRANSACTION [] - 1.0551ms 2022-07-18T12:05:13.712215294Z: INFO ▶ [DATABASE] 0f9 [SQL] DROP TABLE IF EXISTS "task_reminders" [] - 1.513318ms 2022-07-18T12:05:13.713646286Z: INFO ▶ [DATABASE] 0fa [SQL] COMMIT [] - 1.394522ms 2022-07-18T12:05:13.713912165Z: INFO ▶ [DATABASE] 0fb [SQL] BEGIN TRANSACTION [] - 223.705µs 2022-07-18T12:05:13.716255932Z: INFO ▶ [DATABASE] 0fc [SQL] DROP TABLE IF EXISTS "link_shares" [] - 2.275606ms 2022-07-18T12:05:13.718682355Z: INFO ▶ [DATABASE] 0fd [SQL] COMMIT [] - 2.390813ms 2022-07-18T12:05:13.719377134Z: INFO ▶ [DATABASE] 0fe [SQL] BEGIN TRANSACTION [] - 656.612µs 2022-07-18T12:05:13.720694703Z: INFO ▶ [DATABASE] 0ff [SQL] DROP TABLE IF EXISTS "task_relations" [] - 1.229264ms 2022-07-18T12:05:13.722376088Z: INFO ▶ [DATABASE] 100 [SQL] COMMIT [] - 1.528494ms 2022-07-18T12:05:13.723143504Z: INFO ▶ [DATABASE] 101 [SQL] BEGIN TRANSACTION [] - 730.597µs 2022-07-18T12:05:13.724394131Z: INFO ▶ [DATABASE] 102 [SQL] DROP TABLE IF EXISTS "task_attachments" [] - 1.209135ms 2022-07-18T12:05:13.72569622Z: INFO ▶ [DATABASE] 103 [SQL] COMMIT [] - 1.269279ms 2022-07-18T12:05:13.72593805Z: INFO ▶ [DATABASE] 104 [SQL] BEGIN TRANSACTION [] - 208.364µs 2022-07-18T12:05:13.727680578Z: INFO ▶ [DATABASE] 105 [SQL] DROP TABLE IF EXISTS "task_comments" [] - 1.7053ms 2022-07-18T12:05:13.730589794Z: INFO ▶ [DATABASE] 106 [SQL] COMMIT [] - 2.864382ms 2022-07-18T12:05:13.731032772Z: INFO ▶ [DATABASE] 107 [SQL] BEGIN TRANSACTION [] - 348.627µs 2022-07-18T12:05:13.73344454Z: INFO ▶ [DATABASE] 108 [SQL] DROP TABLE IF EXISTS "buckets" [] - 2.370863ms 2022-07-18T12:05:13.735130747Z: INFO ▶ [DATABASE] 109 [SQL] COMMIT [] - 1.61198ms 2022-07-18T12:05:13.735418787Z: INFO ▶ [DATABASE] 10a [SQL] BEGIN TRANSACTION [] - 223.931µs 2022-07-18T12:05:13.738363143Z: INFO ▶ [DATABASE] 10b [SQL] DROP TABLE IF EXISTS "unsplash_photos" [] - 2.901843ms 2022-07-18T12:05:13.739862814Z: INFO ▶ [DATABASE] 10c [SQL] COMMIT [] - 1.457357ms 2022-07-18T12:05:13.740153725Z: INFO ▶ [DATABASE] 10d [SQL] BEGIN TRANSACTION [] - 201.246µs 2022-07-18T12:05:13.742244606Z: INFO ▶ [DATABASE] 10e [SQL] DROP TABLE IF EXISTS "saved_filters" [] - 2.028721ms 2022-07-18T12:05:13.743997159Z: INFO ▶ [DATABASE] 10f [SQL] COMMIT [] - 1.690667ms 2022-07-18T12:05:13.744287263Z: INFO ▶ [DATABASE] 110 [SQL] BEGIN TRANSACTION [] - 223.797µs 2022-07-18T12:05:13.74624955Z: INFO ▶ [DATABASE] 111 [SQL] DROP TABLE IF EXISTS "subscriptions" [] - 1.902203ms 2022-07-18T12:05:13.747803215Z: INFO ▶ [DATABASE] 112 [SQL] COMMIT [] - 1.487339ms 2022-07-18T12:05:13.748109026Z: INFO ▶ [DATABASE] 113 [SQL] BEGIN TRANSACTION [] - 264.934µs 2022-07-18T12:05:13.748952852Z: INFO ▶ [DATABASE] 114 [SQL] DROP TABLE IF EXISTS "favorites" [] - 801.252µs 2022-07-18T12:05:13.750092028Z: INFO ▶ [DATABASE] 115 [SQL] COMMIT [] - 1.104428ms 2022-07-18T12:05:13.750353339Z: INFO ▶ [DATABASE] 116 [SQL] BEGIN TRANSACTION [] - 196.631µs 2022-07-18T12:05:13.751950806Z: INFO ▶ [DATABASE] 117 [SQL] DROP TABLE IF EXISTS "files" [] - 1.560791ms 2022-07-18T12:05:13.753323447Z: INFO ▶ [DATABASE] 118 [SQL] COMMIT [] - 1.340187ms 2022-07-18T12:05:13.753655182Z: INFO ▶ [DATABASE] 119 [SQL] BEGIN TRANSACTION [] - 291.145µs 2022-07-18T12:05:13.755041661Z: INFO ▶ [DATABASE] 11a [SQL] DROP TABLE IF EXISTS "migration_status" [] - 1.281484ms 2022-07-18T12:05:13.756390668Z: INFO ▶ [DATABASE] 11b [SQL] COMMIT [] - 1.277741ms 2022-07-18T12:05:13.756655071Z: INFO ▶ [DATABASE] 11c [SQL] BEGIN TRANSACTION [] - 201.612µs 2022-07-18T12:05:13.759755294Z: INFO ▶ [DATABASE] 11d [SQL] DROP TABLE IF EXISTS "users" [] - 3.039349ms 2022-07-18T12:05:13.762028726Z: INFO ▶ [DATABASE] 11e [SQL] COMMIT [] - 1.937379ms 2022-07-18T12:05:13.762732477Z: INFO ▶ [DATABASE] 11f [SQL] BEGIN TRANSACTION [] - 669.572µs 2022-07-18T12:05:13.764499921Z: INFO ▶ [DATABASE] 120 [SQL] DROP TABLE IF EXISTS "totp" [] - 1.684014ms 2022-07-18T12:05:13.766584016Z: INFO ▶ [DATABASE] 121 [SQL] COMMIT [] - 1.998243ms 2022-07-18T12:05:13.767673342Z: INFO ▶ [DATABASE] 122 [SQL] BEGIN TRANSACTION [] - 932.825µs 2022-07-18T12:05:13.769645281Z: INFO ▶ [DATABASE] 123 [SQL] DROP TABLE IF EXISTS "user_tokens" [] - 1.913834ms 2022-07-18T12:05:13.771687568Z: INFO ▶ [DATABASE] 124 [SQL] COMMIT [] - 1.909405ms 2022-07-18T12:05:13.772359797Z: INFO ▶ [DATABASE] 125 [SQL] BEGIN TRANSACTION [] - 599.28µs 2022-07-18T12:05:13.774248991Z: INFO ▶ [DATABASE] 126 [SQL] DROP TABLE IF EXISTS "notifications" [] - 1.814279ms 2022-07-18T12:05:13.77617556Z: INFO ▶ [DATABASE] 127 [SQL] COMMIT [] - 1.840644ms 2022-07-18T12:05:13.776229444Z: INFO ▶ dump/Restore 128 Wiped database. 2022-07-18T12:05:13.777599217Z: CRITICAL ▶ cmd/func10 12a could not create db structure: xormigrate: Tried to migrate to an ID that doesn't exist ```
Author

This is really bad because we wanted to use Vikunja productively today :/

I tried an older backup and the import worked, but I still cannot create new lists:

2022-07-18T12:15:44.63537652Z: ERROR    ▶ handler/CreateWeb 8ba pq: duplicate key value violates unique constraint "lists_pkey"

As said, latest unstable.

This is really bad because we wanted to use Vikunja productively today :/ I tried an older backup and the import worked, but I still cannot create new lists: 2022-07-18T12:15:44.63537652Z: ERROR ▶ handler/CreateWeb 8ba pq: duplicate key value violates unique constraint "lists_pkey" As said, latest unstable.
Author

Just noticed I am not quite at latest, but includes your commits anyways:

Frontend Version: 0.18.1+1972-d462d56202

API Version: v0.18.1+283-8023674adf
Just noticed I am not quite at latest, but includes your commits anyways: Frontend Version: 0.18.1+1972-d462d56202 API Version: v0.18.1+283-8023674adf
Author

I spent far too much time, three hours now, resetting and reimporting different dumps.

I now finally ended up being able to copy in a directory backup of postgres, running vikunja migrate and thus having a working version with the latest data on unstable.

The original problem is still not fixed, of course...

I spent far too much time, three hours now, resetting and reimporting different dumps. I now finally ended up being able to copy in a directory backup of postgres, running `vikunja migrate` and thus having a working version with the latest data on unstable. The original problem is still not fixed, of course...
Owner

but whenever I login I find everything empty...

You mean nothing was imported?

2022-07-18T12:05:13.777599217Z: CRITICAL ▶ cmd/func10 12a could not create db structure: xormigrate: Tried to migrate to an ID that doesn't exist

That's something that should never happen. Can you send the content of the migrations.json file from inside the .zip?

The original problem is still not fixed, of course...

So you're unable to create new lists? But namespaces work?

I spent far too much time, three hours now, resetting and reimporting different dumps.

Sorry to hear that... maybe it'd be easier to use pgloader to migrate the sqlite db to postgres?

> but whenever I login I find everything empty... You mean nothing was imported? > 2022-07-18T12:05:13.777599217Z: CRITICAL ▶ cmd/func10 12a could not create db structure: xormigrate: Tried to migrate to an ID that doesn't exist That's something that should never happen. Can you send the content of the `migrations.json` file from inside the `.zip`? > The original problem is still not fixed, of course... So you're unable to create new lists? But namespaces work? > I spent far too much time, three hours now, resetting and reimporting different dumps. Sorry to hear that... maybe it'd be easier to use pgloader to migrate the sqlite db to postgres?
Owner

During the last import, was there a warning message like Could not reset id sequence for lists in the logs?

During the last import, was there a warning message like `Could not reset id sequence for lists` in the logs?
Author

I am getting 500's pretty randomly for some add operations (including adding task relations, lists and tasks), but they always work after a few repetitions.

I also struggled to obtain proper logs, unfortunately...

migration.json:

[{"description":"","id":"SCHEMA_INIT"},
{"description":"","id":"20190324205606"},
{"description":"","id":"20190328074430"},
{"description":"","id":"20190430111111"},
{"description":"","id":"20190511202210"},
{"description":"","id":"20190514192749"},
{"description":"","id":"20190524205441"},
{"description":"","id":"20190718200716"},
{"description":"","id":"20190818210133"},
{"description":"","id":"20190920185205"},
{"description":"","id":"20190922205826"},
{"description":"","id":"20191008194238"},
{"description":"","id":"20191010131430"},
{"description":"","id":"20191207204427"},
{"description":"","id":"20191207220736"},
{"description":"","id":"20200120201756"},
{"description":"","id":"20200219183248"},
{"description":"","id":"20200308205855"},
{"description":"","id":"20200308210130"},
{"description":"","id":"20200322214440"},
{"description":"","id":"20200322214624"},
{"description":"","id":"20200417175201"},
{"description":"","id":"20200418230432"},
{"description":"","id":"20200418230605"},
{"description":"","id":"20200420215928"},
{"description":"","id":"20200425182634"},
{"description":"","id":"20200509103709"},
{"description":"","id":"20200515172220"},
{"description":"","id":"20200515195546"},
{"description":"","id":"20200516123847"},
{"description":"","id":"20200524221534"},
{"description":"","id":"20200524224611"},
{"description":"","id":"20200614113230"},
{"description":"","id":"20200621214452"},
{"description":"","id":"20200801183357"},
{"description":"","id":"20200904101559"},
{"description":"","id":"20200905151040"},
{"description":"","id":"20200905232458"},
{"description":"","id":"20200906184746"},
{"description":"","id":"20201025195822"},
{"description":"","id":"20201121181647"},
{"description":"","id":"20201218152741"},
{"description":"","id":"20201218220204"},
{"description":"","id":"20201219145028"},
{"description":"","id":"20210207192805"},
{"description":"","id":"20210209204715"},
{"description":"","id":"20210220222121"},
{"description":"","id":"20210221111953"},
{"description":"","id":"20210321185225"},
{"description":"","id":"20210328191017"},
{"description":"","id":"20210403145503"},
{"description":"","id":"20210403220653"},
{"description":"","id":"20210407170753"},
{"description":"","id":"20210411113105"},
{"description":"","id":"20210411161337"},
{"description":"","id":"20210413131057"},
{"description":"","id":"20210527105701"},
{"description":"","id":"20210603174608"},
{"description":"","id":"20210709191101"},
{"description":"","id":"20210709211508"},
{"description":"","id":"20210711173657"},
{"description":"","id":"20210713213622"},
{"description":"","id":"20210725153703"},
{"description":"","id":"20210727204942"},
{"description":"","id":"20210727211037"},
{"description":"","id":"20210729142940"},
{"description":"","id":"20210802081716"},
{"description":"","id":"20210829194722"},
{"description":"","id":"SCHEMA_INIT"},
{"description":"","id":"20190324205606"},
{"description":"","id":"20190328074430"},
{"description":"","id":"20190430111111"},
{"description":"","id":"20190511202210"},
{"description":"","id":"20190514192749"},
{"description":"","id":"20190524205441"},
{"description":"","id":"20190718200716"},
{"description":"","id":"20190818210133"},
{"description":"","id":"20190920185205"},
{"description":"","id":"20190922205826"},
{"description":"","id":"20191008194238"},
{"description":"","id":"20191010131430"},
{"description":"","id":"20191207204427"},
{"description":"","id":"20191207220736"},
{"description":"","id":"20200120201756"},
{"description":"","id":"20200219183248"},
{"description":"","id":"20200308205855"},
{"description":"","id":"20200308210130"},
{"description":"","id":"20200322214440"},
{"description":"","id":"20200322214624"},
{"description":"","id":"20200417175201"},
{"description":"","id":"20200418230432"},
{"description":"","id":"20200418230605"},
{"description":"","id":"20200420215928"},
{"description":"","id":"20200425182634"},
{"description":"","id":"20200509103709"},
{"description":"","id":"20200515172220"},
{"description":"","id":"20200515195546"},
{"description":"","id":"20200516123847"},
{"description":"","id":"20200524221534"},
{"description":"","id":"20200524224611"},
{"description":"","id":"20200614113230"},
{"description":"","id":"20200621214452"},
{"description":"","id":"20200801183357"},
{"description":"","id":"20200904101559"},
{"description":"","id":"20200905151040"},
{"description":"","id":"20200905232458"},
{"description":"","id":"20200906184746"},
{"description":"","id":"20201025195822"},
{"description":"","id":"20201121181647"},
{"description":"","id":"20201218152741"},
{"description":"","id":"20201218220204"},
{"description":"","id":"20201219145028"},
{"description":"","id":"20210207192805"},
{"description":"","id":"20210209204715"},
{"description":"","id":"20210220222121"},
{"description":"","id":"20210221111953"},
{"description":"","id":"20210321185225"},
{"description":"","id":"20210328191017"},
{"description":"","id":"20210403145503"},
{"description":"","id":"20210403220653"},
{"description":"","id":"20210407170753"},
{"description":"","id":"20210411113105"},
{"description":"","id":"20210411161337"},
{"description":"","id":"20210413131057"},
{"description":"","id":"20210527105701"},
{"description":"","id":"20210603174608"},
{"description":"","id":"20210709191101"},
{"description":"","id":"20210709211508"},
{"description":"","id":"20210711173657"},
{"description":"","id":"20210713213622"},
{"description":"","id":"20210725153703"},
{"description":"","id":"20210727204942"},
{"description":"","id":"20210727211037"},
{"description":"","id":"20210729142940"},
{"description":"","id":"20210802081716"},
{"description":"","id":"20210829194722"}]

Looks duplicated, weird...

I am getting 500's pretty randomly for some add operations (including adding task relations, lists and tasks), but they always work after a few repetitions. I also struggled to obtain proper logs, unfortunately... migration.json: ```sh [{"description":"","id":"SCHEMA_INIT"}, {"description":"","id":"20190324205606"}, {"description":"","id":"20190328074430"}, {"description":"","id":"20190430111111"}, {"description":"","id":"20190511202210"}, {"description":"","id":"20190514192749"}, {"description":"","id":"20190524205441"}, {"description":"","id":"20190718200716"}, {"description":"","id":"20190818210133"}, {"description":"","id":"20190920185205"}, {"description":"","id":"20190922205826"}, {"description":"","id":"20191008194238"}, {"description":"","id":"20191010131430"}, {"description":"","id":"20191207204427"}, {"description":"","id":"20191207220736"}, {"description":"","id":"20200120201756"}, {"description":"","id":"20200219183248"}, {"description":"","id":"20200308205855"}, {"description":"","id":"20200308210130"}, {"description":"","id":"20200322214440"}, {"description":"","id":"20200322214624"}, {"description":"","id":"20200417175201"}, {"description":"","id":"20200418230432"}, {"description":"","id":"20200418230605"}, {"description":"","id":"20200420215928"}, {"description":"","id":"20200425182634"}, {"description":"","id":"20200509103709"}, {"description":"","id":"20200515172220"}, {"description":"","id":"20200515195546"}, {"description":"","id":"20200516123847"}, {"description":"","id":"20200524221534"}, {"description":"","id":"20200524224611"}, {"description":"","id":"20200614113230"}, {"description":"","id":"20200621214452"}, {"description":"","id":"20200801183357"}, {"description":"","id":"20200904101559"}, {"description":"","id":"20200905151040"}, {"description":"","id":"20200905232458"}, {"description":"","id":"20200906184746"}, {"description":"","id":"20201025195822"}, {"description":"","id":"20201121181647"}, {"description":"","id":"20201218152741"}, {"description":"","id":"20201218220204"}, {"description":"","id":"20201219145028"}, {"description":"","id":"20210207192805"}, {"description":"","id":"20210209204715"}, {"description":"","id":"20210220222121"}, {"description":"","id":"20210221111953"}, {"description":"","id":"20210321185225"}, {"description":"","id":"20210328191017"}, {"description":"","id":"20210403145503"}, {"description":"","id":"20210403220653"}, {"description":"","id":"20210407170753"}, {"description":"","id":"20210411113105"}, {"description":"","id":"20210411161337"}, {"description":"","id":"20210413131057"}, {"description":"","id":"20210527105701"}, {"description":"","id":"20210603174608"}, {"description":"","id":"20210709191101"}, {"description":"","id":"20210709211508"}, {"description":"","id":"20210711173657"}, {"description":"","id":"20210713213622"}, {"description":"","id":"20210725153703"}, {"description":"","id":"20210727204942"}, {"description":"","id":"20210727211037"}, {"description":"","id":"20210729142940"}, {"description":"","id":"20210802081716"}, {"description":"","id":"20210829194722"}, {"description":"","id":"SCHEMA_INIT"}, {"description":"","id":"20190324205606"}, {"description":"","id":"20190328074430"}, {"description":"","id":"20190430111111"}, {"description":"","id":"20190511202210"}, {"description":"","id":"20190514192749"}, {"description":"","id":"20190524205441"}, {"description":"","id":"20190718200716"}, {"description":"","id":"20190818210133"}, {"description":"","id":"20190920185205"}, {"description":"","id":"20190922205826"}, {"description":"","id":"20191008194238"}, {"description":"","id":"20191010131430"}, {"description":"","id":"20191207204427"}, {"description":"","id":"20191207220736"}, {"description":"","id":"20200120201756"}, {"description":"","id":"20200219183248"}, {"description":"","id":"20200308205855"}, {"description":"","id":"20200308210130"}, {"description":"","id":"20200322214440"}, {"description":"","id":"20200322214624"}, {"description":"","id":"20200417175201"}, {"description":"","id":"20200418230432"}, {"description":"","id":"20200418230605"}, {"description":"","id":"20200420215928"}, {"description":"","id":"20200425182634"}, {"description":"","id":"20200509103709"}, {"description":"","id":"20200515172220"}, {"description":"","id":"20200515195546"}, {"description":"","id":"20200516123847"}, {"description":"","id":"20200524221534"}, {"description":"","id":"20200524224611"}, {"description":"","id":"20200614113230"}, {"description":"","id":"20200621214452"}, {"description":"","id":"20200801183357"}, {"description":"","id":"20200904101559"}, {"description":"","id":"20200905151040"}, {"description":"","id":"20200905232458"}, {"description":"","id":"20200906184746"}, {"description":"","id":"20201025195822"}, {"description":"","id":"20201121181647"}, {"description":"","id":"20201218152741"}, {"description":"","id":"20201218220204"}, {"description":"","id":"20201219145028"}, {"description":"","id":"20210207192805"}, {"description":"","id":"20210209204715"}, {"description":"","id":"20210220222121"}, {"description":"","id":"20210221111953"}, {"description":"","id":"20210321185225"}, {"description":"","id":"20210328191017"}, {"description":"","id":"20210403145503"}, {"description":"","id":"20210403220653"}, {"description":"","id":"20210407170753"}, {"description":"","id":"20210411113105"}, {"description":"","id":"20210411161337"}, {"description":"","id":"20210413131057"}, {"description":"","id":"20210527105701"}, {"description":"","id":"20210603174608"}, {"description":"","id":"20210709191101"}, {"description":"","id":"20210709211508"}, {"description":"","id":"20210711173657"}, {"description":"","id":"20210713213622"}, {"description":"","id":"20210725153703"}, {"description":"","id":"20210727204942"}, {"description":"","id":"20210727211037"}, {"description":"","id":"20210729142940"}, {"description":"","id":"20210802081716"}, {"description":"","id":"20210829194722"}] ``` Looks duplicated, weird...
Owner

Looks duplicated, weird...

It does indeed. I noticed that as well during my fix but that should work fine now. Does it make a difference if you remove the duplicates?

> Looks duplicated, weird... It does indeed. I noticed that as well during my fix but that should work fine now. Does it make a difference if you remove the duplicates?
Author

I finally have a separate test instance running now where I will test this :)

I finally have a separate test instance running now where I will test this :)
Author

If I repackage a newly exported zip, it goes panic now - but the non-repackaged zip works, even though they are identical in content:

Yes, I understand
panic: runtime error: slice bounds out of range [:-5]

goroutine 1 [running]:
code.vikunja.io/api/pkg/modules/dump.Restore(0x7fffc4d3902d, 0xa, 0x0, 0x0)
	/go/src/code.vikunja.io/api/pkg/modules/dump/restore.go:74 +0x1f7a
code.vikunja.io/api/pkg/cmd.glob..func10(0x1e75960, 0xc0004b7780, 0x1, 0x1)
	/go/src/code.vikunja.io/api/pkg/cmd/restore.go:38 +0x49
github.com/spf13/cobra.(*Command).execute(0x1e75960, 0xc0004b7760, 0x1, 0x1, 0x1e75960, 0xc0004b7760)
	/go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:876 +0x2ec
github.com/spf13/cobra.(*Command).ExecuteC(0x1e751e0, 0xc000000180, 0x200000003, 0xc000000180)
	/go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:990 +0x375
github.com/spf13/cobra.(*Command).Execute(...)
	/go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:918
code.vikunja.io/api/pkg/cmd.Execute()
	/go/src/code.vikunja.io/api/pkg/cmd/cmd.go:44 +0x31
main.main()
	/go/src/code.vikunja.io/api/main.go:22 +0x25
command terminated with exit code 2
❯ unzip -l vikunja-dump_2022-07-20.zip
Archive:  vikunja-dump_2022-07-20.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
      727  2022-07-18 15:37   config.yml
     1427  1980-00-00 00:00   .env
       22  1980-00-00 00:00   VERSION
      722  1980-00-00 00:00   database/team_members.json
      403  1980-00-00 00:00   database/task_assignees.json
     1182  1980-00-00 00:00   database/labels.json
      679  1980-00-00 00:00   database/subscriptions.json
      997  1980-00-00 00:00   database/files.json
    43462  1980-00-00 00:00   database/tasks.json
      124  1980-00-00 00:00   database/teams.json
      216  1980-00-00 00:00   database/link_shares.json
        2  1980-00-00 00:00   database/totp.json
        2  1980-00-00 00:00   database/user_tokens.json
        2  1980-00-00 00:00   database/users_lists.json
        2  1980-00-00 00:00   database/favorites.json
     4604  1980-00-00 00:00   database/task_relations.json
        2  1980-00-00 00:00   database/task_attachments.json
      477  1980-00-00 00:00   database/task_comments.json
        2  1980-00-00 00:00   database/team_lists.json
        2  1980-00-00 00:00   database/users_namespaces.json
     5563  1980-00-00 00:00   database/buckets.json
     5735  1980-00-00 00:00   database/migration.json
      343  1980-00-00 00:00   database/team_namespaces.json
     6774  1980-00-00 00:00   database/users.json
        2  1980-00-00 00:00   database/migration_status.json
    11585  1980-00-00 00:00   database/notifications.json
     2514  1980-00-00 00:00   database/namespaces.json
        2  1980-00-00 00:00   database/unsplash_photos.json
     1376  1980-00-00 00:00   database/label_tasks.json
       90  1980-00-00 00:00   database/task_reminders.json
     3790  1980-00-00 00:00   database/lists.json
        2  1980-00-00 00:00   database/saved_filters.json
---------                     -------
    92832                     32 files
❯ unzip -l 220720.zip #repackaged
Archive:  220720.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
      727  2022-07-18 15:37   config.yml
        0  2022-07-20 23:18   database/
        2  1979-12-31 00:00   database/totp.json
      722  1979-12-31 00:00   database/team_members.json
        2  1979-12-31 00:00   database/migration_status.json
      403  1979-12-31 00:00   database/task_assignees.json
        2  1979-12-31 00:00   database/users_namespaces.json
    11585  1979-12-31 00:00   database/notifications.json
      216  1979-12-31 00:00   database/link_shares.json
     3790  1979-12-31 00:00   database/lists.json
     1182  1979-12-31 00:00   database/labels.json
        2  1979-12-31 00:00   database/users_lists.json
     6774  1979-12-31 00:00   database/users.json
     5735  1979-12-31 00:00   database/migration.json
      997  1979-12-31 00:00   database/files.json
      343  1979-12-31 00:00   database/team_namespaces.json
        2  1979-12-31 00:00   database/team_lists.json
      124  1979-12-31 00:00   database/teams.json
      477  1979-12-31 00:00   database/task_comments.json
     2514  1979-12-31 00:00   database/namespaces.json
        2  1979-12-31 00:00   database/task_attachments.json
        2  1979-12-31 00:00   database/saved_filters.json
       90  1979-12-31 00:00   database/task_reminders.json
    43462  1979-12-31 00:00   database/tasks.json
        2  1979-12-31 00:00   database/favorites.json
        2  1979-12-31 00:00   database/user_tokens.json
        2  1979-12-31 00:00   database/unsplash_photos.json
     1376  1979-12-31 00:00   database/label_tasks.json
     5563  1979-12-31 00:00   database/buckets.json
     4604  1979-12-31 00:00   database/task_relations.json
      679  1979-12-31 00:00   database/subscriptions.json
       22  1979-12-31 00:00   VERSION
---------                     -------
    91405                     32 files
If I repackage a newly exported zip, it goes panic now - but the non-repackaged zip works, even though they are identical in content: ```sh Yes, I understand panic: runtime error: slice bounds out of range [:-5] goroutine 1 [running]: code.vikunja.io/api/pkg/modules/dump.Restore(0x7fffc4d3902d, 0xa, 0x0, 0x0) /go/src/code.vikunja.io/api/pkg/modules/dump/restore.go:74 +0x1f7a code.vikunja.io/api/pkg/cmd.glob..func10(0x1e75960, 0xc0004b7780, 0x1, 0x1) /go/src/code.vikunja.io/api/pkg/cmd/restore.go:38 +0x49 github.com/spf13/cobra.(*Command).execute(0x1e75960, 0xc0004b7760, 0x1, 0x1, 0x1e75960, 0xc0004b7760) /go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:876 +0x2ec github.com/spf13/cobra.(*Command).ExecuteC(0x1e751e0, 0xc000000180, 0x200000003, 0xc000000180) /go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:990 +0x375 github.com/spf13/cobra.(*Command).Execute(...) /go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:918 code.vikunja.io/api/pkg/cmd.Execute() /go/src/code.vikunja.io/api/pkg/cmd/cmd.go:44 +0x31 main.main() /go/src/code.vikunja.io/api/main.go:22 +0x25 command terminated with exit code 2 ``` ```sh ❯ unzip -l vikunja-dump_2022-07-20.zip Archive: vikunja-dump_2022-07-20.zip Length Date Time Name --------- ---------- ----- ---- 727 2022-07-18 15:37 config.yml 1427 1980-00-00 00:00 .env 22 1980-00-00 00:00 VERSION 722 1980-00-00 00:00 database/team_members.json 403 1980-00-00 00:00 database/task_assignees.json 1182 1980-00-00 00:00 database/labels.json 679 1980-00-00 00:00 database/subscriptions.json 997 1980-00-00 00:00 database/files.json 43462 1980-00-00 00:00 database/tasks.json 124 1980-00-00 00:00 database/teams.json 216 1980-00-00 00:00 database/link_shares.json 2 1980-00-00 00:00 database/totp.json 2 1980-00-00 00:00 database/user_tokens.json 2 1980-00-00 00:00 database/users_lists.json 2 1980-00-00 00:00 database/favorites.json 4604 1980-00-00 00:00 database/task_relations.json 2 1980-00-00 00:00 database/task_attachments.json 477 1980-00-00 00:00 database/task_comments.json 2 1980-00-00 00:00 database/team_lists.json 2 1980-00-00 00:00 database/users_namespaces.json 5563 1980-00-00 00:00 database/buckets.json 5735 1980-00-00 00:00 database/migration.json 343 1980-00-00 00:00 database/team_namespaces.json 6774 1980-00-00 00:00 database/users.json 2 1980-00-00 00:00 database/migration_status.json 11585 1980-00-00 00:00 database/notifications.json 2514 1980-00-00 00:00 database/namespaces.json 2 1980-00-00 00:00 database/unsplash_photos.json 1376 1980-00-00 00:00 database/label_tasks.json 90 1980-00-00 00:00 database/task_reminders.json 3790 1980-00-00 00:00 database/lists.json 2 1980-00-00 00:00 database/saved_filters.json --------- ------- 92832 32 files ❯ unzip -l 220720.zip #repackaged Archive: 220720.zip Length Date Time Name --------- ---------- ----- ---- 727 2022-07-18 15:37 config.yml 0 2022-07-20 23:18 database/ 2 1979-12-31 00:00 database/totp.json 722 1979-12-31 00:00 database/team_members.json 2 1979-12-31 00:00 database/migration_status.json 403 1979-12-31 00:00 database/task_assignees.json 2 1979-12-31 00:00 database/users_namespaces.json 11585 1979-12-31 00:00 database/notifications.json 216 1979-12-31 00:00 database/link_shares.json 3790 1979-12-31 00:00 database/lists.json 1182 1979-12-31 00:00 database/labels.json 2 1979-12-31 00:00 database/users_lists.json 6774 1979-12-31 00:00 database/users.json 5735 1979-12-31 00:00 database/migration.json 997 1979-12-31 00:00 database/files.json 343 1979-12-31 00:00 database/team_namespaces.json 2 1979-12-31 00:00 database/team_lists.json 124 1979-12-31 00:00 database/teams.json 477 1979-12-31 00:00 database/task_comments.json 2514 1979-12-31 00:00 database/namespaces.json 2 1979-12-31 00:00 database/task_attachments.json 2 1979-12-31 00:00 database/saved_filters.json 90 1979-12-31 00:00 database/task_reminders.json 43462 1979-12-31 00:00 database/tasks.json 2 1979-12-31 00:00 database/favorites.json 2 1979-12-31 00:00 database/user_tokens.json 2 1979-12-31 00:00 database/unsplash_photos.json 1376 1979-12-31 00:00 database/label_tasks.json 5563 1979-12-31 00:00 database/buckets.json 4604 1979-12-31 00:00 database/task_relations.json 679 1979-12-31 00:00 database/subscriptions.json 22 1979-12-31 00:00 VERSION --------- ------- 91405 32 files ```
Author

Oha! If I just update database/migration.json in the zip, removing all migrations before the second SCHEMA_INIT, and then import that, it is successfull!

Oha! If I just update `database/migration.json` in the zip, removing all migrations before the second `SCHEMA_INIT`, and then import that, it is successfull!
Author

But the error 500's are staying, because the sequence restoration fails for each table:

...
2022-07-20T22:43:33.253442173Z: WARNING ▶ db/Restore 256 Could not reset id sequence for totp_id_seq: pq: setval: value 0 is out of bounds for s
equence "totp_id_seq" (1..9223372036854775807)
2022-07-20T22:43:33.253525168Z: INFO    ▶ dump/Restore 257 Restored table totp
2022-07-20T22:43:33.254904573Z: INFO    ▶ [DATABASE] 258 [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = $2 [public
users_namespaces] - 1.253526ms
2022-07-20T22:43:33.255599727Z: INFO    ▶ [DATABASE] 259 [SQL] SELECT setval('users_namespaces_id_seq', 0, true); [] - 603.996µs
2022-07-20T22:43:33.255673885Z: WARNING ▶ db/Restore 25a Could not reset id sequence for users_namespaces_id_seq: pq: setval: value 0 is out of
bounds for sequence "users_namespaces_id_seq" (1..9223372036854775807)
2022-07-20T22:43:33.255790039Z: INFO    ▶ dump/Restore 25b Restored table users_namespaces
2022-07-20T22:43:33.25694785Z: INFO     ▶ [DATABASE] 25c [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = $2 [public
users_lists] - 1.053895ms
2022-07-20T22:43:33.257488614Z: INFO    ▶ [DATABASE] 25d [SQL] SELECT setval('users_lists_id_seq', 0, true); [] - 455.061µs
2022-07-20T22:43:33.257555408Z: WARNING ▶ db/Restore 25e Could not reset id sequence for users_lists_id_seq: pq: setval: value 0 is out of bound
s for sequence "users_lists_id_seq" (1..9223372036854775807)
2022-07-20T22:43:33.257619033Z: INFO    ▶ dump/Restore 25f Restored table users_lists
...
But the error 500's are staying, because the sequence restoration fails for each table: ```sh ... 2022-07-20T22:43:33.253442173Z: WARNING ▶ db/Restore 256 Could not reset id sequence for totp_id_seq: pq: setval: value 0 is out of bounds for s equence "totp_id_seq" (1..9223372036854775807) 2022-07-20T22:43:33.253525168Z: INFO ▶ dump/Restore 257 Restored table totp 2022-07-20T22:43:33.254904573Z: INFO ▶ [DATABASE] 258 [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = $2 [public users_namespaces] - 1.253526ms 2022-07-20T22:43:33.255599727Z: INFO ▶ [DATABASE] 259 [SQL] SELECT setval('users_namespaces_id_seq', 0, true); [] - 603.996µs 2022-07-20T22:43:33.255673885Z: WARNING ▶ db/Restore 25a Could not reset id sequence for users_namespaces_id_seq: pq: setval: value 0 is out of bounds for sequence "users_namespaces_id_seq" (1..9223372036854775807) 2022-07-20T22:43:33.255790039Z: INFO ▶ dump/Restore 25b Restored table users_namespaces 2022-07-20T22:43:33.25694785Z: INFO ▶ [DATABASE] 25c [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = $2 [public users_lists] - 1.053895ms 2022-07-20T22:43:33.257488614Z: INFO ▶ [DATABASE] 25d [SQL] SELECT setval('users_lists_id_seq', 0, true); [] - 455.061µs 2022-07-20T22:43:33.257555408Z: WARNING ▶ db/Restore 25e Could not reset id sequence for users_lists_id_seq: pq: setval: value 0 is out of bound s for sequence "users_lists_id_seq" (1..9223372036854775807) 2022-07-20T22:43:33.257619033Z: INFO ▶ dump/Restore 25f Restored table users_lists ... ```
xeruf reopened this issue 2022-07-20 22:48:47 +00:00
Author

Actually, it worked fine for task_relations:

2022-07-20T22:43:33.215749473Z: INFO    ▶ [DATABASE] 233 [SQL] SELECT setval('task_relations_id_seq', 38, true); [] - 1.01942ms
2022-07-20T22:43:33.215880178Z: INFO    ▶ dump/Restore 234 Restored table task_relations

But adding the first two task relations still produces error 500's.

My hunch: you need to increment the length by one ;)

Actually, it worked fine for `task_relations`: 2022-07-20T22:43:33.215749473Z: INFO ▶ [DATABASE] 233 [SQL] SELECT setval('task_relations_id_seq', 38, true); [] - 1.01942ms 2022-07-20T22:43:33.215880178Z: INFO ▶ dump/Restore 234 Restored table task_relations But adding the first two task relations still produces error 500's. My hunch: you need to increment the length by one ;)
Author

actually even then the length is a bad heuristic, this was in the table referenced above before I made any additions:

vikunja=> SELECT * FROM task_relations;
 id | task_id | other_task_id | relation_kind | created_by_id |       created       
----+---------+---------------+---------------+---------------+---------------------
  5 |      16 |            17 | subtask       |             1 | 2022-07-08 13:00:59
  6 |      17 |            16 | parenttask    |             1 | 2022-07-08 13:00:59
  7 |      17 |            18 | subtask       |             1 | 2022-07-08 13:01:48
  8 |      18 |            17 | parenttask    |             1 | 2022-07-08 13:01:48
 11 |      17 |            19 | subtask       |             1 | 2022-07-08 13:02:14
 12 |      19 |            17 | parenttask    |             1 | 2022-07-08 13:02:14
 21 |      16 |            27 | subtask       |             1 | 2022-07-08 13:36:28
 22 |      27 |            16 | parenttask    |             1 | 2022-07-08 13:36:28
 23 |      16 |            28 | subtask       |             1 | 2022-07-08 13:39:26
 24 |      28 |            16 | parenttask    |             1 | 2022-07-08 13:39:26
 25 |      31 |            29 | subtask       |             1 | 2022-07-08 14:27:20
 26 |      29 |            31 | parenttask    |             1 | 2022-07-08 14:27:20
 27 |      31 |            30 | subtask       |             1 | 2022-07-08 14:27:20
 28 |      30 |            31 | parenttask    |             1 | 2022-07-08 14:27:20
 31 |      52 |            53 | subtask       |             1 | 2022-07-09 01:05:16
 32 |      53 |            52 | parenttask    |             1 | 2022-07-09 01:05:16
 33 |      53 |            54 | subtask       |             1 | 2022-07-09 01:05:16
 34 |      54 |            53 | parenttask    |             1 | 2022-07-09 01:05:16
 35 |      53 |            55 | subtask       |             1 | 2022-07-09 01:05:16
 36 |      55 |            53 | parenttask    |             1 | 2022-07-09 01:05:16
 37 |      53 |            56 | subtask       |             1 | 2022-07-09 01:05:16
 38 |      56 |            53 | parenttask    |             1 | 2022-07-09 01:05:16
  1 |     101 |           102 | subtask       |            10 | 2022-07-18 13:54:18
  2 |     102 |           101 | parenttask    |            10 | 2022-07-18 13:54:18
  3 |     101 |           103 | subtask       |            10 | 2022-07-18 13:54:39
  4 |     103 |           101 | parenttask    |            10 | 2022-07-18 13:54:39
  9 |     104 |           105 | subtask       |            10 | 2022-07-18 14:02:30
 10 |     105 |           104 | parenttask    |            10 | 2022-07-18 14:02:30
 13 |     107 |           104 | parenttask    |            10 | 2022-07-18 14:06:15
 14 |     104 |           107 | subtask       |            10 | 2022-07-18 14:06:15
 15 |      94 |           108 | subtask       |             1 | 2022-07-18 14:07:16
 16 |     108 |            94 | parenttask    |             1 | 2022-07-18 14:07:16
 18 |      67 |            94 | parenttask    |             1 | 2022-07-18 14:07:24
 20 |      67 |            94 | parenttask    |             1 | 2022-07-18 14:07:41
 29 |      94 |           109 | subtask       |             1 | 2022-07-18 14:08:00
 30 |     109 |            94 | parenttask    |             1 | 2022-07-18 14:08:00
 39 |      94 |           110 | subtask       |             1 | 2022-07-18 14:08:16
 40 |     110 |            94 | parenttask    |             1 | 2022-07-18 14:08:16

You need to actually determine the highest id used to far.

actually even then the length is a bad heuristic, this was in the table referenced above before I made any additions: ```sh vikunja=> SELECT * FROM task_relations; id | task_id | other_task_id | relation_kind | created_by_id | created ----+---------+---------------+---------------+---------------+--------------------- 5 | 16 | 17 | subtask | 1 | 2022-07-08 13:00:59 6 | 17 | 16 | parenttask | 1 | 2022-07-08 13:00:59 7 | 17 | 18 | subtask | 1 | 2022-07-08 13:01:48 8 | 18 | 17 | parenttask | 1 | 2022-07-08 13:01:48 11 | 17 | 19 | subtask | 1 | 2022-07-08 13:02:14 12 | 19 | 17 | parenttask | 1 | 2022-07-08 13:02:14 21 | 16 | 27 | subtask | 1 | 2022-07-08 13:36:28 22 | 27 | 16 | parenttask | 1 | 2022-07-08 13:36:28 23 | 16 | 28 | subtask | 1 | 2022-07-08 13:39:26 24 | 28 | 16 | parenttask | 1 | 2022-07-08 13:39:26 25 | 31 | 29 | subtask | 1 | 2022-07-08 14:27:20 26 | 29 | 31 | parenttask | 1 | 2022-07-08 14:27:20 27 | 31 | 30 | subtask | 1 | 2022-07-08 14:27:20 28 | 30 | 31 | parenttask | 1 | 2022-07-08 14:27:20 31 | 52 | 53 | subtask | 1 | 2022-07-09 01:05:16 32 | 53 | 52 | parenttask | 1 | 2022-07-09 01:05:16 33 | 53 | 54 | subtask | 1 | 2022-07-09 01:05:16 34 | 54 | 53 | parenttask | 1 | 2022-07-09 01:05:16 35 | 53 | 55 | subtask | 1 | 2022-07-09 01:05:16 36 | 55 | 53 | parenttask | 1 | 2022-07-09 01:05:16 37 | 53 | 56 | subtask | 1 | 2022-07-09 01:05:16 38 | 56 | 53 | parenttask | 1 | 2022-07-09 01:05:16 1 | 101 | 102 | subtask | 10 | 2022-07-18 13:54:18 2 | 102 | 101 | parenttask | 10 | 2022-07-18 13:54:18 3 | 101 | 103 | subtask | 10 | 2022-07-18 13:54:39 4 | 103 | 101 | parenttask | 10 | 2022-07-18 13:54:39 9 | 104 | 105 | subtask | 10 | 2022-07-18 14:02:30 10 | 105 | 104 | parenttask | 10 | 2022-07-18 14:02:30 13 | 107 | 104 | parenttask | 10 | 2022-07-18 14:06:15 14 | 104 | 107 | subtask | 10 | 2022-07-18 14:06:15 15 | 94 | 108 | subtask | 1 | 2022-07-18 14:07:16 16 | 108 | 94 | parenttask | 1 | 2022-07-18 14:07:16 18 | 67 | 94 | parenttask | 1 | 2022-07-18 14:07:24 20 | 67 | 94 | parenttask | 1 | 2022-07-18 14:07:41 29 | 94 | 109 | subtask | 1 | 2022-07-18 14:08:00 30 | 109 | 94 | parenttask | 1 | 2022-07-18 14:08:00 39 | 94 | 110 | subtask | 1 | 2022-07-18 14:08:16 40 | 110 | 94 | parenttask | 1 | 2022-07-18 14:08:16 ``` You need to actually determine the highest id used to far.
Owner

There's probably a really fancy SQL statement which updates and resets all sequences in postgres and dies a better job than my quick and dirty solution... I'll take another look.

There's probably a really fancy SQL statement which updates and resets all sequences in postgres and dies a better job than my quick and dirty solution... I'll take another look.
Owner

This looks promising: https://wiki.postgresql.org/wiki/Fixing_Sequences

Does it work if you apply the fix from there?

This looks promising: https://wiki.postgresql.org/wiki/Fixing_Sequences Does it work if you apply the fix from there?
Author

Yes, fantastic!

Yes, fantastic!
Author

So the generic, codified query should be:

x.Query("SELECT setval('" + idSequence + "', COALESCE(MAX(id), 1) );
So the generic, codified query should be: x.Query("SELECT setval('" + idSequence + "', COALESCE(MAX(id), 1) );
Owner

Changed in 0f555b7ec7 - please test again with the next unstable build.

Changed in 0f555b7ec74ad493d2f70a4f4040db333943dc1c - please test again with the next unstable build.
Author

Seems to be fine now, though I did not test with the old exports.
Will report if an issue pops up again.

Seems to be fine now, though I did not test with the old exports. Will report if an issue pops up again.
xeruf closed this issue 2022-08-02 22:57:27 +00:00
Author

I wasted hours this week because the fix as available in 0.20.4 did not work properly.
Finally found this issue again and the exact same solution above (https://wiki.postgresql.org/wiki/Fixing_Sequences) fixed it again...

I wasted hours this week because the fix as available in 0.20.4 did not work properly. Finally found this issue again and the exact same solution above (https://wiki.postgresql.org/wiki/Fixing_Sequences) fixed it again...
xeruf changed title from Migrate to different Database type to Postgresql ssequences not updated correctly upon dump import 2024-02-16 18:36:55 +00:00
xeruf reopened this issue 2024-02-16 18:36:58 +00:00
Sign in to join this conversation.
No Milestone
No Assignees
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: vikunja/vikunja#1199
No description provided.