Bug - Installation Failure - SQL Error

[sarshad01]

Description

pnpm run kyb-manual-review-example results in failure, more specifically database connection error when @ballerine/workflows-service@0.7.37 db:reset is invoked. Running npm db:reset executes successfully if ran manually within services/workflows-service directory. However, running npm run seed throws Foreign key constraint error (Alert_businessId_fkey (index))

Expected Behaviour

BackOffice, Collection and Workflows services should start listening

Actual Behaviour

Command Fails with Exit code 1

Environment

• Node version: v20.17.0
• Device, operating system and its version: Ubuntu 22.04.4 LTS
  *
• Browser and its version: Firefox 130.0
  *
• Version of the affected apps and packages: Dev Branch @ c39a8df
  *

Steps to Reproduce

1. Follow the steps here : https://docs.ballerine.com/en/getting_started/installation/
2. pnpm run kyb-manual-review-example would exit the process with the database connection error
3. Run npm run db:reset while the db container is running and the command will succeed
4. Run npm run seed this will fail showing foreign key error

Any possible solutions

1- My assumption was the database connection error occurs due to localhost being used, I switched it to 127.0.0.1 in the /services/workflows-service/.env however it did not solve the issue.

2- I then tried to run npm run db:reset manually and it worked

3- I then manually ran npm run seed which failed throwing foreign key constraint error

Examples and references

Output of pnpm run kyb-manual-review-example :

Output of npm run seed :

[WFA-juliver]

is this first run?

[sarshad01]

@WFA-juliver I have tried multiple times and unsuccessful before trying to install it via docker

[WFA-juliver]

@sarshad01 when running the example, did you verify if theres a db container with postgres running?

try

rm -rf node_modules \
&& pnpm install \
&& pnpm monorepo:init \
&& pnpm run kyb-manual-review-example

and check if theres database running docker ps -a

[sarshad01]

@WFA-juliver Yes I did and the db container is running. The workflows-service:setup basically starts the db container via docker:db command, so even if it is not running, it starts running when the workflows-service is setup. If it is already running, the command restarts it. My assumption is the 'wait' for whatever reason does not work and the script tries to run migrations before the container is ready for accepting connections?

[WFA-juliver]

@WFA-juliver Yes I did and the db container is running. The workflows-service:setup basically starts the db container via docker:db command, so even if it is not running, it starts running when the workflows-service is setup. If it is already running, the command restarts it. My assumption is the 'wait' for whatever reason does not work and the script tries to run migrations before the container is ready for accepting connections?

yeah I see that too, hope someone on the ballerine team can help us. I really wanted to make this software up and running on my instance.

[WFA-juliver]

@sarshad01 any luck on your side?

[sarshad01]

I was able to get it up and running by manually running the db commands, to fix the npm seed and SQL error I edited /services/workflows-service/scripts/seed.ts and commented the seedTransactionsAlerts block

[WFA-juliver]

@sarshad01 how about the docker deployment, you have luck?

[TestYouTestUntilTest]

Created a special sh script /home/XXXXXXXXX/ballerine/services/workflows-service/scripts/waitDb.sh

#!/bin/bash

set -e

# There are some times database is not ready yet!
# We'll check if database is ready and we can connect to it
# then the rest of the code run as well.

if [[ -z "${DB_HOST}" ]]; then
  DB_HOST="localhost"
  echo "set DB_HOST=localhost"
else
  DB_HOST="${DB_HOST}"
fi

if [[ -z "${DB_PORT}" ]]; then
  DB_PORT="5432"
  echo "set DB_PORT=5432"
else
  DB_PORT="${DB_PORT}"
fi

echo "Waiting for database..."
echo DB_NAME: ${DB_NAME}
echo DB_HOST: ${DB_HOST}
echo DB_PORT: ${DB_PORT}
while ! ncat -z ${DB_HOST} ${DB_PORT};
do
  sleep 1
  echo "."
done
echo "Connected to database."

echo "Extra wait for 5 second(s)"
sleep 5
echo "Go forward...."

# ... Run what you have to here

Then add a lauch of this script to /home/XXXXXXXXX/ballerine/services/workflows-service/scripts/db-reset.ts

  console.info('Before Running Prisma migrate reset check db server...');
  //https://nodejs.org/api/child_process.html#child_processexecsynccommand-options
  execSync('bash $HOME/ballerine/services/workflows-service/scripts/waitDb.sh', { stdio: 'inherit' });

Actually, 3-5 seconds delay is the solution

[WFA-juliver]

@TestYouTestUntilTest I'm stuck on this error

Migration `20230723212353_add_plv8` failed to apply cleanly to the shadow database.
Error:
db error: ERROR: permission denied to create extension "plv8"
HINT: Must be superuser to create this extension.
   0: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:266

any idea?

[TestYouTestUntilTest]

@TestYouTestUntilTest I'm stuck on this error

Migration `20230723212353_add_plv8` failed to apply cleanly to the shadow database.
Error:
db error: ERROR: permission denied to create extension "plv8"
HINT: Must be superuser to create this extension.
   0: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:266

any idea?

Add sudo to the shell script to run commands and enter your user pass during script running.
But actually you can reduce script just for waiting 5 seconds (and this will not require any sudo)

echo "Extra wait for 5 second(s)"
sleep 5
echo "Go forward...."