vikunja/vikunja
5
48
Fork 86
Code Issues 150 Pull Requests 35 Packages Releases 37 Activity

migration failed: index UQE_buckets_id already exists #2243

New Issue
Closed
opened 2024-03-29 18:22:42 +00:00 by BV · 23 comments
BV commented 2024-03-29 18:22:42 +00:00
Copy Link

Description

When attempting to test the most recent unstable build I get the following error in the container logs and Vikunja won't start

2024-03-29T11:14:03.723877682-07:00: INFO	▶ config/InitConfig 001 No config file found, using default or config from environment variables.
2024-03-29T11:14:03.731626701-07:00: CRITICAL	▶ migration/Migrate 06a Migration failed: migration 20240315093418 failed: index UQE_buckets_id already exists

Upgrading from Version: v0.23.0 (according to the web UI) to whatever the most recent 'unstable' image is. Portainer shows this for the image hash sha256:8c1e61427c3fdfc5316654b6ec8b395117929562b11fbf1a49614e9564790df6

Vikunja Version

0.23.0

Browser and version

n/a

Can you reproduce the bug on the Vikunja demo site?

No

Screenshots

No response

### Description When attempting to test the most recent unstable build I get the following error in the container logs and Vikunja won't start ``` 2024-03-29T11:14:03.723877682-07:00: INFO ▶ config/InitConfig 001 No config file found, using default or config from environment variables. 2024-03-29T11:14:03.731626701-07:00: CRITICAL ▶ migration/Migrate 06a Migration failed: migration 20240315093418 failed: index UQE_buckets_id already exists ``` Upgrading from Version: v0.23.0 (according to the web UI) to whatever the most recent 'unstable' image is. Portainer shows this for the image hash sha256:8c1e61427c3fdfc5316654b6ec8b395117929562b11fbf1a49614e9564790df6 ### Vikunja Version 0.23.0 ### Browser and version n/a ### Can you reproduce the bug on the Vikunja demo site? No ### Screenshots _No response_
BV added the
kind/bug
 label 2024-03-29 18:22:42 +00:00
konrad commented 2024-03-29 18:30:15 +00:00
Owner
Copy Link

Are you using sqlite?

Are you using sqlite?
BV commented 2024-03-29 18:34:56 +00:00
Author
Copy Link

Yes. Sorry forgot to mention that.

Yes. Sorry forgot to mention that.
konrad commented 2024-04-07 10:51:41 +00:00
Owner
Copy Link

Should be fixed with ca33c0b2bc - please check with the next unstable build (ready in ~45min) if the problem went away.

Should be fixed with https://kolaente.dev/vikunja/vikunja/commit/ca33c0b2bcaf9de018cecca1051bc4c3b176ce61 - please check with the next unstable build (ready in ~45min) if the problem went away.
BV commented 2024-04-07 17:25:20 +00:00
Author
Copy Link

Same error still exists I'm afraid. This is my compose file for reference and the db and files directories were copied over from a working vikunja:lastest instance.

compose.yaml

version: "3"
services:
  vikunja:
    image: vikunja/vikunja:unstable
    container_name: vikunja
    environment:
      VIKUNJA_SERVICE_JWTSECRET: $VIKUNJA_SERVICE_JWTSECRET
      VIKUNJA_SERVICE_PUBLICURL: https://vikunja.********.ca
      VIKUNJA_SERVICE_ENABLEREGISTRATION: false
      VIKUNJA_SERVICE_TIMEZONE: America/Vancouver
      VIKUNJA_DATABASE_PATH: /db/vikunja.db
      TZ: America/Vancouver
    ports:
      - 3456:3456
    volumes:
      - $APP_DATA_PATH/files:/app/vikunja/files
      - $APP_DATA_PATH/db:/db
    restart: unless-stopped

.env

APP_NAME=vikunja
APP_DATA_PATH=.
VIKUNJA_SERVICE_JWTSECRET=**********

