starred/koel-koel

Fork 0

mirror of https://github.com/koel/koel.git synced 2026-04-25 16:56:02 +03:00

[GH-ISSUE #637] Postgres error on first sync: Unique violation #456

New issue

Closed

opened 2026-02-26 02:33:15 +03:00 by kerem · 6 comments

kerem commented

2026-02-26 02:33:15 +03:00

Owner

Originally created by @svalo on GitHub (Aug 18, 2017).
Original GitHub issue: https://github.com/koel/koel/issues/637

Configuration:
OS: archlinux
Postgres version:9.6.3
MariaDB version:10.1.26-MariaDB
Php version: 7.1.8
Koel is configured using php-fpm + nginx

I'm encountering the following error on a clean install of koel using postgres. If I use mariadb issue doesn't appear.

On first sync i receive:

                                                                                                                                                                         
  [Illuminate\Database\QueryException]                                                                                                                                   
  SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "artists_pkey"                                                             
  DETAIL:  Key (id)=(1) already exists. (SQL: insert into "artists" ("name", "updated_at", "created_at") values (Le Luci Della Centrale Elettrica, 2017-08-18 08:55:36,  
   2017-08-18 08:55:36) returning "id")                                                                                                                                  
                                                                                                                                                                         

                                                                                                              
  [PDOException]                                                                                              
  SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "artists_pkey"  
  DETAIL:  Key (id)=(1) already exists.

As suggested by #138 I tried to execute commands but failed, has to raise from 2 to 3 to make koel start to sync the library.

After php artisan koel:init in the db I have:

select * from artists;
 id |      name       |     created_at      |     updated_at      | image 
----+-----------------+---------------------+---------------------+-------
  2 | Various Artists | 2017-08-18 09:01:36 | 2017-08-18 09:01:36 | 
  1 | Unknown Artist  | 2017-08-18 09:01:36 | 2017-08-18 09:01:36 | 
(2 rows)


koel=# select * from albums;
 id | artist_id |     name      |       cover       |     created_at      |     updated_at      | is_compilation 
----+-----------+---------------+-------------------+---------------------+---------------------+----------------
  1 |         1 | Unknown Album | unknown-album.png | 2017-08-18 09:01:36 | 2017-08-18 09:01:36 | 
(1 row)

koel-# select * from artists_id_seq ;
 sequence_name  | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 artists_id_seq |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
(1 row)

koel=# select * from albums_id_seq;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 albums_id_seq |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
(1 row)

Seems like when seeding the DB the last_value field in the sequences does not get incremented, are there any updates from the situation linked in #138 ?

kerem closed this issue

2026-02-26 02:33:15 +03:00

kerem commented

2026-02-26 02:33:16 +03:00

Author

Owner

@phanan commented on GitHub (Aug 19, 2017):

Is there a command in PostreSQL to set the auto increment seed value? In MySQL we have AUTO_INCREMENT, but I don't know much about Postres.

@phanan commented on GitHub (Aug 19, 2017): Is there a command in PostreSQL to set the auto increment seed value? In MySQL we have `AUTO_INCREMENT`, but I don't know much about Postres.

kerem commented

2026-02-26 02:33:16 +03:00

Author

Owner

@svalo commented on GitHub (Aug 26, 2017):

Sorry, I'm not an expert in postgres and this was the first time I met laravel so I don't really know :(

@svalo commented on GitHub (Aug 26, 2017): Sorry, I'm not an expert in postgres and this was the first time I met laravel so I don't really know :(

kerem commented

2026-02-26 02:33:16 +03:00

Author

Owner

@phanan commented on GitHub (Aug 26, 2017):

Then why don't you just use MySQL? :)

@phanan commented on GitHub (Aug 26, 2017): Then why don't you just use MySQL? :)

kerem commented

2026-02-26 02:33:16 +03:00

Author

Owner

@svalo commented on GitHub (Aug 26, 2017):

I was experimenting using koel on a Olinuxino with not much RAM and CPU and in my experience I found Postgres behaving better in such scenario so I tried :) Don't want to start a discussion Postgres vs MySQL :)

@svalo commented on GitHub (Aug 26, 2017): I was experimenting using koel on a Olinuxino with not much RAM and CPU and in my experience I found Postgres behaving better in such scenario so I tried :) Don't want to start a discussion Postgres vs MySQL :)

kerem commented

2026-02-26 02:33:16 +03:00

Author

Owner

@srsgores commented on GitHub (Aug 29, 2017):

Just got this too, on first scan.

Using postgres too. Has anyone found a way to circumvent this problem?

@srsgores commented on GitHub (Aug 29, 2017): Just got this too, on first scan. ![image](https://user-images.githubusercontent.com/1750837/29803357-59f8d70a-8c38-11e7-9f65-88df48831161.png) Using postgres too. Has anyone found a way to circumvent this problem?

kerem commented

2026-02-26 02:33:16 +03:00

Author

Owner

@svalo commented on GitHub (Aug 29, 2017):

@srsgores you can try what is suggested in #138, the ALTER SEQUENCE bit.
To be sure about which number is correct for you, you can execute the following 2 queries agains koel db:
select max(id) from albums;
select * from albums_id_seq;
The first query will output the IDs of the albums present in the DB, while the second will output the status of the sequence.
For koel to work I had to execute the query in #138 with the value of the first query+1
You'll probably step into the same problem with the artists table, just follow the same instructions changing the tables accordingly

@svalo commented on GitHub (Aug 29, 2017): @srsgores you can try what is suggested in #138, the ALTER SEQUENCE bit. To be sure about which number is correct for you, you can execute the following 2 queries agains koel db: `select max(id) from albums;` `select * from albums_id_seq;` The first query will output the IDs of the albums present in the DB, while the second will output the status of the sequence. For koel to work I had to execute the query in #138 with the value of the first query+1 You'll probably step into the same problem with the artists table, just follow the same instructions changing the tables accordingly

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#456

No description provided.

Rows
Columns