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

clausnielsen · March 2, 2021, 10:17am

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

>     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 ALTER TABLE alm_app_installs DROP CONSTRAINT pk_alm_app_installs
>             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:38)
>             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-02443: Cannot drop constraint  - nonexistent constraint
> 
>             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
```

pierreguillot · March 2, 2021, 11:04am

Hi, welcome to the community forum. I need to confirm that, but I think this is an error in SQ codebase, and we may craft a bugfix release to fix that.

If you really can’t wait for a potential fix, you can follow this procedure to mimic this migration step yourself. Nothing really fancy, it’s about deleting everything related to the table ALM_APP_INSTALLS that is not used anymore.

If you choose to give this workaround a try, I suggest you follow these instructions, in order, without skipping any steps:

Backup your database
Run the migration until this point where it fails
Retrieve the real PK constraint on that table:

SELECT constraint_name
FROM user_constraints
WHERE table_name = ALM_APP_INSTALLS AND constraint_type='P'

Once you got it, you can drop it yourself:

ALTER TABLE ALM_APP_INSTALLS DROP CONSTRAINT insert_your_constraint_name_here DROP INDEX

Then, you need to delete indexes on that table:

DROP INDEX alm_app_installs_owner;
DROP INDEX alm_app_installs_install;
DROP INDEX alm_app_installs_external_id;

Drop the sequence, if any (ignore error code -2289, it means that this sequence does not exist):

DROP SEQUENCE ALM_APP_INSTALLS_seq;

Drop the trigger, if any (ignore error code -4080, it means the trigger does not exist):

DROP TRIGGER ALM_APP_INSTALLS_idt;

Finally, drop the table:

DROP TABLE ALM_APP_INSTALLS;

If everything went smooth until here, you are safe to skip this migration step:

INSERT INTO schema_migrations ('version') VALUES('4207');

Commit the insert if you don’t have an auto-commit mechanism.
Restart SonarQube, the migration will resume to the next migration step.

clausnielsen · March 2, 2021, 11:30am

Hi Pierre - Thanks a lot for the fast response

Waiting for a fix is the best option for us - as we need to do exactly the same on our production instance. Our upgrade process includes 100% automation via Ansible - so fiddling with SQL by-hand is a no-go.

Let me know if you confirm this as a bug which will be fixed. Thanks in advance.

pierreguillot · March 2, 2021, 11:33am

And I now realise that you may run into the same issue with two other migrations after this one. So I suggest you rollback on your 8.5 version and wait for the fix. I guess we will release a bugfix in a week or so.

Tracking ticket is here.

pierreguillot · March 2, 2021, 12:42pm

And thank you for reporting your issue here, it’s helping us a lot

clausnielsen · March 2, 2021, 1:41pm

Your welcome

I will rollback the upgrade. I’m really looking forward to the point in time where SonarSource releases LTS versions more often

Chris · March 18, 2021, 2:47pm

Hi,
SonarQube 8.7.1 should now solve this issue.
You’ll find more details about this version in the release notes.

clausnielsen · March 19, 2021, 7:09am

Awesome

Thanks a lot and have a nice weekend !

clausnielsen · April 9, 2021, 2:41pm

OMG same error present when trying to upgrade to 8.8 - forgot to merge the changes to the 8.8 branch ??

2021.04.09 16:38:22 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=102ms
2021.04.09 16:38:22 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: Cannot find constraint for table 'alm_app_installs'
        at org.sonar.server.platform.db.migration.sql.DbPrimaryKeyConstraintFinder.constraintNotFoundException(DbPrimaryKeyConstraintFinder.java:115)
        at org.sonar.server.platform.db.migration.sql.DbPrimaryKeyConstraintFinder.lambda$findConstraintName$0(DbPrimaryKeyConstraintFinder.java:48)
        at java.base/java.util.Optional.orElseThrow(Optional.java:408)
        at org.sonar.server.platform.db.migration.sql.DbPrimaryKeyConstraintFinder.findConstraintName(DbPrimaryKeyConstraintFinder.java:48)
        at org.sonar.server.platform.db.migration.sql.DropPrimaryKeySqlGenerator.generate(DropPrimaryKeySqlGenerator.java:54)
        at org.sonar.server.platform.db.migration.sql.DropPrimaryKeySqlGenerator.generate(DropPrimaryKeySqlGenerator.java:49)
        at org.sonar.server.platform.db.migration.version.v87.DropAlmAppInstallsTable.execute(DropAlmAppInstallsTable.java:40)
        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
Waiting for data... (interrupt to abort)

dmeneses · April 12, 2021, 3:43am

Hi,
Looks like there is no primary key for the table alm_app_installs.
The problem tat was fixed was that sometimes the primary key constraint name is something different than what was expected. This is a problem we’ve had a few times in the past.
I suspect that the bug fix 8.7.1 would not fix your problem. You never end up trying, right?

clausnielsen · April 12, 2021, 5:50am

Hi Duarte,

No I never ended up trying the 8.7.1 version. I really really think that SonarSource should invest more time in the “upgrade testing” and/or also make LTS versions more often.

We use way to much time on failed upgrade db scripts on our test of the upgrade including rollback on DB level.

Kind regards
Claus Nielsen
Tech Lead Nykredit Realkredit

dmeneses · April 12, 2021, 3:34pm

If you had other failures during DB upgrades, we’d be happy to learn about them so that we can address them.
We’ve created a new ticket to solve the issue: https://jira.sonarsource.com/browse/SONAR-14686

dmeneses · April 12, 2021, 3:47pm

Also note that your DB is at an unexpected state, since a PK is missing. We don’t know if that’s due to corruption or because of failed DB migrations in the past, but that explains why our internal testing wouldn’t be able to find the issue and why no other users have had the problem as far as we know.

clausnielsen · April 14, 2021, 8:18am

I have tried to upgrade from 8.5.1.38104 to version 8.7.1.42226 and I get this error:

2021.04.14 10:14:04 ERROR es[o.e.b.Bootstrap] Exception
java.lang.IllegalStateException: cannot downgrade a node from version [7.11.2] to version [7.10.2]
at org.elasticsearch.env.NodeMetadata.upgradeToCurrentVersion(NodeMetadata.java:94) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.env.NodeEnvironment.loadNodeMetadata(NodeEnvironment.java:433) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.env.NodeEnvironment.(NodeEnvironment.java:330) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.node.Node.(Node.java:362) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.node.Node.(Node.java:289) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Bootstrap$5.(Bootstrap.java:227) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Bootstrap.setup(Bootstrap.java:227) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Bootstrap.init(Bootstrap.java:393) [elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Elasticsearch.init(Elasticsearch.java:170) [elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Elasticsearch.execute(Elasticsearch.java:161) [elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.cli.EnvironmentAwareCommand.execute(EnvironmentAwareCommand.java:86) [elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.cli.Command.mainWithoutErrorHandling(Command.java:127) [elasticsearch-cli-7.10.2.jar:7.10.2]
at org.elasticsearch.cli.Command.main(Command.java:90) [elasticsearch-cli-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:126) [elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:92) [elasticsearch-7.10.2.jar:7.10.2]
2021.04.14 10:14:04 ERROR es[o.e.b.ElasticsearchUncaughtExceptionHandler] uncaught exception in thread [main]
org.elasticsearch.bootstrap.StartupException: java.lang.IllegalStateException: cannot downgrade a node from version [7.11.2] to version [7.10.2]
at org.elasticsearch.bootstrap.Elasticsearch.init(Elasticsearch.java:174) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Elasticsearch.execute(Elasticsearch.java:161) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.cli.EnvironmentAwareCommand.execute(EnvironmentAwareCommand.java:86) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.cli.Command.mainWithoutErrorHandling(Command.java:127) ~[elasticsearch-cli-7.10.2.jar:7.10.2]
at org.elasticsearch.cli.Command.main(Command.java:90) ~[elasticsearch-cli-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:126) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:92) ~[elasticsearch-7.10.2.jar:7.10.2]
Caused by: java.lang.IllegalStateException: cannot downgrade a node from version [7.11.2] to version [7.10.2]
at org.elasticsearch.env.NodeMetadata.upgradeToCurrentVersion(NodeMetadata.java:94) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.env.NodeEnvironment.loadNodeMetadata(NodeEnvironment.java:433) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.env.NodeEnvironment.(NodeEnvironment.java:330) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.node.Node.(Node.java:362) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.node.Node.(Node.java:289) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Bootstrap$5.(Bootstrap.java:227) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Bootstrap.setup(Bootstrap.java:227) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Bootstrap.init(Bootstrap.java:393) ~[elasticsearch-7.10.2.jar:7.10.2]
at org.elasticsearch.bootstrap.Elasticsearch.init(Elasticsearch.java:170) ~[elasticsearch-7.10.2.jar:7.10.2]

clausnielsen · April 14, 2021, 8:30am

We ALWAYS test our upgrades on our test instance before doing the upgrade in production. If we see any errors on the upgrade on the test instance - we rollback the upgrade with a “copy” of the production database. (we manually correct license and other configs). So if our test instance db is in an unexpected state - then our production instance which is running 8.5.x will also be in this state. Don’t you have some tooling for checking db “state” ?

I just wonder why SonarQube is the only product we upgrade and maintain ourselves which have these issues from time to time when upgrading. We almost never see this on other products.