spl0k / supysonic

Supysonic is a Python implementation of the Subsonic server API.
https://supysonic.readthedocs.io
GNU Affero General Public License v3.0
259 stars 57 forks source link

Postgresql migration to 0.7.7. fails due to syntax (reserved word) #264

Closed trap000d closed 1 month ago

trap000d commented 1 month ago

After update to 0.7.7 Supysonic won't start due to syntax error in SQL.

May 20 10:47:53 trap000d.trap000d gunicorn[1097785]: peewee.ProgrammingError: syntax error at or near "user"
May 20 10:47:53 trap000d.trap000d gunicorn[1097785]: LINE 1: ALTER TABLE user ADD COLUMN listenbrainz_session CHAR(36) 
May 20 10:47:53 trap000d.trap000d gunicorn[1097785]:                     ^
May 20 10:47:53 trap000d.trap000d gunicorn[1097785]: [2024-05-20 10:47:53 +1200] [1097785] [INFO] Worker exiting (pid: 1097785)
May 20 10:47:54 trap000d.trap000d gunicorn[1097781]: [2024-05-20 10:47:54 +1200] [1097781] [ERROR] Worker (pid:1097784) exited with code 3
May 20 10:47:54 trap000d.trap000d gunicorn[1097781]: [2024-05-20 10:47:54 +1200] [1097781] [ERROR] Worker (pid:1097783) was sent SIGTERM!
May 20 10:47:54 trap000d.trap000d gunicorn[1097781]: [2024-05-20 10:47:54 +1200] [1097781] [ERROR] Worker (pid:1097786) was sent SIGTERM!
May 20 10:47:54 trap000d.trap000d gunicorn[1097781]: [2024-05-20 10:47:54 +1200] [1097781] [ERROR] Worker (pid:1097785) was sent SIGTERM!
May 20 10:47:54 trap000d.trap000d gunicorn[1097781]: [2024-05-20 10:47:54 +1200] [1097781] [ERROR] Shutting down: Master
May 20 10:47:54 trap000d.trap000d gunicorn[1097781]: [2024-05-20 10:47:54 +1200] [1097781] [ERROR] Reason: Worker failed to boot.

After some googling it appeared that user is reserved word in PostgreSQL. Effectively there are two ways to fix the issue: either enclose it in quotes or use 'public' prefix for table name

schema/migration/postgres/20240318.sql:

ALTER TABLE "user" ADD COLUMN listenbrainz_session CHAR(36);
ALTER TABLE "user" ADD COLUMN listenbrainz_status BOOLEAN NOT NULL DEFAULT TRUE;
ALTER TABLE public.user ADD COLUMN listenbrainz_session CHAR(36);
ALTER TABLE public.user ADD COLUMN listenbrainz_status BOOLEAN NOT NULL DEFAULT TRUE;