LTS Oracle DB Migration not working from 7.9.4 LTS to 8.9.0 LTS

steveda86 · June 11, 2021, 12:04pm

We have upgraded our Sonarqube LTS Enterprise Version 7.9.4 to 8.9.0 on out Test Stage one week before and this was working without any Problems.

Today we tried to do the same Steps und our Productive Stage and the DB Migration Process failed
with the following Steps:

2021.06.11 13:37:46 INFO  web[][DbMigrations] Executing DB migrations...
2021.06.11 13:37:46 INFO  web[][DbMigrations] #3307 'Remove column 'resource_id' in 'group_roles''...
2021.06.11 13:37:46 ERROR web[][DbMigrations] #3307 'Remove column 'resource_id' in 'group_roles'': failure | time=45ms
2021.06.11 13:37:46 ERROR web[][DbMigrations] Executed DB migrations: failure | time=47ms
2021.06.11 13:37:46 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=276ms
2021.06.11 13:37:46 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 #3307 'Remove column 'resource_id' in 'group_roles'' 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 group_roles_resource
        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.v83.grouproles.DropResourceIdFromGroupRolesTable.execute(DropResourceIdFromGroupRolesTable.java:47)
        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.T4CTTIoer11.processError(T4CTTIoer11.java:509)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303)
        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-01418: specified index does not exist

I also checked the Oracle DB and i saw the the Indexes don’t exists on that Table. We tried to manually create those indexes but that does not work.
After that we removed the Removed the column ‘resource_id’ in ‘group_roles’ and restartet the DB migration process. The Problem stay’s the same.

Any Hints or Ideas what we could try next to solve this?

steveda86 · June 17, 2021, 9:45am

Hello
I have tried a few more steps. Deleted table manually and adjusted count in migration table. But it then continues from error to error.
It seems that there is something wrong inside the database. E.g. it searches for the trigger
PERM_TEMPLATES_GROUPS_IDT
but in the database the trigger is called
PERM_TEMPLATES_GROUPS_ID_TRG

I have also renamed these triggers, then the process continues until the next error.
Is there a script from Sonarsource or tool with which I can check the database that all tables, indexes and triggers are correct?

Belen_Pruvost · June 18, 2021, 10:50am

Hey @steveda86,

It looks like your DB is in an inconsistent state.

When you say that you deleted the table - are you talking about the group_roles table? If that’s the case, then that wouldn’t be correct, as migration 3307 is about dropping a column on the table (and not the whole table).

A workaround could be to follow this procedure to mimic this migration step yourself (manually removing the resource_id column in group_roles table). If you’d like to do this, please follow these steps in order:

Backup your 7.9.4 database
Run the migrations directly from SQ until this point (migration 3307) where it fails
Execute migration 3307 manually, omitting the step to drop the group_roles_resource index:
a. Drop the uniq_group_roles index
DROP INDEX uniq_group_roles
b. Remove the resource_id column
ALTER TABLE group_roles SET UNUSED (resource_id)
c. Create the new index uniq_group_roles
CREATE UNIQUE INDEX uniq_group_roles ON group_roles (organization_uuid, group_id, component_uuid, role)
If everything went well until this step, you can omit this migration step by running:
INSERT INTO schema_migrations ('version') VALUES('3307')
Commit the insert if you don’t have an auto-commit mechanism.
Restart SonarQube, the migration should resume to the next migration step

If after migration 3307 you encounter an issue with another migration, please share the logs with us and we’ll help you from there.

Cheers,
Belén

steveda86 · June 18, 2021, 11:14am

Hi @Belen_Pruvost

That was just an example of one of the steps who are failing.
The follwing steps are all failing

#3204 ‘Rename table ‘PROJECTS’ to ‘COMPONENTS’’ failed
#3307 ‘Remove column ‘resource_id’ in ‘group_roles’’ failed
#3310 ‘Remove column ‘resource_id’ in ‘user_roles’’ failed
#3311 ‘Remove column ‘id’ in ‘components’’ failed
#3492 ‘Drop primary key on ‘ID’ column of ‘PERM_TEMPLATES_GROUPS’ table’ failed
#3504 ‘Drop primary key on ‘ID’ column of ‘PERM_TEMPLATES_USERS’ table’ failed
#3573 ‘Drop primary key on ‘ID’ column of ‘PERMISSION_TEMPLATES’ table’ failed
#3679 ‘Drop column ‘templateId’ column for ‘RULES’’ failed

I tried to do this steps manually (drop coulm, drop prim-key and so on)
Sometime the Triggers had different names, and sometimes the Triggert didnt even exists in the Oracle Database.
Also on some steps i got the error that the specified index does not exist.

Sure i can try to fix all those problems manually.
But for me it looks like there are too many steps not working and it looks like our Database is broken. So is there a script or an SQL Command to check the consistency of this database?

Belen_Pruvost · June 18, 2021, 12:30pm

Hi @steveda86 ,

There is no script - but you could manually compare your current schema with the schema-sq.ddl file for 8.9.

steveda86 · June 21, 2021, 9:17am

hi, this make no sense! i’m on version 7.9.4. i need to fix this Database Schema before i can upgrade to 8.9.
it makes no sense to compare my 7.9 database with the 8.9 schema.

pierreguillot · June 21, 2021, 9:38am

Hi,

This file is not available for 7.9 (it have been introduced with 8.0).

When the migration is failing at the step 3204, it means your database is upgrading from 8.1 to 8.2. So at this specific step, you can compare your schema with SQ 8.1.