Upgrade from 7.9.6 LTS to 8.9.0 LTS failed

App-Infra-LHT · June 1, 2021, 9:15am

Must-share information (formatted with Markdown):

SonarQube 7.9.6 LTS, DB Oracle 12 with ojdbc8-12.2.0.1.jar
Upgrade to 8.9.0.43852

The Update process from 7.9.6 LTS to 8.9.0 LTS failed.
Stepps we did:

Unzip sonarqube-8.9.0.43852.zip to ~/sonar/sonarqube-8.9.0.43852
Set properties in sonar.properties (we don’t have any special setting in wrapper.conf)
Stop the old instance
Start the new instance
Run https://URL.TO.SONAR/sonar/setup and click the Update-button
After some minutes an error message appears “Database is unavailbale”

Our DB admins confirmed, that that there were activities on the database.
The web.log shows the following error:

...
2021.05.31 14:34:02 INFO  web[][DbMigrations] #3491 'Make 'uuid' column not nullable for 'PERM_TEMPLATES_GROUPS''...
2021.05.31 14:34:02 INFO  web[][DbMigrations] #3491 'Make 'uuid' column not nullable for 'PERM_TEMPLATES_GROUPS'': success | time=14ms
2021.05.31 14:34:02 INFO  web[][DbMigrations] #3492 'Drop primary key on 'ID' column of 'PERM_TEMPLATES_GROUPS' table'...
2021.05.31 14:34:02 ERROR web[][DbMigrations] #3492 'Drop primary key on 'ID' column of 'PERM_TEMPLATES_GROUPS' table': failure | time=6ms
2021.05.31 14:34:02 ERROR web[][DbMigrations] Executed DB migrations: failure | time=74399ms
2021.05.31 14:34:02 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=74481ms
2021.05.31 14:34:02 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 #3492 'Drop primary key on 'ID' column of 'PERM_TEMPLATES_GROUPS' table' 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 TRIGGER perm_templates_groups_IDT
        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.v84.common.DropPrimaryKeyOnIdColumn.execute(DropPrimaryKeyOnIdColumn.java:39)
        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.SQLSyntaxErrorException: ORA-04080: Trigger 'PERM_TEMPLATES_GROUPS_IDT' ist nicht vorhanden

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:896)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1737)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1692)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:300)
        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
Caused by: oracle.jdbc.OracleDatabaseException: ORA-04080: Trigger 'PERM_TEMPLATES_GROUPS_IDT' ist nicht vorhanden

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
        ... 29 common frames omitted
2021.05.31 14:34:36 INFO  web[][o.s.p.ProcessEntryPoint] Gracefully stopping process
...

A previously update of our test environment was successfull.

Do you have any idea how we could update our sonar instance?

Update: In the meanwhile I discovered that the trigger to be deleted in PERM_TEMPLATES_GROUPS seems to be misspelt.
In the exception it is named: perm_templates_groups_IDT
But in our database the trigger is named: perm_templates_groups_id_trg
The same appears for table PERM_TEMPLATES_USERS

But I still have no idea, how to fix this.

Daniel_Meppiel · June 2, 2021, 7:22am

Hi @App-Infra-LHT ,

You seem to be experiencing the same issue as this user here: SonarQube Update 8.4 failed PERM_TEMPLATES_GROUPS

The approach to resolution would be similar, with an extra step:

run the database migration until it fails at step 3492
Manually delete the 'ID' is not null constraint
Manually delete the index in the ID column
Manually delete the existing perm_templates_groups_id_trg trigger
Execute this insert query insert into schema_migrations (version) values ('3492')
Resume the migration (restart SonarQube)

The migration should continue with the next steps.

App-Infra-LHT · June 2, 2021, 1:25pm

Hi @Daniel_Meppiel!

Thanks for your replay.
After investigating the database I found another, more simple solution.

After stopping the old SonarQube instance, I logged in into the database and there I found three (3) triggers, which names did not match to the name in the error log above.

They were named “PERM_TEMPLATES_…ID_TRG" instead of "PERM_TEMPLATES…_IDT”.
I renamed theese triggers to the expected ones and then the update process runs fine.

Best regards
Ralph@LHT

system · June 9, 2021, 1:26pm

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