Migration 20220811234822_add_after_meeting_ends_trigger fails

[AlessandroLorenzi]

Issue Summary

The migration 20220811234822_add_after_meeting_ends_trigger fails

Technical details

Postgres version: 11.13

This is the Log:

@calcom/prisma:db-deploy: cache bypass, force executing a5f69b44b49a295c
@calcom/prisma:db-deploy: $ yarn prisma migrate deploy
@calcom/prisma:db-deploy: $ /calcom/node_modules/.bin/prisma migrate deploy
@calcom/prisma:db-deploy: Prisma schema loaded from schema.prisma
@calcom/prisma:db-deploy: Datasource "db": PostgreSQL database "calcom", schema "public" at "database:5432"
@calcom/prisma:db-deploy: 
@calcom/prisma:db-deploy: 118 migrations found in prisma/migrations
@calcom/prisma:db-deploy: 
@calcom/prisma:db-deploy: Error: P3009
@calcom/prisma:db-deploy: 
@calcom/prisma:db-deploy: migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
@calcom/prisma:db-deploy: The `20220811234822_add_after_meeting_ends_trigger` migration started at 2022-08-16 04:25:01.737537 UTC failed
@calcom/prisma:db-deploy: 
@calcom/prisma:db-deploy: 
@calcom/prisma:db-deploy: error Command failed with exit code 1.
@calcom/prisma:db-deploy: info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
@calcom/prisma:db-deploy: error Command failed with exit code 1.
@calcom/prisma:db-deploy: info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
@calcom/prisma:db-deploy: ERROR: command finished with error: command (packages/prisma) yarn run db-deploy exited (1)
command (packages/prisma) yarn run db-deploy exited (1)

 Tasks:    0 successful, 1 total
Cached:    0 cached, 1 total
  Time:    2.369s 

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

[PeerRich]

have you tried this? https://developer.cal.com/self-hosting/upgrading

[thedevale]

Having the same issue, did everything, also upgraded to postgres 14

[PeerRich]

@CarinaWolli this looks like your migration afaik. not sure if its related to 20220811234822_add_after_meeting_ends_trigger

[PeerRich]

@AlessandroLorenzi @thedevale

what version are you on?

[AlessandroLorenzi]

Hi, we have discovered that in postgres 11 does not allows ALTER TYPE ... ADD inside a transaction block.

ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block.
https://www.postgresql.org/docs/11/sql-altertype.html

In psql 12 it turns into:

If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) is executed inside a transaction block, the new value cannot be used until after the transaction has been committed.

We have runned manually the migration outside the transacion block as suggested here: prisma/prisma#9154

We suggest you to update the migration to support also psql 10 and 11 and test the migration in CI using a matrix.

COMMIT; 

-- AlterEnum
ALTER TYPE "WebhookTriggerEvents" ADD VALUE 'MEETING_ENDED';

BEGIN;
-- AlterTable
ALTER TABLE "Booking" ADD COLUMN     "scheduledJobs" TEXT[];

cc @simobasso

[CarinaWolli]

This PR #3955 should fix the issue with the migration in Postgres 11