DB migration failed

#1

We are currently experiencing issues upgrading to 5.1 from 4.2.3, we are receving a DB migration failure:

failed to migrate database: 1 error occurred:
* Migration ‘1537546150_global_resource_version_history.up.sql’ failed: 2 errors occurred:
* Transaction INSERT INTO resource_disabled_versions (resource_id, version_md5)
SELECT vr.resource_id, md5(vr.version)
FROM versioned_resources vr
WHERE NOT enabled failed, rolled back the migration
* pq: duplicate key value violates unique constraint “resource_disabled_versions_resource_id_version_md5_uniq”

Any assistance or hints to get past this error would be greatly appreciated.

Thanks,

Jon Carmack
cloud.gov Platform team

#2

Shot in the dark: 5.x replaced version disabling with version pinning, and the message smells like it is around version disabled. I remember that the migration code of 5.x explicitly takes care of migrating from disabled to pinned, maybe you are hitting a corner case.

Do you have any version disabled ? For example git versions ? Maybe if they are few you can make a list, enable them, run the migration and re-disable them by hand?

Also, to prepare for a major version upgrade, what we do is to take a dump of the db (you never know :slight_smile: and run on it not concourse web but simply the migration utility, I forgot how it is called, if you run concourse --help it should tell you.

#3

Thanks for the help Marco-m! Thankfully we were able to roll back to 4.2.3. We definitely have some disabled versions, many in fact. I’ll take a look there and try doing the migration manually as you suggested, that seems much more safe.

-Jon

#4

We are having the same issue.

We had two sets of rows that violated the contraint. We delete the duplicate rows.

Now we are having another contraint violation when the migration try to update the resource_configs table contraints.

failed to migrate database: 1 error occurred:
* Migration ‘1538493253_split_resource_config_unique_constraint.up.sql’ failed: 2 errors occurred:
* Transaction ALTER TABLE ONLY resource_configs
DROP CONSTRAINT resource_configs_resource_cache_id_base_resource_type_id_so_key,
ADD CONSTRAINT resource_configs_resource_cache_id_so_key UNIQUE (resource_cache_id, source_hash),
ADD CONSTRAINT resource_configs_base_resource_type_id_so_key UNIQUE (base_resource_type_id, source_hash) failed, rolled back the migration
* pq: could not create unique index “resource_configs_base_resource_type_id_so_key”

Now there is a lots more rows that violate that contraints and do not feel confortable to remove rows without understanding more. So we are also rolling back.

#5

We were able to upgrade to > 5.0 after simply enabling the conflicting disabled version. If we had more failures my next option was to possibly stand up a new deployment of concourse of 5.2.0 and just refly the pipelines. Might not be a bad option for you.

-Jon