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

clausnielsen · May 4, 2021, 7:24pm

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

Kind regards
Claus Nielsen

pierreguillot · May 5, 2021, 7:43am

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?

clausnielsen · May 5, 2021, 8:32am

Here you go

pierreguillot · May 5, 2021, 9:43am

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

clausnielsen · May 5, 2021, 9:57am

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 ?

clausnielsen · May 5, 2021, 10:04am

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.

pierreguillot · May 5, 2021, 11:59am

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.

clausnielsen · May 5, 2021, 12:15pm

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…

pierreguillot · May 5, 2021, 1:04pm

Yep, sorry. This link:

github.com

SonarSource/sonarqube/blob/8.5.1.38104/server/sonar-db-dao/src/schema/schema-sq.ddl

###############################################################
####  Description of SonarQube's schema in H2 SQL syntax   ####
####                                                       ####
####   This file is autogenerated and stored in SCM to     ####
####   conveniently read the SonarQube's schema at any     ####
####   point in time.                                      ####
####                                                       ####
####          DO NOT MODIFY THIS FILE DIRECTLY             ####
####    use gradle task :server:sonar-db-dao:dumpSchema    ####
###############################################################

CREATE TABLE "SCHEMA_MIGRATIONS"(
    "VERSION" VARCHAR(255) NOT NULL
);

CREATE TABLE "ACTIVE_RULE_PARAMETERS"(
    "VALUE" VARCHAR(4000),
    "RULES_PARAMETER_KEY" VARCHAR(128),
    "UUID" VARCHAR(40) NOT NULL,
    "ACTIVE_RULE_UUID" VARCHAR(40) NOT NULL,

This file has been truncated. show original

clausnielsen · May 7, 2021, 12:04pm

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

pierreguillot · May 10, 2021, 11:02am

Glad to read that!

system · June 28, 2021, 8:26pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.