starred/koel-koel

Fork 0

mirror of https://github.com/koel/koel.git synced 2026-04-25 08:46:00 +03:00

[GH-ISSUE #1286] Postgres error while adding files on fresh install #736

New issue

Closed

opened 2026-02-26 02:34:08 +03:00 by kerem · 7 comments

kerem commented

2026-02-26 02:34:08 +03:00

Owner

Originally created by @vitoyucepi on GitHub (Mar 4, 2021).
Original GitHub issue: https://github.com/koel/koel/issues/1286

Describe the bug
I'm creating my own container image with koel and pgsql.
You can find my build script and images in this gitlab repo.
On a clean install of postgresql, I cannot add new files until I make three requests that fail.

Error log

db_1   | 2021-03-04 21:44:45.590 UTC [61] ERROR:  duplicate key value violates unique constraint "artists_pkey"
db_1   | 2021-03-04 21:44:45.590 UTC [61] DETAIL:  Key (id)=(1) already exists.
db_1   | 2021-03-04 21:44:45.590 UTC [61] STATEMENT:  insert into "artists" ("name", "updated_at", "created_at") values ($1, $2, $3) returning "id"
web_1  | 192.168.160.1 - - [04/Mar/2021:21:44:45 +0000] "POST /api/upload HTTP/1.1" 500 486 "http://localhost:8080/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.192 Safari/537.36"
db_1   | 2021-03-04 21:44:47.593 UTC [62] ERROR:  duplicate key value violates unique constraint "artists_pkey"
db_1   | 2021-03-04 21:44:47.593 UTC [62] DETAIL:  Key (id)=(2) already exists.
db_1   | 2021-03-04 21:44:47.593 UTC [62] STATEMENT:  insert into "artists" ("name", "updated_at", "created_at") values ($1, $2, $3) returning "id"
web_1  | 192.168.160.1 - - [04/Mar/2021:21:44:47 +0000] "POST /api/upload HTTP/1.1" 500 486 "http://localhost:8080/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.192 Safari/537.36"
db_1   | 2021-03-04 21:44:48.327 UTC [63] ERROR:  duplicate key value violates unique constraint "albums_pkey"
db_1   | 2021-03-04 21:44:48.327 UTC [63] DETAIL:  Key (id)=(1) already exists.
db_1   | 2021-03-04 21:44:48.327 UTC [63] STATEMENT:  insert into "albums" ("artist_id", "name", "updated_at", "created_at") values ($1, $2, $3, $4) returning "id"
web_1  | 192.168.160.1 - - [04/Mar/2021:21:44:48 +0000] "POST /api/upload HTTP/1.1" 500 486 "http://localhost:8080/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.192 Safari/537.36"
web_1  | 192.168.160.1 - - [04/Mar/2021:21:44:49 +0000] "POST /api/upload HTTP/1.1" 200 850 "http://localhost:8080/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.192 Safari/537.36"

As you can see in the log the first 3 queries were failed with database errors.
The fourth query is ok.

This bug is also reproducible with https://github.com/Hyzual/docker-koel but with pgsql instead of mysql.

To reproduce
Steps to reproduce the behavior:

Clone repo git clone https://gitlab.com/vito-containers/koel.git
Replace pdo_mysql with pdo_pgsql in file /containers/5.0/php-apache/Dockerfile
Build container by executing command ./build.sh 5.0 php-apache or docker build -t "registry.gitlab.com/vito-containers/koel/php-apache:5.0" ./containers/5.0/php-apache/

Create docker compose file

version: "2.4"
services:
  web:
    image: "registry.gitlab.com/vito-containers/koel/php-apache:5.0"
    restart: always
    ports:
      - "127.0.0.1:8080:80"
    volumes:
      - music:/music
      - covers:/var/www/html/public/img/covers
      - search-indexes:/var/www/html/storage/search-indexes
    logging: &default-logging
      driver: "json-file"
      options:
        max-size: "1m"
        max-file: "3"
    environment: &koel-environment
      TZ: "UTC"
      DB_CONNECTION: "pgsql"
      DB_HOST: "db"
      DB_DATABASE: "koel"
      DB_USERNAME: "koel"
      DB_PASSWORD: koel
      ADMIN_NAME: "admin"
      ADMIN_EMAIL: "admin@localhost"
      ADMIN_PASSWORD: "admin"

  db:
    image: "postgres:13.2-alpine"
    volumes:
      - db_data:/var/lib/postgresql/data
    logging: *default-logging
    environment:
      TZ: UTC
      POSTGRES_DB: koel
      POSTGRES_USER: koel
      POSTGRES_PASSWORD: koel

volumes:
  db_data:
  covers:
  search-indexes:
  music:

Start with docker-compose up or docker-compose up -d to start in background.
Go to http://localhost:8080 and login with login admin@localhost and password admin.
Try to upload new music.
See errors in database container log and 500 errors in koel container log.

Expected behavior
Clean installation with pgsql should work ok, but it seems the problem with scheme.

Environment

Koel version: 5.0.2
OS: archlinux
Browser: Chrome 88
PHP version: 7.4.16
Node version: N/A (because 5.0.2 is already compiled)

Additional context
I've tested this bug with postgres and mysql container images.
There is no error in mysql and mariadb.
It seems the problem only present for the first 3 queries to new instance.

I've started using postgres a while ago because of memory and storage usage

	postgres:13-alpine	mysql:8	mysql/mysql-server:8.0	mariadb:10	mariadb/server:10.5
Memory	30MiB	348MiB	345MiB	67MiB	68MiB
Disk	160MB	546MB	406MB	401MB	360MB

Also there's no need to create your own images to override default values to utf8mb4 here and there.

Originally created by @vitoyucepi on GitHub (Mar 4, 2021). Original GitHub issue: https://github.com/koel/koel/issues/1286 **Describe the bug** I'm creating my own container image with koel and pgsql. You can find my build script and images in [this gitlab repo](https://gitlab.com/vito-containers/koel). On a clean install of postgresql, I cannot add new files until I make three requests that fail. Error log ``` db_1 | 2021-03-04 21:44:45.590 UTC [61] ERROR: duplicate key value violates unique constraint "artists_pkey" db_1 | 2021-03-04 21:44:45.590 UTC [61] DETAIL: Key (id)=(1) already exists. db_1 | 2021-03-04 21:44:45.590 UTC [61] STATEMENT: insert into "artists" ("name", "updated_at", "created_at") values ($1, $2, $3) returning "id" web_1 | 192.168.160.1 - - [04/Mar/2021:21:44:45 +0000] "POST /api/upload HTTP/1.1" 500 486 "http://localhost:8080/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.192 Safari/537.36" db_1 | 2021-03-04 21:44:47.593 UTC [62] ERROR: duplicate key value violates unique constraint "artists_pkey" db_1 | 2021-03-04 21:44:47.593 UTC [62] DETAIL: Key (id)=(2) already exists. db_1 | 2021-03-04 21:44:47.593 UTC [62] STATEMENT: insert into "artists" ("name", "updated_at", "created_at") values ($1, $2, $3) returning "id" web_1 | 192.168.160.1 - - [04/Mar/2021:21:44:47 +0000] "POST /api/upload HTTP/1.1" 500 486 "http://localhost:8080/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.192 Safari/537.36" db_1 | 2021-03-04 21:44:48.327 UTC [63] ERROR: duplicate key value violates unique constraint "albums_pkey" db_1 | 2021-03-04 21:44:48.327 UTC [63] DETAIL: Key (id)=(1) already exists. db_1 | 2021-03-04 21:44:48.327 UTC [63] STATEMENT: insert into "albums" ("artist_id", "name", "updated_at", "created_at") values ($1, $2, $3, $4) returning "id" web_1 | 192.168.160.1 - - [04/Mar/2021:21:44:48 +0000] "POST /api/upload HTTP/1.1" 500 486 "http://localhost:8080/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.192 Safari/537.36" web_1 | 192.168.160.1 - - [04/Mar/2021:21:44:49 +0000] "POST /api/upload HTTP/1.1" 200 850 "http://localhost:8080/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.192 Safari/537.36" ``` As you can see in the log the first 3 queries were failed with database errors. The fourth query is ok. This bug is also reproducible with https://github.com/Hyzual/docker-koel but with pgsql instead of mysql. **To reproduce** Steps to reproduce the behavior: 1. Clone repo `git clone https://gitlab.com/vito-containers/koel.git` 1. Replace `pdo_mysql` with `pdo_pgsql` in file `/containers/5.0/php-apache/Dockerfile` 1. Build container by executing command `./build.sh 5.0 php-apache` or `docker build -t "registry.gitlab.com/vito-containers/koel/php-apache:5.0" ./containers/5.0/php-apache/` 1. Create docker compose file ```docker-compose.yml version: "2.4" services: web: image: "registry.gitlab.com/vito-containers/koel/php-apache:5.0" restart: always ports: - "127.0.0.1:8080:80" volumes: - music:/music - covers:/var/www/html/public/img/covers - search-indexes:/var/www/html/storage/search-indexes logging: &default-logging driver: "json-file" options: max-size: "1m" max-file: "3" environment: &koel-environment TZ: "UTC" DB_CONNECTION: "pgsql" DB_HOST: "db" DB_DATABASE: "koel" DB_USERNAME: "koel" DB_PASSWORD: koel ADMIN_NAME: "admin" ADMIN_EMAIL: "admin@localhost" ADMIN_PASSWORD: "admin" db: image: "postgres:13.2-alpine" volumes: - db_data:/var/lib/postgresql/data logging: *default-logging environment: TZ: UTC POSTGRES_DB: koel POSTGRES_USER: koel POSTGRES_PASSWORD: koel volumes: db_data: covers: search-indexes: music: ``` 1. Start with `docker-compose up` or `docker-compose up -d` to start in background. 1. Go to `http://localhost:8080` and login with login `admin@localhost` and password `admin`. 1. Try to upload new music. 1. See errors in `database` container log and `500` errors in `koel` container log. **Expected behavior** Clean installation with pgsql should work ok, but it seems the problem with scheme. **Environment** - Koel version: 5.0.2 - OS: archlinux - Browser: Chrome 88 - PHP version: 7.4.16 - Node version: N/A (because 5.0.2 is already compiled) **Additional context** I've tested this bug with `postgres` and `mysql` container images. There is no error in mysql and mariadb. It seems the problem only present for the first 3 queries to new instance. I've started using postgres a while ago because of memory and storage usage | | postgres:13-alpine | mysql:8 | mysql/mysql-server:8.0 | mariadb:10 | mariadb/server:10.5 | | --- | --- | --- | --- | --- | --- | | Memory | 30MiB | 348MiB | 345MiB | 67MiB | 68MiB | | Disk | 160MB | 546MB | 406MB | 401MB | 360MB | Also there's no need to create your own images to override default values to `utf8mb4` here and there.

kerem closed this issue

2026-02-26 02:34:08 +03:00

kerem commented

2026-02-26 02:34:08 +03:00

Author

Owner

@vitoyucepi commented on GitHub (Mar 5, 2021):

Maybe related to laravel/framework#4897

@vitoyucepi commented on GitHub (Mar 5, 2021): Maybe related to laravel/framework#4897

kerem commented

2026-02-26 02:34:08 +03:00

Author

Owner

@phanan commented on GitHub (Mar 25, 2021):

This might not be the answer you're looking for but if MySQL works, can you use it? I don't use Postgres myself and the whole DB layer is done by Laravel's Eloquent, so there's very little I can help here.

@phanan commented on GitHub (Mar 25, 2021): This might not be the answer you're looking for but if MySQL works, can you use it? I don't use Postgres myself and the whole DB layer is done by Laravel's Eloquent, so there's very little I can help here.

kerem commented

2026-02-26 02:34:08 +03:00

Author

Owner

@vitoyucepi commented on GitHub (Mar 25, 2021):

Now I'm using koel with mariadb. It seems to work fine.
But I've found many other issues, like infinite loop for console commands and race condition for library scanning.
Didn't create issues about them.

As I already said but some time ago I think that postgresql was too complicated for me.
Now I'm thinking that postgresql is far more user-friendly technology because of docker image.
There's no need to configure anything. There's no need to create a custom container image because of utf8 by default.

Also there should be a warning in the docs, that postgresql is not officially supported by koel because of this bug.

@vitoyucepi commented on GitHub (Mar 25, 2021): Now I'm using koel with mariadb. It seems to work fine. But I've found many other issues, like infinite loop for console commands and race condition for library scanning. Didn't create issues about them. As I already said but some time ago I think that postgresql was too complicated for me. Now I'm thinking that postgresql is far more user-friendly technology because of docker image. There's no need to configure anything. There's no need to create a custom container image because of utf8 by default. Also there should be a warning in the [docs](https://docs.koel.dev/#server), that postgresql is not officially supported by koel because of this bug.

kerem commented

2026-02-26 02:34:08 +03:00

Author

Owner

@vitoyucepi commented on GitHub (Mar 26, 2021):

@phanan so what is the status of this issue.
Is it closed because won't fix?
Or maybe it should be opened with help wanted tag?

@vitoyucepi commented on GitHub (Mar 26, 2021): @phanan so what is the status of this issue. Is it closed because won't fix? Or maybe it should be opened with help wanted tag?

kerem commented

2026-02-26 02:34:08 +03:00

Author

Owner

@Hyzual commented on GitHub (May 20, 2021):

I reproduced this exact issue while testing https://github.com/koel/docker/pull/78

@Hyzual commented on GitHub (May 20, 2021): I reproduced this exact issue while testing https://github.com/koel/docker/pull/78

kerem commented

2026-02-26 02:34:08 +03:00

Author

Owner

@phanan commented on GitHub (May 21, 2021):

Hmm, looks like I'll have to install Postres and give it a try.

@phanan commented on GitHub (May 21, 2021): Hmm, looks like I'll have to install Postres and give it a try.

kerem commented

2026-02-26 02:34:08 +03:00

Author

Owner

@phanan commented on GitHub (May 21, 2021):

@vitoyucepi Sorry I just realized I haven't answered your question. Yeah I was inclined to mark this as won't fix, as I thought it was an edge-cased issue. But now that it doesn't look like one, I've reopened the issue and put a "Help Wanted" label.

@phanan commented on GitHub (May 21, 2021): @vitoyucepi Sorry I just realized I haven't answered your question. Yeah I was inclined to mark this as won't fix, as I thought it was an edge-cased issue. But now that it doesn't look like one, I've reopened the issue and put a "Help Wanted" label.

kerem referenced this issue

2026-02-26 03:30:48 +03:00

[PR #839] [MERGED] Add "Recently Played" playlist #1383

No milestone

No project

No assignees

1 participant

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

starred/koel-koel#736

No description provided.

Rows
Columns