Docker images

$ docker images | grep vik
vikunja/vikunja                        unstable           4a51b92edeb3   5 hours ago     88.2MB
vikunja/vikunja                        latest             255441410643   8 weeks ago     106MB

Docker log output

vikunja         | 2024-04-07T10:25:10.343669461-07:00: INFO    ▶ config/InitConfig 001 No config file found, using default or config from environment variables.
vikunja         | 2024-04-07T10:25:10.355268804-07:00: CRITICAL        ▶ migration/Migrate 06a Migration failed: migration 20240315093418 failed: index UQE_buckets_id already exists
vikunja exited with code 1
Same error still exists I'm afraid. This is my compose file for reference and the db and files directories were copied over from a working vikunja:lastest instance. compose.yaml ```yaml version: "3" services: vikunja: image: vikunja/vikunja:unstable container_name: vikunja environment: VIKUNJA_SERVICE_JWTSECRET: $VIKUNJA_SERVICE_JWTSECRET VIKUNJA_SERVICE_PUBLICURL: https://vikunja.********.ca VIKUNJA_SERVICE_ENABLEREGISTRATION: false VIKUNJA_SERVICE_TIMEZONE: America/Vancouver VIKUNJA_DATABASE_PATH: /db/vikunja.db TZ: America/Vancouver ports: - 3456:3456 volumes: - $APP_DATA_PATH/files:/app/vikunja/files - $APP_DATA_PATH/db:/db restart: unless-stopped ``` .env ``` APP_NAME=vikunja APP_DATA_PATH=. VIKUNJA_SERVICE_JWTSECRET=********** ``` Docker images ``` $ docker images | grep vik vikunja/vikunja unstable 4a51b92edeb3 5 hours ago 88.2MB vikunja/vikunja latest 255441410643 8 weeks ago 106MB ``` Docker log output ``` vikunja | 2024-04-07T10:25:10.343669461-07:00: INFO ▶ config/InitConfig 001 No config file found, using default or config from environment variables. vikunja | 2024-04-07T10:25:10.355268804-07:00: CRITICAL ▶ migration/Migrate 06a Migration failed: migration 20240315093418 failed: index UQE_buckets_id already exists vikunja exited with code 1 ```
BV reopened this issue 2024-04-07 17:25:20 +00:00
ce72 commented 2024-04-13 10:33:30 +00:00
Contributor
Copy Link

I get exactly the same error with a freshly pulled :unstable image when I start with a copy of my productive Sqlite vikunja.db.

  • Almost exactly: It says: ▶ migration/Migrate 91e Migration failed: migration 20240315093418 failed: index UQE_buckets_id already exists
I get exactly the same error with a freshly pulled :unstable image when I start with a copy of my productive Sqlite vikunja.db. - Almost exactly: It says: ▶ migration/Migrate **91e** Migration failed: migration 20240315093418 failed: index UQE_buckets_id already exists
konrad commented 2024-04-13 12:21:35 +00:00
Owner
Copy Link

Should be fixed in bf3c8ac9da. Please check with the next unstable build (ready in ~45min).

If the migration went through, please run this on your db to ensure the index is present:

SELECT *
FROM sqlite_master
WHERE type = 'index'
  and tbl_name = 'buckets'
  and name = 'UQE_buckets_id';
Should be fixed in https://kolaente.dev/vikunja/vikunja/commit/bf3c8ac9da6234ae8e06ca9ba403eef8cac72f7a. Please check with the next unstable build (ready in ~45min). If the migration went through, please run this on your db to ensure the index is present: ``` SELECT * FROM sqlite_master WHERE type = 'index' and tbl_name = 'buckets' and name = 'UQE_buckets_id'; ```
BV commented 2024-04-13 22:03:06 +00:00
Author
Copy Link

Still the same error for me unfortunately.

$ docker images | grep vikunja
vikunja/vikunja                        unstable           7bb458a2bab8   3 hours ago     88.3MB
vikunja/vikunja                        latest             255441410643   2 months ago    106MB

