migration failed: index UQE_buckets_id already exists #2243

New Issue

BV · 2024-03-29T18:22:42Z

BV commented

2024-03-29 18:22:42 +00:00

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

Are you using sqlite?

BV commented

2024-03-29 18:34:56 +00:00

Yes. Sorry forgot to mention that.

renovate referenced this issue from a commit

2024-04-01 15:07:09 +00:00

chore(deps): update dependency vue-tsc to v0.40.1 (#2243)

konrad referenced this issue from a commit

2024-04-07 10:50:40 +00:00

fix: drop bucket index before recreating it

konrad referenced this issue from a commit

2024-04-07 10:50:54 +00:00

fix: drop bucket index before recreating it

konrad closed this issue

2024-04-07 10:50:54 +00:00

konrad commented

2024-04-07 10:51:41 +00:00

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

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

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 referenced this issue from a commit

2024-04-13 12:20:43 +00:00

fix(views): check if bucket index already exists before creating new index

konrad closed this issue

2024-04-13 12:20:43 +00:00

konrad commented

2024-04-13 12:21:35 +00:00

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

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

@BV which Vikunja version is that?

BV commented

2024-04-13 23:40:41 +00:00

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

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

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

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

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

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

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

ce72 commented

2024-04-14 15:24:51 +00:00

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

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

ce72 commented

2024-04-19 20:48:05 +00:00

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

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 referenced this issue from a commit

2024-04-21 11:50:34 +00:00

fix(migration): remove buckets table name when dropping index

konrad commented

2024-04-21 11:50:40 +00:00

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

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

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.

Sign in to join this conversation.