Problems with upgrade from 6.7.3 (build 38370) to 7.9.6 (build 41879

SonarQube
,
1

Hello,
We are currently running *Community Edition Version 6.7.3 (build 38370) and attempting to upgrade it to *Community Edition Version 7.9.6 (build 41879). Our database is Oracle. During the upgrade process, we encountered an error:

ERROR web[][DbMigrations] #2104 'Create ALM_APP_INSTALLS table': failure | time=46ms
ERROR web[][DbMigrations] Executed DB migrations: failure | time=21728759ms
ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=21728856ms
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 #2104 'Create ALM_APP_INSTALLS 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 java.base/java.lang.Iterable.forEach(Iterable.java:75) 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 CREATE UNIQUE INDEX alm_app_installs_owner ON alm_app_installs (alm_id, owner_id)
at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:97)
at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:77)
 at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:117)
 at org.sonar.server.platform.db.migration.version.v72.CreateAlmAppInstallsTable.execute(CreateAlmAppInstallsTable.java:87) 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-01450: maximum key length (6398) exceeded
 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:175)
at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175)
at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:82)
 ... 14 common frames omitted "

When attempting to resume the upgrade process after restarting the SonarQube service, the table creation appears to be successful but without the index. However, the upgrade process fails on the second table with the same error:

Create PROJECT_MAPPINGS table' failed.
CREATE UNIQUE INDEX key_type_kee ON project_mappings
Caused by: java.sql.SQLException: ORA-01450: maximum key length (6398) exceeded

When attempting to restart and continue the upgrade process, the second table is also created without the index. However, after some time, the upgrade process is completed successfully.

I guess you cannot say that those indices are not necessary and won’t cause any problems in the future :slight_smile:
So
Any opinion/suggestion/workaround I don’t know anything, would be highly appreciated

2

Hey there.

You may want to make sure you’re using the right JDBC driver for your Oracle DB version.

What specific Oracle DB version are you using?

3

Hello, thanks for your response. So yea I’ve seen that post .

Oracle Version is 19c

JDBC drivers I’ve tried and not worked:

1/ ojdbc11.jar
2/ ojdbc7-12.1.0.2.0.jar
3/ ojdbc8.jar “JDBC driver working with SonarQube version 6.7.3.”

4

Thanks.

Can you let me know how max_string_size is configurd for your Oracle DB? Is it STANDARD or EXTENDED?

5

Hello, Colin apologies for the late reply. It’s set to extended

6

Thanks.

As we (now) document in our requirements:

Only MAX_STRING_SIZE=STANDARD parameter is supported, not EXTENDED.

I think this wasn’t a concern when SonarQube 7.9 was released because the EXTENDED set

Ideally, you would be able to move your data to a databse where max_string_size is set to STANDARD – according to Oracle docs, it doesn’t seem that it’s possible to go the other way.

Although it might still be possible:

I suggest consulting with your Oracle DBAs.

You’re right. Having all required indicies, and a supported max_string_size are important as you upgrade across many SonarQube versions.

7

Thank you for your response.
According to the official Oracle documentation (source: https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321), the MAX_STRING_SIZE parameter controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL.

When set to STANDARD, it imposes the length limits of Oracle Database releases prior to Oracle Database 12c, such as 4000 bytes for VARCHAR2 and NVARCHAR2, and 2000 bytes for RAW. When set to EXTENDED, it allows the maximum limit of 32767 bytes, which was introduced in Oracle Database 12c.

Our issue pertains to the maximum key length (6398) exceeded, so it would be preferable for us to have the MAX_STRING_SIZE parameter set to 32767 bytes rather than 4000 bytes. However, please note that once the MAX_STRING_SIZE parameter is set to EXTENDED, it cannot be reverted to STANDARD.

Can we switch from Oracle to Postgres as our database? Additionally, is it feasible to perform database migration using the SonarQube Server?

8

Hey there.

It is possible to use the DB Copy Tool to move to another database (it might even work to move from one Oracle database with the EXTENDED keyset to STANDARD).

9

Hello, sorry for my late response.

Here is a solution that has worked for me:

  1. Create a new tablespace in Oracle with a 64 KB block size.
  2. Export the “before upgrade” SonarQube schema and import it into the new tablespace.
  3. Initiate the upgrade process from the beginning.

Following these steps, the upgrade process was successfully completed.

1 Like