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
- 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
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