DB Migration issues during Upgrade from 8.9.10 LTS to 9.9 LTS

Hi Community,

today I upgraded our test system from 8.9.10 LTS to 9.9 LTS.
Sonar is running on Windows Server 2016 and we’re using a MS SQL database (2016 I guess).
I’ve upgraded Java to OpenJDK 17.0.6 and also changed the mssql_auth.dll file.

I ran into the same problem as Migration error when upgrading to 9.7.1 - SonarQube - Sonar Community (sonarsource.com)

Fortunately I was able to solve it by manually dropping the constraint like mentioned in the thread, but in my opinion this should be patched as I don’t expect to have to do a manual operation on the DB when migrating from one LTS to the other. (Not to mention that I couldn’t have done it without the help here)

This might also refer to
SonarQube 9.9 LTS DB migration issue - SonarQube / 9.9 LTS Upgrade - Sonar Community (sonarsource.com)
[SONAR-18502] Database migration fails to drop indexes with long generated names - Jira (atlassian.net)

2023.02.24 10:08:44 INFO  web[][DbMigrations] #6006 'Drop 'user_managed' column from 'metrics' table'...
2023.02.24 10:08:44 ERROR web[][DbMigrations] #6006 'Drop 'user_managed' column from 'metrics' table': failure | time=126ms
2023.02.24 10:08:44 ERROR web[][DbMigrations] Executed DB migrations: failure | time=4442ms
2023.02.24 10:08:44 WARN  web[][o.s.c.a.AnnotationConfigApplicationContext] Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jdk.internal.loader.ClassLoaders$AppClassLoader@659e0bfd-org.sonar.server.platform.db.migration.AutoDbMigration': Initialization of bean failed; nested exception is org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #6006 'Drop 'user_managed' column from 'metrics' table' failed
2023.02.24 10:08:44 ERROR web[][o.s.s.p.PlatformImpl] Web server startup failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jdk.internal.loader.ClassLoaders$AppClassLoader@659e0bfd-org.sonar.server.platform.db.migration.AutoDbMigration': Initialization of bean failed; nested exception is org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #6006 'Drop 'user_managed' column from 'metrics' table' failed
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:628)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542)
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:955)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:918)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583)
	at org.sonar.core.platform.SpringComponentContainer.startComponents(SpringComponentContainer.java:187)
	at org.sonar.server.platform.platformlevel.PlatformLevel.start(PlatformLevel.java:80)
	at org.sonar.server.platform.PlatformImpl.start(PlatformImpl.java:196)
	at org.sonar.server.platform.PlatformImpl.startSafeModeContainer(PlatformImpl.java:188)
	at org.sonar.server.platform.PlatformImpl.doStart(PlatformImpl.java:90)
	at org.sonar.server.platform.web.PlatformServletContextListener.contextInitialized(PlatformServletContextListener.java:44)
	at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4764)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5222)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1393)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1383)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
	at java.base/java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:145)
	at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:916)
	at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:835)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1393)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1383)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
	at java.base/java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:145)
	at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:916)
	at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:265)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.StandardService.startInternal(StandardService.java:430)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:930)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.startup.Tomcat.start(Tomcat.java:486)
	at org.sonar.server.app.EmbeddedTomcat.start(EmbeddedTomcat.java:72)
	at org.sonar.server.app.WebServer.start(WebServer.java:55)
	at org.sonar.process.ProcessEntryPoint.launch(ProcessEntryPoint.java:97)
	at org.sonar.process.ProcessEntryPoint.launch(ProcessEntryPoint.java:81)
	at org.sonar.server.app.WebServer.main(WebServer.java:104)
Caused by: org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #6006 'Drop 'user_managed' column from 'metrics' 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:422)
	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:66)
	at org.sonar.server.platform.db.migration.AutoDbMigration.start(AutoDbMigration.java:40)
	at org.sonar.core.platform.StartableBeanPostProcessor.postProcessBeforeInitialization(StartableBeanPostProcessor.java:33)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:440)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1796)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620)
	... 43 common frames omitted
Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE metrics DROP CONSTRAINT DF__metrics__user_ma__1A14E395
	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.step.DropColumnChange.execute(DropColumnChange.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)
	... 52 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 'DF__metrics__user_ma__1A14E395' is not a constraint.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1676)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:907)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:802)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7627)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3916)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:775)
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:91)
	... 57 common frames omitted
2023.02.24 10:08:44 INFO  web[][c.z.h.HikariDataSource] HikariPool-1 - Shutdown initiated...
2023.02.24 10:08:44 INFO  web[][c.z.h.HikariDataSource] HikariPool-1 - Shutdown completed.
2023.02.24 10:08:44 INFO  web[][o.s.s.a.EmbeddedTomcat] HTTP connector enabled on port 8082
2023.02.24 10:08:44 INFO  web[][o.s.p.ProcessEntryPoint] Hard stopping process

Thanks to the community! :heart:

1 Like

Hey, we don’t fully understand this issue yet, and report like this will help us improve the product. thanks for reporting this :+1:

To help us pinpoint the issue, could you please share the exact query you ran to manually delete the constraint? Specifically, did you use dbo.metrics or directly metrics, as table name?

Hi Pierre,

I realized that SonarQube duplicated all the tables in the DB and added my account name in front of the table name, e.g. domain\account-name.metrics instead of dbo.metrics, so I took the query from the above mentioned post and changed it to

ALTER TABLE domain\account-name.metrics 
DROP CONSTRAINT DF__metrics__user_ma__084B3915;  
GO

(of course the constraint name differed a little bit)

The duplication might have happened when I started SonarQube via CLI when I was logged on with my personal account. I always do this to try out before upgrading. Usually SonarQube is running as a service with service account credentials. SQL authentication is configured as Integrated Security.
So maybe it was (partly) my own fault, but I still don’t think this should happen, right?

This is a part of the tables list I can see when connecting to the DB:
image

Finally, I’d like to point out that I’m not a DB expert and don’t usually tinker with the DB (it’s hosted by our IT :grimacing:)

Thanks for your support!
BR,
Julian

Looks like you indentified the issue, indeed. By starting SonarQube with your personal account, you created another schema. When SonarQube tries to delete a primary key, we look into the database metadata to identify the constraint name (as it contain a generated sufix). The issue in your case is that two schema are present, hence the two rows returned by the search in the metadata. Then, when SonarQube tries to delete the constraint, it’s not visible from the SQL user, and the query fail (which is good, otherwise it would delete a constraint on another schema…).

I tracked down this problem here.

Thanks for your contribution :+1: