Introduction
This article addresses potential migration issues when upgrading Terraform Enterprise (TFE) to version 202406-1 (776). This upgrade may fail if there are orphaned records in the memberships, teams, or subscriptions tables. Customers who haven't upgraded past June 2024 and have orphaned data in the memberships table are particularly at risk. To proactively avoid these issues, customers starting from version 202406-1 or lower should use the following Rails commands to check for issues before attempting the upgrade.
Membership.left_joins(:team).where(teams: {id: nil}).count
Team.left_joins(:organization).where(organizations: {id: nil}).count
and
Subscription.left_joins(:organization).where(organizations: {id: nil}).count
Problem
When upgrading Terraform Enterprise to v202406-1 (776), the upgrade fails.
In the logs the following lines can be observed.
{"log":"StandardError: An error has occurred, all later migrations canceled:","component":"sidekiq"}
{"log":"","component":"sidekiq"}
{"log":"PG::ForeignKeyViolation: ERROR: insert or update on table \"memberships\" violates foreign key constraint \"fk_rails_ae2aedcfaf\"","component":"sidekiq"}
{"log":"DETAIL: Key (team_id)=(24744) is not present in table \"teams\".","component":"sidekiq"}
Cause
During the upgrade process, there are checks being performed on the foreign keys for the Memberships. When a foreign key is pointing to a non-existing team (because it has been deleted) the upgrade process fails. These orphaned records are most likely remnants from deletion of teams in older TFE versions.
Engineering has identified 2 other foreign key checks that might cause issues.
The three possible issues are:
- Foreign key constraint of a Membership to a Team
- Foreign key constraint of a Team to an Organization
- Foreign key constraint of a Subscription to an Organization
Solution:
-
Roll back the environment to the previously working state. (Skip this step if, this article is being used as a pre-check before upgrading to v202406-1 (776)).
-
All of the following commands should be done in the rails console. It will backup and delete orphan records.
Since there are 3 potential sources of issues during migration, running these individually may be preferred and based on each result, the next step will be to run the delete command that applies to each (as needed).
Note!! Beware!!
If you have orphaned Teams you will NEED to run this delete query and delete Membership Query.
During the upgrade, orphaned teams will automatically be deleted. This might cause orphaned memberships, which in turn will fail the upgrade.
Same holds true for manually running the delete query on Teams.
Therefore it is important to run these queries in the correct order. First check and delete orphaned Teams, then check and delete orphaned Memberships.
Teams
To determine how many Teams have been affected, run:
Team.left_joins(:organization).where(organizations: {id: nil}).count
If the number of Teams is greater than zero, run the following to create a backup, and delete the orphan teams:
sql = <<~SQL
CREATE TABLE teams_orphans_deleted_20240904 AS
WITH deleted AS
(DELETE FROM teams WHERE organization_id NOT IN
(SELECT id FROM organizations)
returning *)
SELECT * FROM deleted;
SQL
ActiveRecord::Base.connection.execute(sql)
Memberships
To determine how many Memberships have been affected, run:
Membership.left_joins(:team).where(teams: {id: nil}).count
If the number of Memberships is greater than zero, run the following to create a backup, and delete the orphan memberships:
sql = <<~SQL
CREATE TABLE memberships_orphans_deleted_20240904 AS
WITH deleted AS
(DELETE FROM memberships WHERE team_id NOT IN
(SELECT id FROM teams)
returning *)
SELECT * FROM deleted
SQL
ActiveRecord::Base.connection.execute(sql)
Subscriptions
To determine how many Subscriptions have been affected, run:
Subscription.left_joins(:organization).where(organizations: {id: nil}).count
If the number of Subscriptions is greater than zero,run the following to create a backup, and delete the orphan subscriptions:
sql = <<~SQL
CREATE TABLE subscriptions_orphans_deleted_20240904 AS
WITH deleted AS
(DELETE FROM subscriptions WHERE organization_id NOT IN
(SELECT id FROM organizations)
returning *)
SELECT * FROM deleted;
SQL
ActiveRecord::Base.connection.execute(sql)
Outcome
After this cleanup, an upgrade to Terraform Enterprise v202406-1 (776) is successful.