parcelvoy
[BUG] `DELETE /admin/projects/{id}/users?user_id={id}` fails with ClickHouse projection error
When attempting to use the DELETE /admin/projects/{id}/users?user_id={id} endpoint, the request fails with the following error in the logs:
worker-1 {
"level": 50,
"time": 1759497301812,
"pid": 9,
"hostname": "00ad03aa7c90",
"error": {
"code": "344",
"type": "SUPPORT_IS_DISABLED"
},
"stacktrace": "Error: DELETE query is not allowed for table parcelvoy.user_events because it has projections and setting lightweight_mutation_projection_mode is set to THROW.
User should change lightweight_mutation_projection_mode OR drop all the projections manually before running the query.
at parseError (/usr/src/app/node_modules/.pnpm/@[email protected]/node_modules/@clickhouse/client-common/dist/error/error.js:38:16)
at ClientRequest.onResponse (/usr/src/app/node_modules/.pnpm/@[email protected]/node_modules/@clickhouse/client/dist/connection/node_base_connection.js:415:107)
at process.processTicksAndRejections (node:internal/process/task_queues:105:5)",
"job": {
"name": "user_delete_bulk",
"data": {}
},
"options": {
"delay": 0,
"attempts": 3
},
"msg": "queue:job:errored"
}
I'm using v3.0.0-beta.9
@jeroenrinzema Users are definitely deleted. At least from UI. However, logs are full of warnings, which makes me think something isn't working as expected.
Do you see anything similar when you visit https://your-parcelvoy-instance.com/organization/performance?
And here is what I see when I click on any warning:
{
"name": "user_delete_bulk",
"data": {
"name": "user_delete_bulk",
"data": {},
"options": {
"delay": 0,
"attempts": 3
}
},
"opts": {
"attempts": 3,
"delay": 0,
"removeOnFail": {
"count": 50,
"age": 86400
},
"removeOnComplete": true,
"backoff": {
"delay": 1000,
"type": "exponential"
}
},
"id": "392324",
"progress": 0,
"returnvalue": null,
"stacktrace": [
"Error: DELETE query is not allowed for table parcelvoy.user_events because as it has projections and setting lightweight_mutation_projection_mode is set to THROW. User should change lightweight_mutation_projection_mode OR drop all the projections manually before running the query. \n at parseError (/usr/src/app/node_modules/.pnpm/@[email protected]/node_modules/@clickhouse/client-common/dist/error/error.js:38:16)\n at ClientRequest.onResponse (/usr/src/app/node_modules/.pnpm/@[email protected]/node_modules/@clickhouse/client/dist/connection/node_base_connection.js:415:107)\n at process.processTicksAndRejections (node:internal/process/task_queues:105:5)",
"Error: DELETE query is not allowed for table parcelvoy.user_events because as it has projections and setting lightweight_mutation_projection_mode is set to THROW. User should change lightweight_mutation_projection_mode OR drop all the projections manually before running the query. \n at parseError (/usr/src/app/node_modules/.pnpm/@[email protected]/node_modules/@clickhouse/client-common/dist/error/error.js:38:16)\n at ClientRequest.onResponse (/usr/src/app/node_modules/.pnpm/@[email protected]/node_modules/@clickhouse/client/dist/connection/node_base_connection.js:415:107)\n at process.processTicksAndRejections (node:internal/process/task_queues:105:5)",
"Error: DELETE query is not allowed for table parcelvoy.user_events because as it has projections and setting lightweight_mutation_projection_mode is set to THROW. User should change lightweight_mutation_projection_mode OR drop all the projections manually before running the query. \n at parseError (/usr/src/app/node_modules/.pnpm/@[email protected]/node_modules/@clickhouse/client-common/dist/error/error.js:38:16)\n at ClientRequest.onResponse (/usr/src/app/node_modules/.pnpm/@[email protected]/node_modules/@clickhouse/client/dist/connection/node_base_connection.js:415:107)\n at process.processTicksAndRejections (node:internal/process/task_queues:105:5)"
],
"delay": 0,
"priority": 0,
"attemptsStarted": 3,
"attemptsMade": 3,
"stalledCounter": 0,
"timestamp": 1759996800024,
"queueQualifiedName": "bull:parcelvoy",
"finishedOn": 1759996804309,
"processedOn": 1759996804265,
"failedReason": "DELETE query is not allowed for table parcelvoy.user_events because as it has projections and setting lightweight_mutation_projection_mode is set to THROW. User should change lightweight_mutation_projection_mode OR drop all the projections manually before running the query. "
}
So I dived deeper and it seems to be a bug in Parcelvoy v3.
The UserDeleteJob attempts to delete user events from ClickHouse using UserEvent.clickhouse().delete(), but ClickHouse throws DELETE query is not allowed for table parcelvoy.user_events because as it has projections and setting lightweight_mutation_projection_mode is set to THROW error. While users appear deleted in the UI (MySQL deletion succeeds), the ClickHouse deletion fails, causing the job to retry and eventually fail after 3 attempts, leaving orphaned user event data in ClickHouse.
⚠️ The Impact: Orphaned user events can cause campaigns to target deleted users, trigger incorrect journey automations, inflate analytics metrics, and create data inconsistencies between MySQL and ClickHouse systems, potentially violating privacy regulations.
Solution 1: lightweight_mutation_projection_mode: 'ALLOW'
What it does: Allows ClickHouse to perform DELETE operations on tables with projections by enabling lightweight mutations.
Pros: ✅ Zero code changes - just a configuration update ✅ Immediate effect - deletions work instantly ✅ Storage efficient - no additional storage overhead ✅ Simple to understand - standard DELETE operations
Cons: ❌ Performance impact - projections need invalidation/rebuild after deletions ❌ Query variability - temporary performance degradation for projection-dependent queries
Solution 2: VersionedCollapsingMergeTree
What it does: Changes the user_events table to use sign-based "deletion" instead of actual DELETE operations.
Pros: ✅ No projection conflicts - works seamlessly with projections ✅ Consistent performance - no query performance degradation ✅ ClickHouse best practice - designed for this exact scenario
Cons: ❌ Storage overhead - requires storing deletion markers (sign = -1) ❌ Code changes needed - must modify deletion logic to use upsert instead of delete ❌ Migration complexity - requires schema changes and data migration ❌ Eventual consistency - actual deletion happens during background merges
@jeroenrinzema I just realised you’re referring to the Docker image build beta.10, published 6 days ago. I can’t find any corresponding source code for this image, so it’s hard to tell if the issue still persists. 🤷♂️
@pushchris, are Docker images built from a separate copy of the repository? 🤔