concourse: Problem upgrade from 3.x to 4.x - Migration Failure

Bug Report

I’m attempting to upgrade to 3.13 to 4.2.1 and I ran into an issue with migrations.

failed to migrate database: 1 error occurred:

* Migration '1510262030_initial_schema.up.sql' failed: 2 errors occurred:

* Transaction CREATE TYPE build_status AS ENUM (
        'pending',
      'started',
      'aborted',
      'succeeded',
      'failed',
      'errored'
  ) failed, rolled back the migration
* pq: type "build_status" already exists

Steps to Reproduce

I’m not entirely sure how to reproduce this. But I can’t delete this TYPE because it’s being referenced by a table.

This might be because I accidentally ran multiple web nodes at the same time on the new version. Each of which attempted to migrate, and one of them ultimately failed.

Expected Results

Migrations are idempotent

Actual Results

A migration most likely started, got interrupted, and I cannot continue the migration.

Additional Context

I checked out this page: https://github.com/concourse/concourse/issues/2074

SELECT * FROM public.schema_migrations
ORDER BY version ASC 

"1524079655"	false

Version Info

Concourse version: 4.2.1
Deployment type (BOSH/Docker/binary): Kubernetes/docker
Infrastructure/IaaS: Kubernetes
Browser (if applicable): N/A
Did this used to work? First time trying to upgrade to 4.2.1

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 15 (8 by maintainers)

Commits related to this issue

atc: don't depend on superuser privileges for migrations concourse/concourse#2724 Signed-off-by: Rui Yang <ryang@pivotal.io> — committed to concourse/concourse by deleted user 6 years ago

Most upvoted comments

@geofffranks from the code https://github.com/concourse/concourse/blob/f2566cb34792ced342e4725a1063da4cecb84bfb/atc/db/migration/migration.go#L439-L441

I feel like it returns 0 (since migration_history table exists while you upgraded to 4.2.1) and make it think there is no old version so it tries to start from beginning. I think if you remove migration_history table and try again it might work. However I recommend you to back up your data still just in case, unless you are 100% sure.

xtremerui on Aug 26, 2019

The schema_migrations table gets populated for the first time in Concourse v3.7. The new migrations_history (added in v4.1) expects the schema_migrations table to be populated as it uses that to determine what migrations have already been run.

What happened here was the @dprotaso attempted an upgrade from v3.6 to v4.2.1 (because our website didn’t mention anything about v3.7+). The website has since been updated to mention that you should upgrade to any version between v3.7-v4.0 inclusive, before upgrading to v4.1 or above.

ddadlani on Nov 29, 2018