Troubleshooting — Pragmatic.Migrations

FAQ, common errors, leader election issues, and recovery procedures.

---

Frequently Asked Questions

Q: How do I see what SQL will be executed before running it?

Use DryRun():

builder.UsePragmaticMigrations(m =>
{
    m.DryRun();
});

The MigrationResult.GeneratedSql contains the full script. The AppliedChanges list shows each individual SchemaChange with a Description and IsBreaking flag. The logs show each change with [safe] or [BREAKING] indicators.

Q: Can I run migrations for only one database in a multi-database setup?

Yes, use OnlyDatabase<T>():

builder.UsePragmaticMigrations(m =>
{
    m.OnlyDatabase<ShowcaseAppDatabase>();
});

Q: Do I need to install any CLI tools?

No. Unlike EF Core (dotnet ef migrations add), Pragmatic.Migrations requires no design-time tools. The SG generates the schema metadata at compile time, and the runner handles everything at startup.

Q: How does it handle EF Core’s __EFMigrationsHistory table?

The introspector automatically excludes __EFMigrationsHistory from the current schema. If you are migrating from EF Core Migrations to Pragmatic.Migrations, the old history table is ignored.

Q: What happens on first run with an empty database?

The introspector returns an empty SchemaVersion. The diff engine produces a CreateTable change for every table. All changes are IsBreaking = false, so no Force is needed.

Q: Can I use Pragmatic.Migrations alongside EF Core Migrations?

Technically yes, but it is not recommended. Both systems would compete for schema ownership. If you need to coexist temporarily during migration, use OnlyDatabase<T>() to partition which databases each system manages.

---

Common Errors

Error: “Cannot create connection: type ‘X’ not found”

Full message: InvalidOperationException: Cannot create connection: type 'Npgsql.NpgsqlConnection, Npgsql' not found. Ensure the provider NuGet package is referenced.

Cause: The database provider NuGet package is not referenced in the host project.

Fix: Add the provider package to the host .csproj:

<PackageReference Include="Npgsql" Version="9.*" />
<!-- or -->
<PackageReference Include="Microsoft.Data.SqlClient" Version="6.*" />
<!-- or -->
<PackageReference Include="Microsoft.Data.Sqlite" Version="9.*" />

---

Error: “Breaking changes detected (N). Use Force=true to apply.”

Cause: The diff engine found changes that may cause data loss (DROP TABLE, DROP COLUMN, SET NOT NULL, narrowing type changes). At host startup this aborts startup — the host throws InvalidOperationException and does not boot.

Fix: Inspect the changes first:

m.DryRun();

Then apply with explicit acknowledgment:

m.Force();

Review the MigrationResult.AppliedChanges to understand exactly what will be dropped or altered.

---

Error: “column X of relation Y contains null values”

Cause: A property was changed from nullable to non-nullable, but existing rows have NULL values.

Fix: Update NULLs before deploying:

UPDATE "Users" SET "Email" = '' WHERE "Email" IS NULL;

Then redeploy with the non-nullable property. The runner provides this suggestion automatically in MigrationResult.Suggestions.

---

Error: “Migration failed at step N/M: ADD FOREIGN KEY”

Cause: Orphan rows exist that violate the foreign key constraint.

Fix:

1. Identify orphan rows:

   SELECT * FROM "Orders"
   WHERE "CustomerId" NOT IN (SELECT "Id" FROM "Customers");

2. Clean up orphans:

   DELETE FROM "Orders"
   WHERE "CustomerId" NOT IN (SELECT "Id" FROM "Customers");

3. Re-run the migration.

The runner suggests: “FK creation failed — orphan rows may exist that violate the constraint.”

---

Error: “Check database user permissions — the migration user needs DDL privileges”

Cause: The database user in the connection string lacks CREATE TABLE, ALTER TABLE, or DROP permissions.

Fix: Grant DDL privileges:

-- PostgreSQL
GRANT ALL ON SCHEMA public TO myuser;

-- SQL Server
ALTER ROLE db_ddladmin ADD MEMBER myuser;

For production, use a dedicated migration user with DDL privileges, separate from the application user.

---

Leader Election Issues

Multiple instances migrating simultaneously

Symptom: Duplicate table errors or deadlocks on startup.

