RunPlanOnlyNonNull Migration Error After Upgrading Terraform Enterprise >= v202305-1

Problem

Database migrations fail when you upgrade to Terraform Enterprise version v202305-1 or later. The failure occurs on the RunPlanOnlyNonNull migration with a PG::CheckViolation error, indicating that the runs_plan_only_not_null check constraint is violated by an existing row.

The log output includes the following errors.

PG::CheckViolation: ERROR: check constraint "runs_plan_only_not_null" is violated by some row
...
Caused by:
ActiveRecord::StatementInvalid: PG::CheckViolation: ERROR: check constraint "runs_plan_only_not_null" is violated by some row
...
Caused by:
PG::CheckViolation: ERROR: check constraint "runs_plan_only_not_null" is violated by some row
...
2024-03-13 14:50:01 [INFO] Migrating to RunPlanOnlyNonNull (20230505152154)
== 20230505152154 RunPlanOnlyNonNull: migrating ===============================
-- execute("alter table runs add constraint runs_plan_only_not_null check (plan_only is not null) not valid;")
 -> 0.0035s
-- execute("alter table runs validate constraint runs_plan_only_not_null;")
- migrations done! removing lock...
- exiting with failure - see migration output for details

Prerequisites

• You are upgrading a Terraform Enterprise instance to version v202305-1 or later.

Cause

Terraform Enterprise v202206-1 introduced a background database migration, BackfillPlanOnlyOnRuns, which is also used as a synchronous migration in version v202305-1 and later.

If the background migration ran successfully in a previous version, the system marks it as complete. During the upgrade to v202305-1 or later, the synchronous migration is skipped because it has the same name. If any runs were created with a null value for the plan_only attribute between these versions, the subsequent RunPlanOnlyNonNull migration fails because it enforces a not null constraint that is violated by this data.

To confirm this cause, complete the following steps.

1. Start a Rails console and verify that there are runs with a nullplan_only attribute.

   irb(main):001:0> Run.where(plan_only: nil).any?
   => true

2. Inspect the background migration to confirm it was marked as complete before the current upgrade.

   irb(main):002:0> BackgroundMigration.find_by(migration_class: "DataMigrations::BackfillPlanOnlyOnRuns").complete_at?
   => true
   
   irb(main):003:0> BackgroundMigration.find_by(migration_class: "DataMigrations::BackfillPlanOnlyOnRuns").complete_at
   => Wed, 27 Mar 2023 15:37:21.988129000 UTC +00:00

If your environment meets the prerequisites and you have confirmed the cause, proceed to the solution.

Solution

To resolve this issue, you must manually reset the migration's completion status and re-run it from the Rails console. Then, you must remove a leftover database constraint before restarting the application.

1. Start a Rails console.

2. Execute the following commands to find the background migration, reset its completion status to nil, and invoke the migration again.

   migration = BackgroundMigration.find_by(migration_class: "DataMigrations::BackfillPlanOnlyOnRuns")
   migration.complete_at = nil
   migration.save!
   BackgroundMigration.perform_all(migration.migration_class)

3. Verify that no runs in the runs table have a null value for plan_only.

   irb(main):002:0> Run.where(plan_only: nil).any?
   => false

4. Remove the leftover constraint from the runs table. The previous migration failure created this constraint. If you do not remove it, the RunPlanOnlyNonNull migration will fail again with a PG::DuplicateObject error.

   The constraint will be safely re-created and validated when the migration runs again after you restart the application.

   Choose the command that matches your Terraform Enterprise version and deployment type.

   For Terraform Enterprise >= v202309-1

   This applies to Replicated deployments in consolidated services or Flexible Deployment Options.

   $ docker exec terraform-enterprise bash -c '. atlas-env && psql $DATABASE_URL -c "alter table runs drop constraint runs_plan_only_not_null;"'

   For Terraform Enterprise < v202309-1

   This applies to Replicated deployments in non-consolidated services.

   $ docker exec tfe-atlas bash -c '. atlas-env && psql $DATABASE_URL -c "alter table runs drop constraint runs_plan_only_not_null;"'

Outcome

After you complete these steps and restart the Terraform Enterprise application, the database migrations should proceed successfully.

Additional Information

• Terraform Enterprise Upgrades
• For more information on database constraints, refer to the PostgreSQL documentation on adding a table constraint.