Inconsistent review app database state leads to errors

Problem

In some situations, a review app database can diverge from the committed schema db/structure.sql. These can happen when the following happens (not extensive):

  • a merge request changes a database migration name
  • a migration has been renamed or deleted on master

This problem could manifest in a few ways:

  • During migration, it might find an existing column. This results in a failed migration during a review app deployment. See Example section below for an example.
  • the application code might encounter an active record method that does not have a corresponding column in the database. This results in an application error during end to end test and a failed end to end test job. Example: #344044 (closed).

Mitigation

A manual workaround currently available is to run the job review-stop which resets the review app namespace to a clean slate.

To improve this workflow and enable an automated recovery, we would need to:

  1. Identify when this situation has occurred (i.e current DB schema is out of sync with the actual database)
  2. Reset or remove the existing database before recreating it and running the entire migration again.
Example

Failed job: https://gitlab.com/gitlab-org/gitlab/-/jobs/1637912243

In this failed job, we can see the migration job failing.

In the logs we can see that the failure was due to an existing column:

PG::DuplicateColumn: ERROR:  column "tasks_to_be_done" of relation "members" already exists

The last few pipelines before it failed:

Screenshot_2021-10-01_at_11.07.16_AM

commit migration filename
363786b1 db/migrate/20210902134140_add_tasks_to_be_done_to_members.rb
414fcc28 db/migrate/20210902134140_add_tasks_to_be_done_to_members.rb
458fff22 db/migrate/20210921120535_add_tasks_to_be_done_to_members.rb

bin/rails db:migrate:status:

   up     20210921063924  Index labels using varchar pattern ops
  down    20210921120535  Add tasks to be done to members
  down    20210921155501  Add task project foreign key to members

But tasks_to_be_done column exists in members:

irb(main):002:0> ActiveRecord::Base.connection.columns('members').map(&:name)
=> ["id", "access_level", "source_id", "source_type", "user_id", "notification_level", "type", "created_at", "updated_at", "created_by_id", "invite_email", "invite_token", "invite_accepted_at", "requested_at", "expires_at", "ldap", "override", "state", "invite_email_success", "tasks_to_be_done", "tasks_project_id"]
Edited by Albert