vikunja  | 2024-04-13T15:01:40.343624579-07:00: CRITICAL       ▶ migration/Migrate 06a Migration failed: migration 20240315093418 failed: index UQE_buckets_id already exists
Still the same error for me unfortunately. ``` $ docker images | grep vikunja vikunja/vikunja unstable 7bb458a2bab8 3 hours ago 88.3MB vikunja/vikunja latest 255441410643 2 months ago 106MB ``` ``` vikunja | 2024-04-13T15:01:40.343624579-07:00: CRITICAL ▶ migration/Migrate 06a Migration failed: migration 20240315093418 failed: index UQE_buckets_id already exists ```
BV reopened this issue 2024-04-13 22:03:06 +00:00
konrad commented 2024-04-13 22:03:40 +00:00
Owner
Copy Link

@BV which Vikunja version is that?

@BV which Vikunja version is that?
BV commented 2024-04-13 23:40:41 +00:00
Author
Copy Link

If you mean which version am I upgrading from I can't get the version number right now as I'm on the road and having trouble with my VPN. Whatever version of the :latest image is show in the comment above with hash 255441410643. I was pretty sure it was up to date (i.e. re-pulled within the last week) but I see the hash does not match what's show for the latest tag in docker hub. It should be very recent though.

I can verify once I resolve the VPN issues or return home in a day or two.

If you mean which version am I upgrading from I can't get the version number right now as I'm on the road and having trouble with my VPN. Whatever version of the `:latest` image is show in the comment above with hash 255441410643. I was pretty sure it was up to date (i.e. re-pulled within the last week) but I see the hash does not match what's show for the latest tag in docker hub. It should be very recent though. I can verify once I resolve the VPN issues or return home in a day or two.
BV commented 2024-04-14 02:05:21 +00:00
Author
Copy Link

Version: v0.23.0 is what I'm running and trying to upgrade from.

The image is current according to docker pull. The number shown by docker images is the image ID whereas in docker hubs it's actual hashes for the index digest, manifest digest, etc.

$ docker pull vikunja/vikunja
Using default tag: latest
latest: Pulling from vikunja/vikunja
Digest: sha256:c824f99b0b09b7f03a1d77ad6691fbce38edf8d737e73e3242d8b87dd96d21e0
Status: Image is up to date for vikunja/vikunja:latest
docker.io/vikunja/vikunja:latest

Current :latest in docker hub
image

Version: v0.23.0 is what I'm running and trying to upgrade from. The image is current according to `docker pull`. The number shown by `docker images` is the image ID whereas in docker hubs it's actual hashes for the index digest, manifest digest, etc. ``` $ docker pull vikunja/vikunja Using default tag: latest latest: Pulling from vikunja/vikunja Digest: sha256:c824f99b0b09b7f03a1d77ad6691fbce38edf8d737e73e3242d8b87dd96d21e0 Status: Image is up to date for vikunja/vikunja:latest docker.io/vikunja/vikunja:latest ``` Current `:latest` in docker hub ![image](/attachments/95cc7135-28f4-440e-92dc-40db594f1179)
image.png
108 KiB
ce72 commented 2024-04-14 06:06:37 +00:00
Contributor
Copy Link

Same here in current vikunja/vikunja:unstable: "vikunja/vikunja@sha256:3de36d8c780791262f5b99828260f653c064e21139037f0293db9ec0eba4f523"

After looking at https://kolaente.dev/vikunja/vikunja/src/branch/main/pkg/migration/20240315093418.go#L102 I believe that this line should be moved a little downwards, after renaming buckets_dg_tmp to buckets.

