Fresh-state migration failures are masked by a bogus rollback error (original exception discarded, never logged)

[zbyte64]

Summary

Any exception raised inside StateMigrator.migrate() after update_versions() is replaced by an unrelated SQLMeshError("There are no prior migrations to roll back to."). The original exception is never logged, and _default_exception_handler only prints str(ex), so the cause is invisible — the operator sees a bogus rollback error and a state DB that is never initialized.

Verified on a database whose state tables don't exist yet, where the failure is deterministic and repeats on every run.

Versions

• sqlmesh 0.236.2, sqlglot 30.8.0, Python 3.12.7
• state in Postgres 17 (sqlmesh_state schema)

Code path (0.236.2)

sqlmesh/core/state_sync/db/migrator.py:

 85  def migrate(self, schema, skip_backup=False, promoted_snapshots_only=True):
 ...
102              if migrate_rows:
103                  self._migrate_rows(promoted_snapshots_only)
104              self.version_state.update_versions()
105              analytics.collector.on_migration_end(...)     # raises here
...
113      except Exception as e:
114              self.rollback()                               # raises, discarding `e`
...
126          raise SQLMeshError("SQLMesh migration failed.") from e
...
130  def rollback(self) -> None:
143              raise SQLMeshError("There are no prior migrations to roll back to.")

On a fresh state DB the sequence is:

1. _apply_migrations() — no state tables exist, so _backup_state() (migrator.py:418-433) backs up nothing, and every migration's migrate_schemas runs. Returns True.
2. _migrate_rows() — no environments/snapshots, logs No changes to snapshots detected.
3. update_versions() — DELETE FROM sqlmesh_state._versions WHERE TRUE + INSERT of schema_version=SCHEMA_VERSION. The row is visible on the same connection, not yet committed.
4. The next statement in the same try raises.
5. The handler calls rollback(), which re-reads _versions, sees a non-zero schema_version (the row from step 3) instead of the 0 it entered migrate() with, finds no *_backup tables, and raises SQLMeshError("There are no prior migrations to roll back to.").
6. rollback()'s raise propagates and the real exception is dropped. Only __cause__ carries it, and the CLI does not print cause chains.

Repro

Any exception after update_versions() triggers it. This one needs only an unwritable SQLMESH_HOME (default ~/.sqlmesh) and a fresh state DB — in our case a container running as a uid that doesn't own its $HOME, which made CI unable to bootstrap SQLMesh state at all:

mkdir -p /tmp/ro-home && chmod 555 /tmp/ro-home
HOME=/tmp/ro-home sqlmesh -p your_project test     # fresh state DB

Output (nothing about a permission error anywhere):

Initializing new project state...
INFO ... Applying migration <...v0000_baseline...> ... <...v0102_normalize_python_env_payloads...>
INFO ... Fetching environments
INFO ... Migrating snapshot rows...
INFO ... No changes to snapshots detected
INFO ... Starting migration rollback.
Error: There are no prior migrations to roll back to.

Every subsequent run prints Initializing new project state... again, because the transaction is discarded at process exit.

With SQLMESH_DEBUG=1 the same run shows both exceptions — the real one first:

ERROR ... Unhandled exception
Traceback (most recent call last):
  File ".../sqlmesh/core/state_sync/db/migrator.py", line 105, in migrate
    analytics.collector.on_migration_end(
  File ".../sqlmesh/core/analytics/collector.py", line 292, in on_migration_end
    self._add_event(
  File ".../sqlmesh/core/analytics/collector.py", line 328, in _add_event
    "user_id": self._user.id,
  File ".../sqlmesh/core/analytics/collector.py", line 339, in _user
    return User.load_or_create(self._sqlmesh_path)
  File ".../sqlmesh/core/analytics/collector.py", line 348, in load_or_create
    sqlmesh_path.mkdir(parents=True, exist_ok=True)
PermissionError: [Errno 13] Permission denied: '/home/dev-user/.sqlmesh'

During handling of the above exception, another exception occurred:
  File ".../sqlmesh/core/state_sync/db/migrator.py", line 114, in migrate
    self.rollback()
  File ".../sqlmesh/core/state_sync/db/migrator.py", line 143, in rollback
    raise SQLMeshError("There are no prior migrations to roll back to.")
sqlmesh.utils.errors.SQLMeshError: There are no prior migrations to roll back to.

(The failing statement in this case is the analytics user lookup, which writes user.yaml even with disable_anonymized_analytics set since disable_analytics() only swaps the dispatcher — mentioning it only because it's the trigger for this reproduction; the defect reported here is the masking, and any exception after update_versions() behaves the same.)

Note the same failure surfaces unmasked on a database whose state is already migrated (e.g. sqlmesh migrate fails in on_cli_command with a raw PermissionError traceback, since migrate() returns early and never reaches step 4). Only the fresh-state path produces the nonsense message — which is exactly the path every new deployment and every fresh CI database takes.

Expected

1. Never let rollback failures replace the original error. Wrap the self.rollback() call in the handler in its own try/except, log the rollback failure, and raise an error that names both problems while keeping e as the cause — e.g. SQLMeshError(f"SQLMesh migration failed: {e}") and logger.exception before rolling back, so the cause is visible without SQLMESH_DEBUG=1.
2. rollback() should not treat "no backup tables" as fatal when migrate() started from an empty state. At that point there is nothing to restore — the state tables were created by this migration. Dropping them (the schema_version == 0 branch already does this) and logging a warning is the honest outcome; the current raise (migrator.py:143) is only meaningful for the case where backups were expected to exist.
3. Related to (2): update_versions() runs before the last statement of the migration, so a failure at that point leaves the connection holding a "migrated" version row for a migration that is about to be treated as failed. Committing it (or moving it after the analytics call) would remove the ambiguity rather than relying on the transaction being discarded.

Impact: on a fresh state DB this turns any bootstrap-time error into an undiagnosable "There are no prior migrations to roll back to.", and the failed migration is silently retried from scratch on the next invocation. It took an instrumented SQLMESH_DEBUG=1 run against a live CI environment to find the real cause (a one-line permission error).

[cookieark7]

Thanks for the detailed write-up, the code path made this easy to follow. I'd like to pick it up if it's free.

I tried it on current main with DuckDB. With an unwritable SQLMESH_HOME I actually hit the PermissionError a bit earlier now, while the project loads, before migration starts (even with disable_anonymized_analytics: true). So I forced a failure right after update_versions() instead, and got the same masked error you saw:

SQLMeshError: There are no prior migrations to roll back to.
__context__: PermissionError('...')

While poking at it I noticed it isn't only a fresh-state problem. On an existing state, if the rollback itself fails (say the connection drops), the rollback error replaces the original one too. And even when nothing gets masked, the CLI only prints SQLMesh migration failed., without the cause.

Here's roughly what I have in mind:

• Base the rollback on the version migrate() started from, not the row it just wrote. A fresh state then gets cleaned up instead of hitting the backup check. The sqlmesh rollback command stays as it is.
• Catch and log rollback failures so they can't hide the original error.
• Include the original error in the message: SQLMesh migration failed: <cause>.
• Add tests for both cases, which fail on main today. Right now test_first_migration_failure only covers failures before update_versions().

One thing I wasn't sure about: should the on_migration_end call in the error handler be guarded too? If analytics is what failed, it raises again from there. Happy to go either way.