Upgrade from SonarQube 7.9.6 to 8.9.6 fails with db error (PostgreSQL 11.14)

  • versions used (SonarQube, Scanner, Plugin, and any relevant extension)

Upgraded SonarQube 6.7 LTS to 7.9.6 LTS with no issues. Upgraded PostgreSQL from 9.4 to 11.14 with no issues. When upgrading SonarQube7.9.6 to 8.9.6 LTS, the upgrade fails with the following log output:

  • error observed
2022.01.12 19:30:08 INFO  web[][DbMigrations] #3603 'Populate 'group_uuid' for 'GROUP_ROLES'': success | time=9917ms
2022.01.12 19:30:08 INFO  web[][DbMigrations] #3604 'Drop index on 'group_id' column of 'GROUP_ROLES' table'...
2022.01.12 19:30:08 INFO  web[][DbMigrations] #3604 'Drop index on 'group_id' column of 'GROUP_ROLES' table': success | time=10ms
2022.01.12 19:30:08 INFO  web[][DbMigrations] #3605 'Add index on 'group_uuid' column of 'GROUP_ROLES' table'...
2022.01.12 19:30:09 INFO  web[][DbMigrations] #3605 'Add index on 'group_uuid' column of 'GROUP_ROLES' table': success | time=590ms
2022.01.12 19:30:09 INFO  web[][DbMigrations] #3606 'Add 'group_uuid' column on 'GROUPS_USERS' table'...
2022.01.12 19:30:09 INFO  web[][DbMigrations] #3606 'Add 'group_uuid' column on 'GROUPS_USERS' table': success | time=2ms
2022.01.12 19:30:09 INFO  web[][DbMigrations] #3607 'Populate 'group_uuid' for 'GROUPS_USERS''...
2022.01.12 19:30:09 ERROR web[][DbMigrations] #3607 'Populate 'group_uuid' for 'GROUPS_USERS'': failure | time=73ms
2022.01.12 19:30:09 ERROR web[][DbMigrations] Executed DB migrations: failure | time=9228736ms
2022.01.12 19:30:09 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=9228958ms
2022.01.12 19:30:09 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration ended with an exception
org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #3607 'Populate 'group_uuid' for 'GROUPS_USERS'' failed
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:79)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:67)
        at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:405)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:52)
        at org.sonar.server.platform.db.migration.engine.MigrationEngineImpl.execute(MigrationEngineImpl.java:68)
        at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doUpgradeDb(DatabaseMigrationImpl.java:105)
        at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doDatabaseMigration(DatabaseMigrationImpl.java:80)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: java.sql.BatchUpdateException: Batch entry 0 delete from groups_users where user_id = '3745' and group_id = 17961 was aborted: ERROR: operator does not exist: integer = character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 40  Call getNextException to see other errors in the batch.
        at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:169)
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:862)
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:901)
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1644)
        at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)
        at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)
        at org.sonar.server.platform.db.migration.step.UpsertImpl.execute(UpsertImpl.java:68)
        at org.sonar.server.platform.db.migration.step.MassUpdate.closeStatements(MassUpdate.java:139)
        at org.sonar.server.platform.db.migration.step.MassUpdate.execute(MassUpdate.java:94)
        at org.sonar.server.platform.db.migration.version.v84.groups.groupsusers.PopulateGroupsUsersGroupUuid.execute(PopulateGroupsUsersGroupUuid.java:44)
        at org.sonar.server.platform.db.migration.step.DataChange.execute(DataChange.java:44)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
        ... 9 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 40
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:859)
        ... 19 common frames omitted

Any help/suggestions appreciated. Let me know if you require any further information.

Thanks,
DaveM

Heya SonarSource team,

Would it be possible to get a response? This APPEARS to be a straight-up bug in the upgrade process, and is blocking our upgrade testing.

Thanks a lot,
Dave