DB migration failed when upgrading from Enterprise edition version 8.5.1.38104 to version 8.9.0.43852

Hi,
I’m trying to upgrade SonarQube Server Enterprise Edition from 8.5.1.38104 to 8.9.0.43852. And as part of DB Upgrade I’m getting below exceptions in the logs:

2021.05.04 18:18:48 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 #4207 'Drop table 'alm_app_installs'' 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:834)
Caused by: java.lang.IllegalStateException: Fail to execute DROP INDEX alm_app_installs_owner
        at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:106)
        at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:86)
        at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:128)
        at org.sonar.server.platform.db.migration.version.v87.DropAlmAppInstallsTable.execute(DropAlmAppInstallsTable.java:41)
        at org.sonar.server.platform.db.migration.step.DdlChange.execute(DdlChange.java:45)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
        ... 9 common frames omitted
Caused by: java.sql.SQLException: ORA-01418: specified index does not exist

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1792)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1745)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:334)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
        at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:91)
        ... 14 common frames omitted
2021.05.04 18:19:34 INFO  web[][o.s.p.ProcessEntryPoint] Gracefully stopping process

I reported something similar here: DB migration failed when upgrading from Enterprise edition version 8.5.1.38104 to version 8.7.0.41497 - #4 by clausnielsen
After the fix: https://jira.sonarsource.com/browse/SONAR-14541 I tried to upgrade again without luck.

The problem reported now is on a fresh copy of our production instance 8.5.1.38104 - so the LTS 8.9.0.43852 also have some problems with the DB migration in this area.

Please help us :slight_smile:

Kind regards
Claus Nielsen

Hi, thanks for reporting this. SONAR-14541 fixed the issue with PK, not for regular indexes.

Could you please look up this alm_app_installs table and show me the indexes there, with their names?

Here you go :smile:

Weird, you are missing two indexes, alm_app_installs_owner and alm_app_installs_install.

This could be caused by a previous half-failed migration, or a backup-restore that did not include them.

in this table-specific case, I’m not really worried, because this migration 4207 is about dropping the alm_app_installs table. So remediation in your case is going to be:

  • Backup your database
  • Run the migration to this failing point
  • Drop all indexes on this table
  • Drop the table
  • Execute insert into schema_migrations(version) values (4207) (that mark this migration step as complete)
  • Resume the migration

I know database upgrade issues are a recurring pain point on your side. We improved a few points in the last weeks, and I admit that things are not yet ideal. We have a quite big technical debt on the database schema that we are resolved piece by piece. So things will improve over time :slight_smile:

Thanks a lot for the quick response. Doing manual stuff on our production database is a no-go as this is handled by another vendor. So how do we fix this ? Can you add a fix to the db migration process automating this ?

Regarding your comment: “This could be caused by a previous half-failed migration, or a backup-restore that did not include them.”

When we upgrade our production instance the upgrade has been tested on a clone of the production database and on the exact same version on the filesystem. We have not experienced failing DB migrations on the production server as we never upgrade unless the test server upgrades without errors. We never backup and restore the production instance - it’s backed up, but we have never restored the production instance.

We could, but you are the only one so far with this specific missing index. And as you can guess, it’s gonna be lower in priority for us to patch SQ for 1 user, VS the issues that several users face.

That’s an interesting piece of information for us, thanks for sharing. I’m not trying to put the responsibility on your side, that was only an assumption. The fact is that your database is in an unexpected state, and until that’s not resolved, you will have upgrade trouble.

What I suggest to move forward with your issues on the database is to compare what you actually got on your schema, versus what’s expected from SonarQube. We don’t have (yet) a tool for that (and I saw that you suggested that on the other thread, and it’s discussed internally in the SQ team), so what I suggest is to do that manually.

So, the database reference for SonarQube in 8.5.1 is this script. It’s for an H2 database, but I hope it’s understandable enough for you to compare with your actual schema on your production database. You want to look for missing tables, missing index, non-matching constraint names.

Please have a look, and let me know if you see a lot of differences. The goal here is to asset how different your database is.

Thanks for the response.
https://github.com/SonarSource/sonar-enterprise/blob/8.5.1.38104/server/sonar-db-dao/src/schema/schema-sq.ddl gives this:

I guess it’s in a private Github repo…

Yep, sorry. This link:

Running this on the test instance and afterwards running the upgrade solved the problem:
CREATE UNIQUE INDEX “ALM_APP_INSTALLS_OWNER” ON “ALM_APP_INSTALLS”(“ALM_ID”, “OWNER_ID”);
CREATE UNIQUE INDEX “ALM_APP_INSTALLS_INSTALL” ON “ALM_APP_INSTALLS”(“ALM_ID”, “INSTALL_ID”);
CREATE INDEX “ALM_APP_INSTALLS_EXTERNAL_ID” ON “ALM_APP_INSTALLS”(“USER_EXTERNAL_ID”);

The problem was the missing indexes on the table. Thanks a lot for helping out - we are now able to update our production instance to LTS 8.9 Enterprise Edition