Cause: Leader election failed to coordinate. Possible reasons:

1. Different connection strings pointing to different databases
2. Custom IMigrationLeaderElection that always returns true
3. The __PragmaticLock table was dropped manually

Diagnosis: Check the logs for:

Migration leader elected: {HostId}
Another instance is the migration leader -- waiting

If both instances log “leader elected”, the lock mechanism is not working correctly.

Fix: Verify all instances use the same connection string. Ensure the __PragmaticLock table exists. The runner creates it automatically, but manual deletion breaks coordination.

---

Leader crashed, followers waiting indefinitely

Symptom: Host instances stuck at startup, logs show “Waiting for leader to complete migrations…” but never proceed.

Cause: The leader process crashed after acquiring the lock but before releasing it. The lock has not expired yet.

Fix: Wait for the lock to expire (default: 5 minutes). Or manually release:

-- PostgreSQL
UPDATE "__PragmaticLock"
SET "HolderId" = NULL, "AcquiredAt" = NULL, "ExpiresAt" = NULL
WHERE "LockName" = 'migration';

-- SQL Server
UPDATE __PragmaticLock
SET HolderId = NULL, AcquiredAt = NULL, ExpiresAt = NULL
WHERE LockName = 'migration';

After the lock is released, followers will proceed within 2 seconds (the polling interval).

---

FallbackLeaderElection warning in logs

Log message: Leader election failed -- proceeding as leader (fallback)

Cause: The DatabaseLeaderElection threw an exception (e.g., connection refused, permission denied), and FallbackLeaderElection wrapped the error and proceeded as leader.

Risk: If multiple instances hit this fallback simultaneously, they all become “leaders” and may conflict.

Fix: Investigate why DatabaseLeaderElection failed. Common causes:

• Connection string invalid for the lock table
• Database not reachable at startup time
• Insufficient permissions to CREATE TABLE

---

Recovery Procedures

Recovery After a Failed Migration

When a migration fails, the transaction is rolled back automatically. The database is in its original state. To recover:

1. Check MigrationResult.FailedChangeIndex and FailedChangeSql to understand what failed
2. Read the Suggestions for context-aware guidance
3. Fix the underlying issue (data cleanup, permissions, etc.)
4. Restart the host — the runner will retry from scratch

Recovery After Manual Schema Modification

If the database was modified outside the migration pipeline:

1. Run with DryRun() to see what the runner thinks needs to change
2. If the changes are correct, run normally
3. If the runner wants to undo manual changes, either:
   • Revert the manual changes
   • Update [Entity] definitions to match the manual changes

Recovery After Database Restore

After restoring a backup:

1. Ensure __PragmaticSchema was included in the backup
2. Run the host — the hash check will skip if the schema matches
3. If the runner detects changes (backup was from an older version), let it apply them
4. Run with DryRun() first if uncertain

Stuck __PragmaticLock

If the lock table is corrupted or the holder ID does not match any running instance:

-- Reset the lock (all providers)
UPDATE "__PragmaticLock"
SET "HolderId" = NULL, "AcquiredAt" = NULL, "ExpiresAt" = NULL
WHERE "LockName" = 'migration';

Or drop and let the runner recreate it:

DROP TABLE IF EXISTS "__PragmaticLock";

---

Diagnostic Checklist

When migrations are not working as expected, check these in order:

#	Check	How
1	Is UsePragmaticMigrations() called?	Search Program.cs for the call
2	Is the provider package referenced?	Check host .csproj for Npgsql / Microsoft.Data.SqlClient
3	Is the connection string correct?	Verify appsettings.json ConnectionStrings section
4	Is the database reachable?	Test with psql / sqlcmd / sqlite3 directly
5	Are migration logs appearing?	Set Pragmatic.Migrations log level to Debug
6	Is leader election working?	Check logs for “leader elected” / “waiting” messages
7	Are there breaking changes?	Run with DryRun() to inspect
8	Is the audit table intact?	Query __PragmaticSchema for recent entries

---

Log Level Configuration

For detailed migration diagnostics:

{
  "Logging": {
    "LogLevel": {
      "Pragmatic.Migrations": "Debug"
    }
  }
}

At Debug level, the runner logs each individual change being applied. At Information level (default), it logs the start, diff summary, and completion.