Same here in current vikunja/vikunja:unstable: "vikunja/vikunja@sha256:3de36d8c780791262f5b99828260f653c064e21139037f0293db9ec0eba4f523" After looking at https://kolaente.dev/vikunja/vikunja/src/branch/main/pkg/migration/20240315093418.go#L102 I believe that this line should be moved a little downwards, after renaming buckets_dg_tmp to buckets.
konrad commented 2024-04-14 08:53:48 +00:00
Owner
Copy Link

I need the actual Vikunja version of the version you've upgraded to, not the docker image hash. You can get it in the web frontend top left -> about or by running vikunja version in the docker container. The docker image hash changes every time a new version is published and depends on the actual image you're pulling (by arch).

What I find interesting about the migration is the index will only ever be created if it does not exist, that means it should not throw an error about the index already existing…

I need the actual Vikunja version of the version you've upgraded to, not the docker image hash. You can get it in the web frontend top left -> about or by running `vikunja version` in the docker container. The docker image hash changes every time a new version is published and depends on the actual image you're pulling (by arch). What I find interesting about the migration is the index will only ever be created if it does not exist, that means it should not throw an error about the index already existing…
ce72 commented 2024-04-14 11:38:41 +00:00
Contributor
Copy Link

Vikunja version: v0.23.0+523-2d084c091e

I think line 102 in https://kolaente.dev/vikunja/vikunja/src/branch/main/pkg/migration/20240315093418.go#L102 does not do anything.
In line 104 you drop the table anyway. I guess the DROP index should be between lines 107 and 109.

Vikunja version: v0.23.0+523-2d084c091e I think line 102 in https://kolaente.dev/vikunja/vikunja/src/branch/main/pkg/migration/20240315093418.go#L102 does not do anything. In line 104 you drop the table anyway. I guess the DROP index should be between lines 107 and 109.
konrad commented 2024-04-14 11:47:49 +00:00
Owner
Copy Link

Does it work if you execute the statement manually in that way?

Does it work if you execute the statement manually in that way?
ce72 commented 2024-04-14 15:24:51 +00:00
Contributor
Copy Link

Yes, that seems to work. After the migration has failed, I ran the lines 85-110 manually and got no error. Strange.

Yes, that seems to work. After the migration has failed, I ran the lines 85-110 manually and got no error. Strange.
konrad commented 2024-04-16 16:15:07 +00:00
Owner
Copy Link

If you then run the migration again, does that work?

If you then run the migration again, does that work?
ce72 commented 2024-04-19 20:48:05 +00:00
Contributor
Copy Link

No: Migration failed: migration 20240315093418 failed: Cannot add a NOT NULL column with default value NULL

No: `Migration failed: migration 20240315093418 failed: Cannot add a NOT NULL column with default value NULL`
ce72 commented 2024-04-19 21:16:36 +00:00
Contributor
Copy Link

Maybe in line https://kolaente.dev/vikunja/vikunja/src/branch/main/pkg/migration/20240315093418.go#L102
it should be
drop index UQE_buckets_id; (without buckets.)

When I perform this statement on the database manually before starting the container the migration runs successfully.

Maybe in line https://kolaente.dev/vikunja/vikunja/src/branch/main/pkg/migration/20240315093418.go#L102 it should be `drop index UQE_buckets_id;` (without `buckets.`) When I perform this statement on the database manually before starting the container the migration runs successfully.
konrad commented 2024-04-21 11:50:40 +00:00
Owner
Copy Link

I've changed what you suggested in e88f95e501. Please check if that works with the next unstable build.

I've changed what you suggested in e88f95e501ee70fa96742571fb2acf8352918ceb. Please check if that works with the next unstable build.
ce72 commented 2024-04-22 18:13:47 +00:00
Contributor
Copy Link

No, that wasn't the reason, unfortunately. The error does still occur.
I did some debugging locally. The error is thrown from line 40.

With VIKUNJA_LOG_DATABASE=on it says:

