Bitbucket upgrade to 6 database failure

[x4e-markus]

Hi

We're using the atlassian/bitbucket-server Docker container (version 5.16.6 at the time of writing) with PostgreSQL 9.6.
When upgrading to any container of version 6 (6.0, 6.1, 6.2, 6.3), we see the following failure during the migration of our database:

Failed SQL: ALTER TABLE public.cwd_granted_perm DROP CONSTRAINT fk_cwd_granted_perm_grp_map

Full context:

INFO [spring-startup] c.a.s.internal.home.HomeLockAcquirer Successfully acquired lock on home directory /var/atlassian/application-data/bitbucket
INFO [spring-startup] c.a.s.internal.home.HomeValidator Upgrading Bitbucket from 5.16.6 to 6.3.2 is a backward-incompatible upgrade. The home directory will be upgraded with a new layout (9.0.0 -> 10.0.0). It will not be possible to downgrade to an older version without restoring from a backup
INFO [spring-startup] c.a.s.internal.home.HomeLockAcquirer Successfully acquired lock on home directory /var/atlassian/application-data/bitbucket/shared
WARN [spring-startup] com.hazelcast.instance.Node [===IP===]:5701 [daemon] [3.11.1] No join method is enabled! Starting standalone.
ERROR [spring-startup] liquibase.changelog.ChangeSet Change Set liquibase/r6_0/upgrade.xml::BBSDEV-16950-10::fdoherty failed. Error: ERROR: constraint "fk_cwd_granted_perm_grp_map" of relation "cwd_granted_perm" does not exist [Failed SQL: ALTER TABLE public.cwd_granted_perm DROP CONSTRAINT fk_cwd_granted_perm_grp_map]
WARN [spring-startup] o.s.w.c.s.XmlWebApplicationContext Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'crowdAliasDao': Unsatisfied dependency expressed through method 'setSessionFactory' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [stash-context.xml]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set liquibase/r6_0/upgrade.xml::BBSDEV-16950-10::fdoherty:
xception.DatabaseException: ERROR: constraint "fk_cwd_granted_perm_grp_map" of relation "cwd_granted_perm" does not exist [Failed SQL: ALTER TABLE public.cwd_granted_perm DROP CONSTRAINT fk_cwd_granted_perm_grp_map]
INFO [spring-startup] c.a.s.internal.home.HomeLockAcquirer Releasing lock on /var/atlassian/application-data/bitbucket/shared
INFO [spring-startup] c.a.s.internal.home.HomeLockAcquirer Releasing lock on /var/atlassian/application-data/bitbucket
ERROR [spring-startup] c.a.j.s.w.s.JohnsonDispatcherServlet SpringMVC dispatcher [springMvc] could not be started
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'crowdAliasDao': Unsatisfied dependency expressed through method 'setSessionFactory' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [stash-context.xml]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set liquibase/r6_0/upgrade.xml::BBSDEV-16950-10::fdoherty:
Reason: liquibase.exception.DatabaseException: ERROR: constraint "fk_cwd_granted_perm_grp_map" of relation "cwd_granted_perm" does not exist [Failed SQL: ALTER TABLE public.cwd_granted_perm DROP CONSTRAINT fk_cwd_granted_perm_grp_map]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredMethodElement.inject(AutowiredAnnotationBeanPostProcessor.java:676)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:877)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549)
at javax.servlet.GenericServlet.init(GenericServlet.java:158)
at java.lang.Thread.run(Unknown Source)
... 18 frames trimmed
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [stash-context.xml]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set liquibase/r6_0/upgrade.xml::BBSDEV-16950-10::fdoherty:
Reason: liquibase.exception.DatabaseException: ERROR: constraint "fk_cwd_granted_perm_grp_map" of relation "cwd_granted_perm" does not exist [Failed SQL: ALTER TABLE public.cwd_granted_perm DROP CONSTRAINT fk_cwd_granted_perm_grp_map]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1762)
... 5 common frames omitted
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set liquibase/r6_0/upgrade.xml::BBSDEV-16950-10::fdoherty:
Reason: liquibase.exception.DatabaseException: ERROR: constraint "fk_cwd_granted_perm_grp_map" of relation "cwd_granted_perm" does not exist [Failed SQL: ALTER TABLE public.cwd_granted_perm DROP CONSTRAINT fk_cwd_granted_perm_grp_map]
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:637)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:78)
at liquibase.Liquibase.update(Liquibase.java:202)
at liquibase.Liquibase.update(Liquibase.java:179)
at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:317)
at liquibase.database.ExtendedSpringLiquibase.afterPropertiesSet(ExtendedSpringLiquibase.java:32)
... 5 common frames omitted
Caused by: liquibase.exception.DatabaseException: ERROR: constraint "fk_cwd_granted_perm_grp_map" of relation "cwd_granted_perm" does not exist [Failed SQL: ALTER TABLE public.cwd_granted_perm DROP CONSTRAINT fk_cwd_granted_perm_grp_map]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:356)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:57)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:125)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1229)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1211)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:600)
... 11 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: constraint "fk_cwd_granted_perm_grp_map" of relation "cwd_granted_perm" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:352)
... 16 common frames omitted

[x4e-markus · Accepted solution]

I fixed the issue manually using a dummy constraint:

ALTER TABLE public.cwd_granted_perm ADD CONSTRAINT fk_cwd_granted_perm_grp_map CHECK (1=1);

Atlassian should use the following command to avoid this (requires PostgreSQL 9.0+):

ALTER TABLE public.cwd_granted_perm DROP CONSTRAINT IF EXISTS fk_cwd_granted_perm_grp_map

[Laurent Boivin]

During a (attempted) migration, the bitbucket-backup.jar complained about missing tables and an error related to a constraints on an existing table.
I fixed the errors the way you did: with SQL commands to create the missing database (using brand new Bitbucket to have the correct information).

Is there a tool to run a consistency check on the database, and many a tool to fix it?