[BUG] Database migration error while installing Ghostfolio on Unraid

[brytorres]

Bug Description

While installing Ghostfolio on my Unraid server via docker, database migration errors are occurring.

To Reproduce

1. Once logged into Unraid, using the Apps page I install Ghostfolio providing all required values.
2. Viewing the logs as the app is setup, a database migration error occurs ERROR: ALTER TYPE ... ADD cannot run inside a transaction block
3. I've tried this with different Postgres users, all with the required privileges.

Expected behavior

The app shouldn't have issues during the DB migration.

Screenshots

Logs

yarn run v1.22.19
$ yarn database:migrate && yarn database:seed && node main
$ prisma migrate deploy
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "ghostfolio-db", schema "public" at "[HOST]:5432"

PostgreSQL database ghostfolio-db created at [HOST]:5432

52 migrations found in prisma/migrations

Applying migration `20210604190809_initial_migration`
Applying migration `20210605161257_added_symbol_profile`
Applying migration `20210612110542_added_auth_device`
Applying migration `20210616075245_added_sectors_to_symbol_profile`
Applying migration `20210703194509_added_balance_to_account`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20210703194509_added_balance_to_account

Database error code: 25001

Database error:
ERROR: ALTER TYPE ... ADD cannot run inside a transaction block

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E25001), message: "ALTER TYPE ... ADD cannot run inside a transaction block", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("xact.c"), line: Some(3218), routine: Some("PreventInTransactionBlock") }


error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

Environment

• Ghostfolio Version X.Y.Z
• Firefox Developer Edition
• Unraid Server

Additional context

Unraid uses the official Docker image.

[dtslvr]

Have you seen this error before @LeonStoldt?

[LeonStoldt]

No, i haven't - but I also did not execute a fresh installation for some time now (e.g. including the auto database migration in docker image).

@framewerktechnologies just to prevent misunderstanding:
You installed postgres, redis and ghostfolio from the Unraid app store, provided the connection details in ghostfolio (for connecting to postgres and redis) like stated in the guide (unraid forum) and started ghostfolio. Then you noticed the given logs without doing anything manually, while ghostfolio started the first time?

It's weird, because the problem not being able to edit an enum in the transaction block - most likely "caused" by this line in the mentioned migration script:

-- AlterEnum
ALTER TYPE "AccountType" ADD VALUE 'CASH';

cannot be a general problem - based on my understanding. If it would be, it would fail everytime you are executing a fresh migration, but this never happened to me. So i would exclude a general issue in the migration (script) structure.

If it's a transaction thing, maybe there's a way to tell prisma how to handle transaction while applying these migrations? I'm not familiar with prisma. @dtslvr is there something like "commit after every migration step"? - just looking for a quick fix here.

Maybe, I would try to apply the script manually one by one. This link should help with prisma commands.

A more "ugly" way would be to connect to your postgres (right click postgres, console and type something like: psql -U ghostfolio -d ghostfolio-db. U=user, d=database) and apply that step manually (including dropping enum and creating it again):

-- Type: AccountType
--DELETE
DROP TYPE public."AccountType";

CREATE TYPE public."AccountType" AS ENUM
    ('CASH', 'SECURITIES');
--optinal change owner to your ghostfolio owner
ALTER TYPE public."AccountType"
    OWNER TO ghostfolio;

--second step in migration script
ALTER TABLE "Account" ADD COLUMN "balance" DOUBLE PRECISION NOT NULL DEFAULT 0,
ADD COLUMN "currency" "Currency" NOT NULL DEFAULT E'USD';

and mark that migration step as already applied with something like this:

npm prisma migrate resolve --applied "20210703194509_added_balance_to_account"

However, it should work out of the box and it's not the best way to start with a crappy database. I will try installing it on a fresh system and try to reproduce the error.

[dtslvr]

Thank you very much for your detailed explanations @LeonStoldt.

If it would be, it would fail everytime you are executing a fresh migration, but this never happened to me. So i would exclude a general issue in the migration (script) structure.

I can confirm that nothing has changed in the migration concept recently.

[rswafford]

Good morning gents, I just wanted to add my report to this issue - I just tried installing a fresh Ghostfolio instance on Unraid as well, with Postgres and Redis from the Unraid app store. I hit the exact same issue as @framewerktechnologies did. Dropped the database and tried again, with the same result. Hoping there's a better fix than hacking the migration script... but if not I'll go ahead with that.

[faspina]