2024-04-22T20:05:23.0232888+02:00: WARNING      ▶ [DATABASE] 93a Table buckets Column id db nullable is true, struct nullable is false
2024-04-22T20:05:23.0237946+02:00: WARNING      ▶ [DATABASE] 93b Table buckets Column project_id db nullable is true, struct nullable is false
2024-04-22T20:05:23.0262079+02:00: INFO ▶ [DATABASE] 93c [SQL] ALTER TABLE `buckets` ADD `project_view_id` INTEGER DEFAULT 0 NOT NULL [] - 2.3892ms
2024-04-22T20:05:23.0262079+02:00: INFO ▶ [DATABASE] 93d [SQL] CREATE UNIQUE INDEX `UQE_buckets_id` ON `buckets` (`id`) []
2024-04-22T20:05:23.0262079+02:00: CRITICAL     ▶ migration/Migrate 93f Migration failed: migration 20240315093418 failed: index UQE_buckets_id already exists

I can remove unique pk from line 26 and the migration runs successfully. (Even without IF EXISTS / IF NOT EXISTS in the subsequent SQL.)
Can you please have another look at it?

No, that wasn't the reason, unfortunately. The error does still occur. I did some debugging locally. The error is thrown from [line 40](https://kolaente.dev/vikunja/vikunja/src/branch/main/pkg/migration/20240315093418.go#L40). With `VIKUNJA_LOG_DATABASE=on` it says: ``` 2024-04-22T20:05:23.0232888+02:00: WARNING ▶ [DATABASE] 93a Table buckets Column id db nullable is true, struct nullable is false 2024-04-22T20:05:23.0237946+02:00: WARNING ▶ [DATABASE] 93b Table buckets Column project_id db nullable is true, struct nullable is false 2024-04-22T20:05:23.0262079+02:00: INFO ▶ [DATABASE] 93c [SQL] ALTER TABLE `buckets` ADD `project_view_id` INTEGER DEFAULT 0 NOT NULL [] - 2.3892ms 2024-04-22T20:05:23.0262079+02:00: INFO ▶ [DATABASE] 93d [SQL] CREATE UNIQUE INDEX `UQE_buckets_id` ON `buckets` (`id`) [] 2024-04-22T20:05:23.0262079+02:00: CRITICAL ▶ migration/Migrate 93f Migration failed: migration 20240315093418 failed: index UQE_buckets_id already exists ``` I can remove `unique pk` from [line 26](https://kolaente.dev/vikunja/vikunja/src/branch/main/pkg/migration/20240315093418.go#L26) and the migration runs successfully. (Even without IF EXISTS / IF NOT EXISTS in the subsequent SQL.) Can you please have another look at it?
konrad commented 2024-04-23 13:08:55 +00:00
Owner
Copy Link

Huh. This is a bug in sqlite then, because it should not try to create an index when it already exists.

I'll take another look.

Huh. This is a bug in sqlite then, because it should not try to create an index when it already exists. I'll take another look.
konrad commented 2024-06-04 06:46:52 +00:00
Owner
Copy Link

I didn't really figure out why it did not work, but I went ahead and removed the unique pk from the struct in a38e768895, as you suggested. Does that fix it for you?

I didn't really figure out why it did not work, but I went ahead and removed the `unique pk` from the struct in https://kolaente.dev/vikunja/vikunja/commit/a38e768895a9343569eb512ca9d0cce2726c6b6f, as you suggested. Does that fix it for you?
👍 1
ce72 commented 2024-06-04 14:23:14 +00:00
Contributor
Copy Link

Yes. With the recent unstable image I can migrate my 0.23.0 database file.

Yes. With the recent unstable image I can migrate my 0.23.0 database file.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
dependencies
duplicate
This issue or pull request already exists
help wanted
Need some help
invalid
Something is wrong
kind/bug
Something is not working
kind/feature
New feature
needs reproduction
question
More information is needed
security
wontfix
This won't be fixed
No Label
kind/bug
Milestone
No items
No Milestone
Assignees
Clear assignees
No Assignees
3 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: vikunja/vikunja#2243
No description provided.
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?