[BUG] Error when using Sqlite - db.go:434 near "(": syntax error

[samcro1967]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

sqlite db init error in container logs on first start. hishtory.db is created and does exist in the config folder.

Expected Behavior

Container starts and DB is initialized without any errors.

Steps To Reproduce

Spin up the container and check the logs.

Environment

- OS:  Ubuntu 22.04
- How docker service was installed:

Setup Docker GPG key and repository, apt update, and then apt install:
docker-ce
docker-ce-cli
containerd.io
docker-compose-plugin

CPU architecture

x86-64

Docker creation

hishtory-server:
    hostname: hishtory-server
    image: ghcr.io/linuxserver/hishtory-server:latest
    container_name: hishtory-server
    restart: always
    ports:
      - 8104:8080
    environment:
      - TZ=${TZ}
      - PUID=${PUID}
      - PGID=${PGID}
      - HISHTORY_SQLITE_DB=/config/hishtory.db
    volumes:
      - ${docker}/hishtory-server:/config

Container logs

[migrations] started
[migrations] no migrations found
───────────────────────────────────────

      ██╗     ███████╗██╗ ██████╗
      ██║     ██╔════╝██║██╔═══██╗
      ██║     ███████╗██║██║   ██║
      ██║     ╚════██║██║██║   ██║
      ███████╗███████║██║╚██████╔╝
      ╚══════╝╚══════╝╚═╝ ╚═════╝

   Brought to you by linuxserver.io
───────────────────────────────────────

To support LSIO projects visit:
https://www.linuxserver.io/donate/

───────────────────────────────────────
GID/UID
───────────────────────────────────────

User UID:    1000
User GID:    1000
───────────────────────────────────────
Linuxserver.io version: v0.326-ls99
Build-date: 2025-01-18T23:01:19+00:00
───────────────────────────────────────

Using SQLite database, ensure you have mounted /config to your host
[custom-init] No custom files found, skipping...
Opening DB
Connecting to DB
AutoMigrating DB tables

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[0.030ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="enc_history_entries"

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[4.505ms] [rows:0] CREATE TABLE `enc_history_entries` (`encrypted_data` blob,`nonce` blob,`device_id` text,`user_id` text,`date` datetime,`encrypted_id` text,`read_count` integer,`is_from_same_device` numeric)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[0.048ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="devices"

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[3.862ms] [rows:0] CREATE TABLE `devices` (`user_id` text,`device_id` text,`registration_ip` text,`registration_date` datetime,`is_integration_test_device` numeric,`uninstall_date` datetime)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[0.043ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="usage_data"

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[3.615ms] [rows:0] CREATE TABLE `usage_data` (`user_id` text NOT NULL,`device_id` text NOT NULL,`last_used` datetime,`last_ip` text,`num_entries_handled` integer,`last_queried` datetime,`num_queries` integer,`version` text)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[3.996ms] [rows:0] CREATE UNIQUE INDEX `usageDataUniqueIndex` ON `usage_data`(`user_id`,`device_id`)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[0.056ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="dump_requests"

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[5.479ms] [rows:0] CREATE TABLE `dump_requests` (`user_id` text,`requesting_device_id` text,`request_time` datetime)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[0.059ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="deletion_requests"

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[3.731ms] [rows:0] CREATE TABLE `deletion_requests` (`user_id` text,`destination_device_id` text,`send_time` datetime,`messages` blob,`read_count` integer)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[0.041ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="feedbacks"

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[3.420ms] [rows:0] CREATE TABLE `feedbacks` (`user_id` text NOT NULL,`date` datetime NOT NULL,`feedback` text)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[0.041ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="active_user_stats"

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:62
[3.349ms] [rows:0] CREATE TABLE `active_user_stats` (`date` datetime,`total_num_devices` integer,`total_num_users` integer,`daily_active_submit_users` integer,`daily_active_query_users` integer,`weekly_active_submit_users` integer,`weekly_active_query_users` integer,`daily_installs` integer,`daily_uninstalls` integer)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:94
[3.139ms] [rows:0] CREATE INDEX IF NOT EXISTS entry_id_idx ON enc_history_entries (encrypted_id)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:94
[3.109ms] [rows:0] CREATE INDEX IF NOT EXISTS device_id_idx ON enc_history_entries (device_id)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:94
[3.320ms] [rows:0] CREATE INDEX IF NOT EXISTS read_count_idx ON enc_history_entries (read_count)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:94
[3.547ms] [rows:0] CREATE INDEX IF NOT EXISTS redact_idx ON enc_history_entries (user_id,device_id,date)

2025/01/20 09:26:14 /build/hishtory/backend/server/internal/database/db.go:94
[3.781ms] [rows:0] CREATE INDEX IF NOT EXISTS del_user_idx ON deletion_requests (user_id)
Pinging DB to confirm liveness
Done initializing DB
Listening on :8080
127.0.0.1:42042 GET "/healthcheck"  34.101µs 2 B
[ls.io-init] done.
Found a valid version: v0.326

2025/01/20 09:26:22 /build/hishtory/backend/server/internal/database/db.go:346
[0.117ms] [rows:0] DELETE FROM enc_history_entries WHERE read_count > 10

2025/01/20 09:26:22 /build/hishtory/backend/server/internal/database/db.go:350
[0.038ms] [rows:0] DELETE FROM deletion_requests WHERE read_count > 100

2025/01/20 09:26:22 /build/hishtory/backend/server/internal/database/db.go:434 near "(": syntax error
[0.055ms] [rows:0]
                CREATE TEMP TABLE temp_inactive_devices AS (
                        SELECT device_id
                        FROM usage_data
                        WHERE last_used <= (now() - INTERVAL '90 days')
                )

[github-actions]

Thanks for opening your first issue here! Be sure to follow the relevant issue templates, or risk having this issue marked as invalid.

[thespad]

I think this is an upstream bug, I can replicate it with an sqlite backend but not postgres. We don't do anything unusual with the database side of things, we just pass the envs straight through to the server.

[samcro1967]

Fixed it upstream by just disabling the offending SQL commands for sqlite.