twentyhq
Error while upgrating 0.31.0 to 0.32.0
Bug Description
I'm trying to upgrade twenty from 0.31.0 to 0.32.0, but I got errors while running yarn command:prod upgrade-0.32 (after yarn database:migrate:prod)
I did update REDIS_URL and APP_SECRET env and read the upgrading guide here: https://twenty.com/developers/section/self-hosting/upgrade-guide#v0.31.0-to-v0.32.0
Output:
[Nest] 34 - 11/05/2024, 11:27:22 AM WARN [SyncWorkspaceMetadataCommand] Workspace health check failed with error, but sync has been forced.
[Nest] 34 - 11/05/2024, 11:27:22 AM WARN [SyncWorkspaceMetadataCommand] NotFoundException: Table viewGroup not found in schema workspace_5hev5eyy45o1a6w9oxucev3en
...
[Nest] 34 - 11/05/2024, 11:27:23 AM ERROR [WorkspaceSyncMetadataService] Sync of standard objects failed with:
[Nest] 34 - 11/05/2024, 11:27:23 AM ERROR [WorkspaceSyncMetadataService] QueryFailedError: invalid input syntax for type json
[Nest] 34 - 11/05/2024, 11:27:23 AM ERROR [WorkspaceSyncMetadataService] Token "The" is invalid.
...
[Nest] 34 - 11/05/2024, 11:27:27 AM LOG [SimplifySearchVectorExpressionCommand] Running command on workspace 5ca04b79-5f88-4d77-a36c-7d4e8da2c0ff failed with error: QueryFailedError: invalid input syntax for type json
...
query failed: SELECT "webhook"."targetUrl" AS "webhook_targetUrl", "webhook"."description" AS "webhook_description", "webhook"."id" AS "webhook_id", "webhook"."createdAt" AS "webhook_createdAt", "webhook"."updatedAt" AS "webhook_updatedAt", "webhook"."deletedAt" AS "webhook_deletedAt", "webhook"."operation" AS "webhook_operation", "webhook"."operations" AS "webhook_operations" FROM "workspace_5hev5eyy45o1a6w9oxucev3en"."webhook" "webhook" WHERE "webhook"."deletedAt" IS NULL
error: error: column webhook.operations does not exist
[Nest] 34 - 11/05/2024, 11:27:28 AM ERROR [UpgradeTo0_32Command] Command failed
QueryFailedError: column webhook.operations does not exist
at PostgresQueryRunner.query (/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async SelectQueryBuilder.loadRawResults (/app/node_modules/typeorm/query-builder/SelectQueryBuilder.js:2192:25)
at async SelectQueryBuilder.executeEntitiesAndRawResults (/app/node_modules/typeorm/query-builder/SelectQueryBuilder.js:2040:26)
at async SelectQueryBuilder.getRawAndEntities (/app/node_modules/typeorm/query-builder/SelectQueryBuilder.js:684:29)
at async SelectQueryBuilder.getMany (/app/node_modules/typeorm/query-builder/SelectQueryBuilder.js:750:25)
at async WorkspaceRepository.find (/app/packages/twenty-server/dist/src/engine/twenty-orm/repository/workspace.repository.js:21:24)
at async CopyWebhookOperationIntoOperationsCommand.executeActiveWorkspacesCommand (/app/packages/twenty-server/dist/src/database/commands/upgrade-version/0-32/0-32-copy-webhook-operation-into-operations-command.js:43:30)
at async UpgradeTo0_32Command.executeActiveWorkspacesCommand (/app/packages/twenty-server/dist/src/database/commands/upgrade-version/0-32/0-32-upgrade-version.command.js:43:9)
at async UpgradeTo0_32Command.executeBaseCommand (/app/packages/twenty-server/dist/src/database/commands/active-workspaces.command.js:59:9) {
query: 'SELECT "webhook"."targetUrl" AS "webhook_targetUrl", "webhook"."description" AS "webhook_description", "webhook"."id" AS "webhook_id", "webhook"."createdAt" AS "webhook_createdAt", "webhook"."updatedAt" AS "webhook_updatedAt", "webhook"."deletedAt" AS "webhook_deletedAt", "webhook"."operation" AS "webhook_operation", "webhook"."operations" AS "webhook_operations" FROM "workspace_5hev5eyy45o1a6w9oxucev3en"."webhook" "webhook" WHERE "webhook"."deletedAt" IS NULL',
parameters: [],
driverError: error: column webhook.operations does not exist
When I start the app I have the following error on the client:
I also try to run yarn command:prod fix-0.32:simplify-search-vector-expression but did not helped
we had a similar / same issue with a hosted instance at server.camp.
Our solution was to fix the database manually. The problem in our case was in the note and task table of the corresponding workspace, where some rows had an empty body column. We fixed it by changing the empty fields to null:
UPDATE workspace_XXXXX.note SET body = NULL WHERE body = '';
UPDATE workspace_XXXXX.task SET body = NULL WHERE body = '';
(Instead of XXXXX you have to set your workspace id, which you can find in the migration log right at the error:
query: `ALTER TABLE "workspace_XXXXX"."task" ADD "searchVector" tsvector GENERATED ALWAYS AS (to_tsvector('simple', COALESCE("title", '') || ' ' || COALESCE(jsonb_path_query_array("body"::jsonb, '$[*].content[*]."text"'::jsonpath)::text, ''))) STORED`,
parameters: undefined,
driverError: error: invalid input syntax for type json
at /app/node_modules/pg/lib/client.js:526:17
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async PostgresQueryRunner.query (/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)
at async PostgresQueryRunner.executeQueries (/app/node_modules/typeorm/query-runner/BaseQueryRunner.js:425:13)
at async PostgresQueryRunner.addColumn (/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:694:9)
at async WorkspaceMigrationRunnerService.createColumn (/app/packages/twenty-server/dist/src/engine/workspace-manager/workspace-migration-runner/workspace-migration-runner.service.js:227:9)
at async WorkspaceMigrationRunnerService.handleColumnChanges (/app/packages/twenty-server/dist/src/engine/workspace-manager/workspace-migration-runner/workspace-migration-runner.service.js:192:21)
at async WorkspaceMigrationRunnerService.handleTableChanges (/app/packages/twenty-server/dist/src/engine/workspace-manager/workspace-migration-runner/workspace-migration-runner.service.js:94:25)
at async WorkspaceMigrationRunnerService.executeMigrationFromPendingMigrations (/app/packages/twenty-server/dist/src/engine/workspace-manager/workspace-migration-runner/workspace-migration-runner.service.js:60:17)
at async WorkspaceSyncMetadataService.synchronize (/app/packages/twenty-server/dist/src/engine/workspace-manager/workspace-sync-metadata/workspace-sync-metadata.service.js:92:13) {
length: 159,
severity: 'ERROR',
code: '22P02',
detail: 'The input string ended unexpectedly.',
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: 'JSON data, line 1: ',
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'jsonfuncs.c',
line: '627',
routine: 'json_ereport_error'
},
length: 159,
severity: 'ERROR',
code: '22P02',
detail: 'The input string ended unexpectedly.',
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: 'JSON data, line 1: ',
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'jsonfuncs.c',
line: '627',
routine: 'json_ereport_error'
}
After this changes, the migration was successfully completed :) Hope this helps!
I found that what's failing on my side is this: jsonb_path_query_array("body"::jsonb, '$[*].content[*]."text"'::jsonpath)::text
SELECT title, jsonb_path_query_array("body"::jsonb, '$[*].content[*]."text"'::jsonpath)::text FROM "workspace_xxxxx"."note";
ERROR: invalid input syntax for type json
DETAIL: Token "The" is invalid.
CONTEXT: JSON data, line 1: The...
So I run select id, title, body from "workspace_xxxxx"."note" where body::text like '%The%'; and found 2 rows from test workspace that was failing due to bad json field value:
a741e4d6-31fd-40ad-9285-9695ac562936 | The Test title | The Test body
4558d8db-810d-4355-9491-9333505c9cac | The Test title | The Test body
I deleted them, rerun the upgrade script, now it seem to work
I'm getting a different error here on loggin:
I've migrated from v0.24.0 to v.0.32.0
Also I had to disable the ENABLE_DB_MIGRATIONS cause get stucked on loop trying to migrate something
Hey everyone, sorry for the painful upgrade process. We really need to work on an admin panel to simplify upgrades next year.
@JuanxCursed we deprecated the PHONE field type to introduce PHONES instead. One of the migration fields should have converted your PHONE to text field, maybe it wasn't ran?
I'll close the issue since there is no concrete action item but feel free to reply or open a new one
