DBMigration failed 7.9.1 -> 8.5.1

emiltorp · December 3, 2020, 5:00pm

So I tried to upgrade our version of SonarQube from version 7.9.1 to version 8.5.1, and got stuck on the first migration:

2020.12.03 17:51:19 INFO  web[][DbMigrations] Executing DB migrations...
2020.12.03 17:51:19 INFO  web[][DbMigrations] #3000 'Set Organizations#guarded column nullable'...
2020.12.03 17:51:19 ERROR web[][DbMigrations] #3000 'Set Organizations#guarded column nullable': failure | time=50ms
2020.12.03 17:51:19 ERROR web[][DbMigrations] Executed DB migrations: failure | time=52ms
2020.12.03 17:51:19 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=288ms
2020.12.03 17:51:19 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 #3000 'Set Organizations#guarded column nullable' 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 organizations ALTER COLUMN guarded BIT NULL
	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.v80.MakeOrganizationsGuardedNullable.execute(MakeOrganizationsGuardedNullable.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: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find the object "organizations" because it does not exist or you do not have permissions.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:744)
	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

Setup:
Windows Server 2019
SQL Server 2019
Running as Windows Service.

I’m happy to provide any more information if it is needed.

jacek.poreda · December 4, 2020, 2:40pm

Hello @emiltorp,

Could you double check if organizations table exist in the database you are using?

Also this is the very first migration of 8.X version, so it is very likely that database you have configured via jdbc properties is incorrect.

emiltorp · December 4, 2020, 3:12pm

Yup, that table exists. It has only one entry (Default Organization) and the guarded column is set to 1

And below is my jdbc properties, which looks fine according to the examples in the provided sonar.properties. That is all jdbc properties I have enabled.

sonar.jdbc.username=****
sonar.jdbc.password=****
sonar.jdbc.url=jdbc:sqlserver://localhost;databaseName=sonarqube

But it is possible that the database is not in “correct state”. We started with version 5.* a few years back and have migrated a couple of times since then.

jacek.poreda · December 4, 2020, 3:18pm

I’m unsure if it is possible (I will check that, but it will take some time).

In order to achieve such state you would have to skip version: 6.2 of SQ, do you mind sharing schema_migrations content? The organization table has been created in migration number: 1400.

What was your usual path of upgrade?

emiltorp · December 4, 2020, 3:30pm

Unfortunately I don’t remember the exact version. But I think it was like 5.6 -> 6.5 -> 6.7 -> 7.0 -> 7.1 -> … 7.9.1 (I think we did every 7 version)

Here are my dbo.schema_migrations, and 1400 is in there. So it is created.

Also, this was the first time I saw a schema migration fail. And I have done all the upgrades since 5.6

jacek.poreda · December 4, 2020, 3:49pm

Okay, so it seems your data in DB is correct.

Could you check if your db user which is used in jdbc connection has a proper permissions to alter that table?

emiltorp · December 4, 2020, 4:30pm

Of course that was the issue. I migrated a backup from an old server to this new server where we will run SonarQube from now on. Put db_datareader and db_datawriter and it started on 7.9.1 just fine so I didn’t think of it.

Now I added db_owner to that account and the migrations went just fine (took about 20 minutes). So this is a case of me not reading the manual completely…

system · December 11, 2020, 4:30pm

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