MemberJunction / MemberJunction/MJ

mj sync push will fail on older upgraded installs after the EntityPermission uniqueness constraint (#3367) — seed IDs cannot match CodeGen-minted rows

Open
#4,400 0 comments 0 reactions 0 assignees View on GitHub
bug priority: high
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
1d 8h
Merged PRs (30d)
308

Description

## What breaks

After #3367, `mj sync push` will **fail loudly** on installs whose `EntityPermission` rows were created by CodeGen rather than restored from a baseline.

#3367 adds `UQ_EntityPermission_EntityID_RoleID_Type` and repoints four metadata seed records at the shipped baseline IDs. That fixes fresh installs and v5.46+-baseline installs, both verified. It cannot fix older upgraded installs, and the PR says so explicitly under `KNOWN LIMIT` in `b435d729a7`:

> `EntityPermission.ID` defaults to `newsequentialid()` and CodeGen never supplies one, so on a database whose rows came from CodeGen rather than a baseline restore they carry machine-specific IDs that no hardcoded seed ID can match. Fresh and v5.46+-baseline installs are fixed and verified; older upgraded installs will now fail sync push loudly rather than duplicate silently.

## Why it now fails instead of silently duplicating

Before the constraint, a seed record whose hardcoded ID didn't match the row already in the database simply **inserted a second row** for the same `(EntityID, RoleID, Type)`. That silent duplication is the very thing #3367 exists to stop — `GetUserPermisions` OR-merges duplicates, so the extra row was invisible at runtime but made the natural key non-unique.

With the constraint in place that insert violates `UQ_EntityPermission_EntityID_RoleID_Type` and `sync push` stops.

**Failing loudly is the right trade** — the alternative is re-introducing the duplicates. But it is a new failure mode for an existing population, and it arrives with a release rather than with a config change, so it needs to be visible before anyone upgrades.

## Scope

Installs upgraded from before v5.46 whose `EntityPermission` rows were minted by CodeGen. Not fresh installs, not v5.46+-baseline restores — both verified working in #3367.

## The real fix, per the PR

Quoting `b435d729a7` again — it names the options and deliberately does not attempt them:

> Seeding these from a migration with deterministic IDs, or natural-key matching in sync, is the real fix and is not attempted here.

Natural-key matching in MetadataSync is the more general of the two: it would make every seeded entity resilient to ID drift, not just `EntityPermission`.

## Needed before release

- A release note telling operators of older installs what they will see and how to resolve it (re-point the offending row, or delete the duplicate and re-run).
- Ideally, a pre-flight check so the failure arrives as a diagnosis rather than a constraint violation.

## Origin

Raised in my review of #3367 and accepted there as a documented residual — correctly, since the durable fix is out of scope for that PR. Filed so it is not lost: it was untracked as of 2026-09-11.

Contributor guide

Open the contributing guide

Research direction

Start by reading #3367 and commit b435d729a7, then trace MetadataSync's sync push handling for EntityPermission rows on older upgraded installs. Done means the failure is diagnosed before the uniqueness constraint violation, natural-key handling prevents ID-drift duplicates, and operators receive the required release guidance.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
backend, databases, documentation
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.