Any ideas on a work around. I am strugging to get this installed on unraid . Fresh install. I went back to 1.190.0 and a fresh install empty DB is is still broke. The docker container stops so I can get the npm command to execute

[faspina]

I figured this out for anyone that wants to know I did sql steps above manually while the docker was stopped by connecting to the postsql db with adminer. Then I went into the migration record that failed and marked it complete (added a completion date). Restarted docker and the DB build. I was able to log in and get started. Ran into another problem pulling prices and historical data, solved that by setting a TZ variable to null

[LeonStoldt]

Hi everyone,
I tried reproducing this behaviour on a fresh system. Installing fresh postgres, fresh redis, fresh ghostfolio - strictly following my step by step guide in the Community Forum. I am really sorry, but I can't reproduce it. My ghostfolio instance is running without any problems, postgres has been migrated and I can login, gather data etc.

I wrote down the docker commands Unraid executed when creating the container via template. Maybe this could be helpful to start it via console (needs some changes in the parameters to fit your setup of course):

Docker commands

Open your unraid terminal in the top-right-hand corner to execute the following commands:

1. Start postgres (Note: I used v15 and have a running system with Postgres 14 as well) - change TZ

docker run \
  -d \
  --name='postgresql15-ghostfolio' \
  --net='bridge' \
  -e TZ="Europe/Berlin" \
  -e HOST_OS="Unraid" \
  -e HOST_HOSTNAME="Server" \
  -e HOST_CONTAINERNAME="postgresql15-ghostfolio" \
  -e 'POSTGRES_PASSWORD'='password' \
  -e 'POSTGRES_USER'='ghostfolio' \
  -e 'POSTGRES_DB'='ghostfolio-db' \
  -l net.unraid.docker.managed=dockerman \
  -l net.unraid.docker.icon='https://github.com/juusujanar/unraid-templates/raw/master/img/PostgreSQL-logo.png' \
  -p '5432:5432/tcp' \
  -v '/mnt/cache/appdata/postgresql15':'/var/lib/postgresql/data':'rw' 'postgres:15'

2. Start redis (change TZ)

docker run \
  -d \
  --name='Redis-ghostfolio' \
  --net='bridge' \
  -e TZ="Europe/Berlin" \
  -e HOST_OS="Unraid" \
  -e HOST_HOSTNAME="Server" \
  -e HOST_CONTAINERNAME="Redis-ghostfolio" \
  -l net.unraid.docker.managed=dockerman \
  -l net.unraid.docker.icon='https://raw.githubusercontent.com/juusujanar/unraid-templates/master/img/Redis-logo.png' \
  -p '6379:6379/tcp' 'redis'

3. Start ghostfolio (change TZ, Redis IP, Postgres IP instead of postgres.local)

docker run \
  -d \
  --name='Ghostfolio' \
  --net='bridge' \
  -e TZ="Europe/Berlin" \
  -e HOST_OS="Unraid" \
  -e HOST_HOSTNAME="Server" \
  -e HOST_CONTAINERNAME="Ghostfolio" \
  -e 'REDIS_HOST'='redis.local' \
  -e 'REDIS_PORT'='6379' \
  -e 'DATABASE_URL'='postgresql://ghostfolio:password@postgres.local:5432/ghostfolio-db?sslmode=prefer' \
  -e 'BASE_CURRENCY'='USD' \
  -e 'REDIS_PASSWORD'='' \
  -e 'ALPHA_VANTAGE_API_KEY'='' \
  -e 'EOD_HISTORICAL_DATA_API_KEY'='' \
  -e 'JWT_SECRET_KEY'='123456' \
  -e 'ACCESS_TOKEN_SALT'='GHOSTFOLIO' \
  -e 'NODE_ENV'='production' \
  -l net.unraid.docker.managed=dockerman \
  -l net.unraid.docker.webui='http://[IP]:[PORT:3333]' \
  -l net.unraid.docker.icon='https://avatars.githubusercontent.com/u/82473144?s=200' \
  -p '3333:3333/tcp' 'ghostfolio/ghostfolio'

and my logs from initial start until gathering some example data looks like this:

Log Outputs

Logs of my containers:

Postgres logs:

Redis Logs:

Ghostfolio Logs:

[tvigers]

I know this is an old issue however I was running into the same error.

I was trying to run it on Postgres 11 (an old installation that I need to migrate off of eventually).

However after searching for the exact error I came across this on stack overflow "error: ALTER TYPE ... ADD cannot run inside a transaction block".

Upgrading to a newer version of Postgres fixed it.