Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQLiteError: no such table: user #133

Open
tehniemer opened this issue Sep 28, 2024 · 26 comments · Fixed by #150
Open

SQLiteError: no such table: user #133

tehniemer opened this issue Sep 28, 2024 · 26 comments · Fixed by #150
Assignees
Labels
bug Something isn't working

Comments

@tehniemer
Copy link

tehniemer commented Sep 28, 2024

Followed the docker install instructions and was presented with the following error when trying to access the service.

SQLiteError: no such table: user
�
1
      at prepare (bun:sqlite:280:10)
      at prepareQuery (/app/node_modules/drizzle-orm/bun-sqlite/session.js:20:30)
      at _prepare (/app/node_modules/drizzle-orm/sqlite-core/query-builders/select.js:572:73)
      at all (/app/node_modules/drizzle-orm/sqlite-core/query-builders/select.js:588:17)
      at execute (/app/node_modules/drizzle-orm/sqlite-core/query-builders/select.js:596:19)
      at then (/app/node_modules/drizzle-orm/query-promise.js:21:17)
15 |   logger;
16 |   exec(query) {
17 |     this.client.exec(query);
18 |   }
19 |   prepareQuery(query, fields, executeMethod, isResponseInArrayMode, customResultMapper) {
20 |     const stmt = this.client.prepare(query.sql);

Here's my compose snippet

services:
  grimoire:
    image: goniszewski/grimoire:develop
    build:
      context: .
      dockerfile: Dockerfile
    container_name: grimoire
    restart: unless-stopped
    networks:
      - traefik
    ports:
      - $GRIMOIRE_PORT:5173
    security_opt:
      - no-new-privileges=true
    logging:
      options:
        max-size: 2m
        max-file: "3"
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /opt/docker/grimoire:/app/data/
    environment:
      - PORT=5173
      - PUBLIC_HTTPS_ONLY=false
      - PUBLIC_SIGNUP_DISABLED=false
    labels:
      - homepage.group=Containers
      - homepage.name=Grimoire
      - homepage.icon=grimoire
      - homepage.description=Bookmark Management
      - homepage.href=https://grimoire.$DOMAINNAME
      - homepage.siteMonitor=http://$HOST:$GRIMOIRE_PORT
      - traefik.enable=true
      - traefik.http.routers.grimoire-rtr.entrypoints=https
      - traefik.http.routers.grimoire-rtr.rule=Host(`grimoire.$DOMAINNAME`)
      - traefik.http.routers.grimoire-rtr.middlewares=chain-no-auth@file # No Authentication
      - traefik.http.routers.grimoire-rtr.service=grimoire-svc
      - traefik.http.services.grimoire-svc.loadbalancer.server.port=5173
@goniszewski
Copy link
Owner

Hello @tehniemer!

Could you try to set up the app again using the latest tag for the Docker image (develop may be missing some latest hotfixes)?

Also, make sure to remove existing Docker volume used by Grimoire and allow it to create a new one.

@tehniemer
Copy link
Author

tehniemer commented Sep 28, 2024

Sure thing, after removing the existing mountpoint and changing to the latest tag I get the following errors

$ bun run ./migrate
1 | import { Database } from 'bun:sqlite';
2 | import { drizzle } from 'drizzle-orm/bun-sqlite';
3 | import { migrate } from 'drizzle-orm/bun-sqlite/migrator';
4 | 
5 | const sqlite = new Database('data/db.sqlite');
                   ^
SQLiteError: unable to open database file
 errno: 14
  code: "SQLITE_CANTOPEN"
      at new Database (bun:sqlite:178:14)
      at /app/migrate.js:5:16
Bun v1.1.29 (Linux x64 baseline)
error: script "run-migrations" exited with code 1
$ bun run ./migrate
1 | import { Database } from 'bun:sqlite';
2 | import { drizzle } from 'drizzle-orm/bun-sqlite';
3 | import { migrate } from 'drizzle-orm/bun-sqlite/migrator';
4 | 
5 | const sqlite = new Database('data/db.sqlite');
                   ^

Maybe a permissions issue with the bind mount? What UID and GID does the container run?

@goniszewski
Copy link
Owner

I've tried to reproduce your issue, but unsuccessfully. For the permissions used, please check this line in the Dockerfile: https://github.com/goniszewski/grimoire/blob/main/Dockerfile#L11

@victornavorskie
Copy link

same issue here

@DavidDotCheck
Copy link

same, doesn't work for me either

@DavidDotCheck
Copy link

Something fun that I noticed is that running adduser --disabled-password --gecos '' grimoire and mkdir -p /app/data && chown -R grimoire:grimoire /app/data && chmod 755 /app/data inside the container, then switching the tag to latest fixes both issues:

$ sudo rm -rf /etc/grimoire/data/
$ sudo nano /etc/grimoire/docker-compose.yml 
$ sudo docker-compose up -d
WARNING: The PORT variable is not set. Defaulting to a blank string.
Creating grimoire ... done
$docker exec -it 43f8aa47b259 bash
root@43f8aa47b259:/app# mkdir -p /app/data && chown -R grimoire:grimoire /app/data && chmod 755 /app/data
chown: invalid user: 'grimoire:grimoire'
root@43f8aa47b259:/app# adduser --disabled-password --gecos '' grimoire
Adding user `grimoire' ...
Adding new group `grimoire' (1001) ...
Adding new user `grimoire' (1001) with group `grimoire' ...
Creating home directory `/home/grimoire' ...
Copying files from `/etc/skel' ...
root@43f8aa47b259:/app# mkdir -p /app/data && chown -R grimoire:grimoire /app/data && chmod 755 /app/data
root@43f8aa47b259:/app# exit
exit
$ sudo docker-compose down
WARNING: The PORT variable is not set. Defaulting to a blank string.
Stopping grimoire ... 
^CERROR: Aborting.
$  docker rm -f $ID
43f8aa47b259
$ sudo nano /etc/grimoire/docker-compose.yml 
$ sudo docker-compose up -d
WARNING: The PORT variable is not set. Defaulting to a blank string.
Creating grimoire ... done

Just doing docker-compose up after running the commands doesn't fix the issue because now the user table doesn't exist.

@AnonymousX86
Copy link

I've found an interesting thing. Using a directory mount causes the error.

# This doesn't work
volumes:
  - ./data:/app/data

Removing volumes makes the app start, as well as using Docker volume.

# This works
# volumes
#   -./data:/app/data
# This works
volumes:
  grimoire_data:/app/data

@tehniemer
Copy link
Author

tehniemer commented Oct 18, 2024

That usually means it's a directory permission issue. If the container doesn't run as root or chown the directory after creation this tends to happen.

I've removed this from my stack so I'm not able to test this.

@goniszewski
Copy link
Owner

I may have introduced some unnecessary permission changes in the latest update to Dockerfile. Sorry for that, I will look into this in the next days.

@AnonymousX86
Copy link

Best would be to make use of PUID and PGID.

@Roalkege
Copy link

image

same issue with unraid.

@goniszewski goniszewski self-assigned this Nov 12, 2024
@goniszewski goniszewski added the bug Something isn't working label Nov 12, 2024
@goniszewski goniszewski linked a pull request Nov 14, 2024 that will close this issue
@goniszewski
Copy link
Owner

Possible fix for this issue is now being released under the preview tag of the Docker image: https://github.com/goniszewski/grimoire/actions/runs/11844872252

Please let me know if it's still not working!

@Roalkege
Copy link

For me, this does not work.
I deleted the grimore folder.
After that, I started the preview image and it crashed.

docker logs 04be30ff4850
chown: changing ownership of '/app/data': Operation not permitted
chmod: changing permissions of '/app/data': Operation not permitted
$ bun run ./migrate
1 | import { Database } from 'bun:sqlite';
2 | import { drizzle } from 'drizzle-orm/bun-sqlite';
3 | import { migrate } from 'drizzle-orm/bun-sqlite/migrator';
4 | 
5 | const sqlite = new Database('data/db.sqlite');
                   ^
SQLiteError: unable to open database file
 errno: 14
  code: "SQLITE_CANTOPEN"

      at new Database (bun:sqlite:195:14)
      at /app/migrate.js:5:16

Bun v1.1.34 (Linux x64 baseline)
error: script "run-migrations" exited with code 1

@JasonLandbridge
Copy link

@goniszewski I saw your call for help on Reddit and figured you were having the exact same problem as I had in PlexRipper namely PlexRipper/PlexRipper#208.

I had the same permission issues and resolved it by converting my dockerfile to use s6-overlay, which supports: PUID and PGID. This is exactly what LinuxServer.io uses for all their images and resolves permission issues.

Read a bit through the links and let me know if you have any questions!

@goniszewski
Copy link
Owner

Thanks, @JasonLandbridge, I will check it!

@Roalkege
Copy link

Any news?

@goniszewski
Copy link
Owner

After some time tinkering, I may have found a solution for this problem. Please check those changes: https://github.com/goniszewski/grimoire/pull/162/files

@cw-cdz
Copy link

cw-cdz commented Dec 19, 2024

same issue with unraid.

As with many containers on Unraid (and elsewhere/containers in general), using Docker’s --user flag often resolves file permission issues by mapping the container process to a specific user and group which has permissions to the storage location. On Unraid, set UID=99 (nobody) and GID=100 (users). Example:

# On Unraid set this using the post arguments field of template: `--user 99:100`
docker run --user 99:100 -v /path/to/data:/app/data goniszewski/grimoire:latest  # replace 99:100 with user/group that has permissions to path
# Or with docker compose
    ...
    image: goniszewski/grimoire:latest
    user: "99:100"
    volumes:
      - /path/to/data:/app/data
        ...

If the container supports PUID/PGID, you can also set these environment variables to achieve the same result. However, Grimoire doesn’t support PUID/PGID, so the --user method was the fix here.

The changes introduced by goniszewski in #162 should also help, but if issues persist, double-check your file permissions and try this approach.

@Roalkege
Copy link

As with many containers on Unraid (and elsewhere/containers in general), using Docker’s --user flag often resolves file permission issues by mapping the container process to a specific user and group which has permissions to the storage location. On Unraid, set UID=99 (nobody) and GID=100 (users). Example:

I added the --user 99:100 the the args...

docker run
  -d
  --name='grimoire'
  --net='bridge'
  --pids-limit 2048
  -e TZ="Europe/Berlin"
  -e HOST_OS="Unraid"
  -e HOST_HOSTNAME="unRaid"
  -e HOST_CONTAINERNAME="grimoire"
  -e 'PUBLIC_HTTPS_ONLY'='false'
  -e 'PUBLIC_SIGNUP_DISABLED'='false'
  -e 'PORT'='5173'
  -l net.unraid.docker.managed=dockerman
  -l net.unraid.docker.webui='http://[IP]:[PORT:5173]'
  -l net.unraid.docker.icon='https://raw.githubusercontent.com/A75G/docker-templates/master/templates/icons/grimoire.png'
  -p '5173:5173/tcp'
  -v '/mnt/user/appdata/grimoire/':'/app/data/':'rw'
  --user 99:100 'goniszewski/grimoire:latest'

But still the SQLiteError: unable to open database file error.

@cw-cdz
Copy link

cw-cdz commented Dec 20, 2024

As with many containers on Unraid (and elsewhere/containers in general), using Docker’s --user flag often resolves file permission issues by mapping the container process to a specific user and group which has permissions to the storage location. On Unraid, set UID=99 (nobody) and GID=100 (users). Example:

I added the --user 99:100 the the args...

docker run
  -d
  --name='grimoire'
  --net='bridge'
  --pids-limit 2048
  -e TZ="Europe/Berlin"
  -e HOST_OS="Unraid"
  -e HOST_HOSTNAME="unRaid"
  -e HOST_CONTAINERNAME="grimoire"
  -e 'PUBLIC_HTTPS_ONLY'='false'
  -e 'PUBLIC_SIGNUP_DISABLED'='false'
  -e 'PORT'='5173'
  -l net.unraid.docker.managed=dockerman
  -l net.unraid.docker.webui='http://[IP]:[PORT:5173]'
  -l net.unraid.docker.icon='https://raw.githubusercontent.com/A75G/docker-templates/master/templates/icons/grimoire.png'
  -p '5173:5173/tcp'
  -v '/mnt/user/appdata/grimoire/':'/app/data/':'rw'
  --user 99:100 'goniszewski/grimoire:latest'

But still the SQLiteError: unable to open database file error.

Did you verify the permissions and owner on the container folder in appdata location? They would have been created previously using incorrect. Either adjust them or delete everything and let them be recreated. I would still suggest checking them after they are recreated.

The key idea here is the storage location is not accessible or writable by the container process.

@Roalkege
Copy link

Did you verify the permissions and owner on the container folder in appdata location? They would have been created previously using incorrect. Either adjust them or delete everything and let them be recreated. I would still suggest checking them after they are recreated.

The key idea here is the storage location is not accessible or writable by the container process.

I had to set it manually to nobody.

@lukanvanderlinde
Copy link

lukanvanderlinde commented Jan 10, 2025

I'm having the same issue, I tried the latest preview, 0.4, 0.4.4 and all gave the same error.

Got it running on 0.3

@joshoram80
Copy link

What is the status with this? Using a bind mount is not working and I don't want to use volumes.

`Bun v1.1.34 (Linux x64 baseline)
error: script "run-migrations" exited with code 1
chown: changing ownership of '/app/data': Operation not permitted
chmod: changing permissions of '/app/data': Operation not permitted
$ bun run ./migrate
1 | import { Database } from 'bun:sqlite';
2 | import { drizzle } from 'drizzle-orm/bun-sqlite';
3 | import { migrate } from 'drizzle-orm/bun-sqlite/migrator';
4 |
5 | const sqlite = new Database('data/db.sqlite');
^
SQLiteError: unable to open database file
errno: 14
code: "SQLITE_CANTOPEN"

  at new Database (bun:sqlite:195:14)
  at /app/migrate.js:5:16

Bun v1.1.34 (Linux x64 baseline)
error: script "run-migrations" exited with code 1`

using both :latest and :preview

@AnonymousX86
Copy link

Adding:

user: root

Fixes the issue. This is weird, as according to docs the container should run as root by default.

volumes:
  - ./data:/app/data

New folder is owned by root:1001.

@BitBernd
Copy link

same issue with unraid.

As with many containers on Unraid (and elsewhere/containers in general), using Docker’s --user flag often resolves file permission issues by mapping the container process to a specific user and group which has permissions to the storage location. On Unraid, set UID=99 (nobody) and GID=100 (users). Example:

On Unraid set this using the post arguments field of template: --user 99:100

docker run --user 99:100 -v /path/to/data:/app/data goniszewski/grimoire:latest # replace 99:100 with user/group that has permissions to path

Or with docker compose

...
image: goniszewski/grimoire:latest
user: "99:100"
volumes:
  - /path/to/data:/app/data
    ...

If the container supports PUID/PGID, you can also set these environment variables to achieve the same result. However, Grimoire doesn’t support PUID/PGID, so the --user method was the fix here.

The changes introduced by goniszewski in #162 should also help, but if issues persist, double-check your file permissions and try this approach.

Just started a new server for the first time. Using the docker compose user mechanism solved it for me.

goniszewski added a commit that referenced this issue Jan 30, 2025
* chore: release v0.4.4 (#158)

* chore: release v0.4.1-hotfix.3

* fix(data-migration): early return if no categories have parents (#128)

Signed-off-by: Robert Goniszewski <[email protected]>

* Closes #130 (#131)

Signed-off-by: Robert Goniszewski <[email protected]>

* fix(database): use dynamic path for SQLite database file

Signed-off-by: Robert Goniszewski <[email protected]>

* docs(readme): use single README file for latest/preview version

Signed-off-by: Robert Goniszewski <[email protected]>

* feat(ci): add manual deployment workflow and adjust tag conditions

Signed-off-by: Robert Goniszewski <[email protected]>

* refactor(workflow): simplify manual-deploy GitHub Action

Signed-off-by: Robert Goniszewski <[email protected]>

* fix(metadata): handle multiple image URLs in mainImageUrl field

Signed-off-by: Robert Goniszewski <[email protected]>

* fix: auth error handling (#144)

* refactor(api): migrate Swagger UI to external documentation and enhance health endpoint

Signed-off-by: Robert Goniszewski <[email protected]>

* chore: release v0.4.3

* fix(docker): resolve issue with data directory permissions (#150)

* Fix issue #153: creation/update of root categories (#157)

* chore: release v0.4.3 (#149)

* chore: release v0.4.1-hotfix.3

* fix(data-migration): early return if no categories have parents (#128)

Signed-off-by: Robert Goniszewski <[email protected]>

* Closes #130 (#131)

Signed-off-by: Robert Goniszewski <[email protected]>

* fix(database): use dynamic path for SQLite database file

Signed-off-by: Robert Goniszewski <[email protected]>

* docs(readme): use single README file for latest/preview version

Signed-off-by: Robert Goniszewski <[email protected]>

* feat(ci): add manual deployment workflow and adjust tag conditions

Signed-off-by: Robert Goniszewski <[email protected]>

* refactor(workflow): simplify manual-deploy GitHub Action

Signed-off-by: Robert Goniszewski <[email protected]>

* fix(metadata): handle multiple image URLs in mainImageUrl field

Signed-off-by: Robert Goniszewski <[email protected]>

* fix: auth error handling (#144)

* refactor(api): migrate Swagger UI to external documentation and enhance health endpoint

Signed-off-by: Robert Goniszewski <[email protected]>

* chore: release v0.4.3

---------

Signed-off-by: Robert Goniszewski <[email protected]>
Co-authored-by: Prabhanjan <[email protected]>

* Fix creation/update of root categories

---------

Signed-off-by: Robert Goniszewski <[email protected]>
Co-authored-by: Robert Goniszewski <[email protected]>
Co-authored-by: Prabhanjan <[email protected]>

* Fix issue #145: bookmarks without images (#156)

* chore: release v0.4.3 (#149)

* chore: release v0.4.1-hotfix.3

* fix(data-migration): early return if no categories have parents (#128)

Signed-off-by: Robert Goniszewski <[email protected]>

* Closes #130 (#131)

Signed-off-by: Robert Goniszewski <[email protected]>

* fix(database): use dynamic path for SQLite database file

Signed-off-by: Robert Goniszewski <[email protected]>

* docs(readme): use single README file for latest/preview version

Signed-off-by: Robert Goniszewski <[email protected]>

* feat(ci): add manual deployment workflow and adjust tag conditions

Signed-off-by: Robert Goniszewski <[email protected]>

* refactor(workflow): simplify manual-deploy GitHub Action

Signed-off-by: Robert Goniszewski <[email protected]>

* fix(metadata): handle multiple image URLs in mainImageUrl field

Signed-off-by: Robert Goniszewski <[email protected]>

* fix: auth error handling (#144)

* refactor(api): migrate Swagger UI to external documentation and enhance health endpoint

Signed-off-by: Robert Goniszewski <[email protected]>

* chore: release v0.4.3

---------

Signed-off-by: Robert Goniszewski <[email protected]>
Co-authored-by: Prabhanjan <[email protected]>

* Fix bookmark creation/update without image

---------

Signed-off-by: Robert Goniszewski <[email protected]>
Co-authored-by: Robert Goniszewski <[email protected]>
Co-authored-by: Prabhanjan <[email protected]>

* chore: release v0.4.4

---------

Signed-off-by: Robert Goniszewski <[email protected]>
Co-authored-by: Prabhanjan <[email protected]>
Co-authored-by: Guillaume Poussel <[email protected]>

* feat(docker): implement s6-overlay

Signed-off-by: Robert Goniszewski <[email protected]>

* fix(docker): use proper tag in compose

Signed-off-by: Robert Goniszewski <[email protected]>

---------

Signed-off-by: Robert Goniszewski <[email protected]>
Co-authored-by: Prabhanjan <[email protected]>
Co-authored-by: Guillaume Poussel <[email protected]>
@goniszewski
Copy link
Owner

goniszewski commented Jan 30, 2025

Without any additional feedback, I have merged the above PR to the preview as https://github.com/goniszewski/grimoire/releases/tag/v0.5.0-pre.2. You can check the goniszewski/grimoire:preview Docker image with the original docker-compose.yml and let me know if this resolved the issue for you.

Sorry for being so sluggish with this one.

EDIT: There is some issue with building the image, and I'm working on a fix for this.

goniszewski added a commit that referenced this issue Jan 30, 2025
This reverts commit b5d338f.
goniszewski added a commit that referenced this issue Jan 30, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

Successfully merging a pull request may close this issue.