Failing update from 6.7.7 to 7.9.1

Hello,

I am testing upgrade from 5.3 (MySQL) to the latest 7.9.1 (PostgreSQL) (community version).

  1. I switched at 5.3 from MySQL to PostgreSQL94 (with pgloader 3.6.1)
  2. Upgraded to 5.6.1
  3. Upgraded to 6.7.7

No error entries in logs.

  1. When starting 7.9.1 (according to download page the next LTS version since 6.7.7) it reports:
2019.11.18 15:57:59 INFO  web[][o.s.p.ProcessEntryPoint] Starting web
2019.11.18 15:58:00 INFO  web[][o.a.t.u.n.NioSelectorPool] Using a shared selector for servlet write/read
2019.11.18 15:58:01 INFO  web[][o.e.p.PluginsService] no modules loaded
2019.11.18 15:58:01 INFO  web[][o.e.p.PluginsService] loaded plugin [org.elasticsearch.join.ParentJoinPlugin]
2019.11.18 15:58:01 INFO  web[][o.e.p.PluginsService] loaded plugin [org.elasticsearch.percolator.PercolatorPlugin]
2019.11.18 15:58:01 INFO  web[][o.e.p.PluginsService] loaded plugin [org.elasticsearch.transport.Netty4Plugin]
2019.11.18 15:58:02 INFO  web[][o.s.s.e.EsClientProvider] Connected to local Elasticsearch: [127.0.0.1:9001]
2019.11.18 15:58:02 INFO  web[][o.s.s.p.LogServerVersion] SonarQube Server / 7.9.1.27448 / e0c45c97bb2e45d52629f3422ac91718020f6db9
2019.11.18 15:58:02 INFO  web[][o.sonar.db.Database] Create JDBC data source for jdbc:postgresql://localhost/sonar
2019.11.18 15:58:02 WARN  web[][o.s.d.d.PostgreSql] Upgrading PostgreSQL to 9.5 or greater is recommended for better performances
2019.11.18 15:58:03 INFO  web[][o.s.s.p.ServerFileSystemImpl] SonarQube home: /vol/sonar/sonar
2019.11.18 15:58:03 INFO  web[][o.s.s.u.SystemPasscodeImpl] System authentication by passcode is disabled
2019.11.18 15:58:04 INFO  web[][o.s.s.p.d.m.h.MigrationHistoryTableImpl] Creating table schema_migrations
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin Git / 1.8.0.1574 / aec3dc8f5228aabd218e1cd31ac6e6515a43715d
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin GitHub Authentication for SonarQube / 1.5.0.870 / 153f7c7af7a264adb0fcbe5fee87bdd140a6a3a1
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin JaCoCo / 1.0.2.475 / b79a4724f3a9af1051266b4f8ca0460977295ead
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin LDAP / 2.2.0.608 / 79dc3fa4393a29667673c70182f3016288b548b7
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SAML 2.0 Authentication for SonarQube / 1.1.0.181 / 16fcb5be2d3bcfee8920b29cd758d8b957a18acb
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarC# / 7.15.0.8572 / e0ad49e38a28a8fc333ba746fc998e48678f6a8b
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarCSS / 1.1.1.1010 / 365e21fd0cb9035669fc59f6fec7c8fd28a7303b
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarFlex / 2.5.1.1831 / a0c44437f6abb0feec76edd073f91fec64db2a6c
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarGo / 1.1.1.2000 / 40d55921c7a63b67386a053490d17b6723a46cd5
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarHTML / 3.1.0.1615 / 4181edb5eff5605bec82dc0aa15ecd70eaa5857f
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarJS / 5.2.1.7778 / 49f34eaeaad59868d4353d89b1fc5c02bbe51976
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarJava / 5.13.1.18282 / 568f8ed2349f48e250a9329895b9a870100dfbeb
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarKotlin / 1.5.0.315 / 4ff3a145a58f3f84f1b39846a205a129d742e993
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarPHP / 3.2.0.4868 / ec66bd5f8490677eb0ebae82aa17c2a5d9c0e5e7
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarPython / 1.14.1.3143 / eed7b315b6116fe462a19c771013bf3891c92a97
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarRuby / 1.5.0.315 / 4ff3a145a58f3f84f1b39846a205a129d742e993
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarScala / 1.5.0.315 / 4ff3a145a58f3f84f1b39846a205a129d742e993
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarTS / 1.9.0.3766 / 4a4080b78001a78d758d1d0fa0190fb9496b6f57
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarVB / 7.15.0.8572 / e0ad49e38a28a8fc333ba746fc998e48678f6a8b
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarXML / 2.0.1.2020 / c5b84004face582d56f110e24c29bf9c6a679e69
2019.11.18 15:58:04 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin Svn / 1.9.0.1295 / 942e075773975354e32691a60bfd968065703e04
2019.11.18 15:58:05 INFO  web[][o.s.s.p.d.m.c.PostgresCharsetHandler] Verify that database charset supports UTF8
2019.11.18 15:58:05 INFO  web[][o.s.s.p.d.m.AutoDbMigration] Automatically perform DB migration on fresh install
2019.11.18 15:58:05 INFO  web[][DbMigrations] Executing DB migrations...
2019.11.18 15:58:05 INFO  web[][DbMigrations] #1 'Create initial schema'...
2019.11.18 15:58:06 ERROR web[][DbMigrations] #1 'Create initial schema': failure | time=417ms
2019.11.18 15:58:06 ERROR web[][DbMigrations] Executed DB migrations: failure | time=418ms
2019.11.18 15:58:06 ERROR web[][o.s.s.p.Platform] Web server startup failed
org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #1 'Create initial schema' 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.AutoDbMigration.start(AutoDbMigration.java:121)
        at org.sonar.core.platform.StartableCloseableSafeLifecyleStrategy.start(StartableCloseableSafeLifecyleStrategy.java:40)
        at org.picocontainer.injectors.AbstractInjectionFactory$LifecycleAdapter.start(AbstractInjectionFactory.java:84)
        at org.picocontainer.behaviors.AbstractBehavior.start(AbstractBehavior.java:169)
        at org.picocontainer.behaviors.Stored$RealComponentLifecycle.start(Stored.java:132)
        at org.picocontainer.behaviors.Stored.start(Stored.java:110)
        at org.picocontainer.DefaultPicoContainer.potentiallyStartAdapter(DefaultPicoContainer.java:1016)
        at org.picocontainer.DefaultPicoContainer.startAdapters(DefaultPicoContainer.java:1009)
        at org.picocontainer.DefaultPicoContainer.start(DefaultPicoContainer.java:767)
        at org.sonar.core.platform.ComponentContainer.startComponents(ComponentContainer.java:135)
        at org.sonar.server.platform.platformlevel.PlatformLevel.start(PlatformLevel.java:90)
        at org.sonar.server.platform.Platform.start(Platform.java:211)
        at org.sonar.server.platform.Platform.startSafeModeContainer(Platform.java:203)
        at org.sonar.server.platform.Platform.doStart(Platform.java:104)
        at org.sonar.server.platform.Platform.doStart(Platform.java:95)
        at org.sonar.server.platform.web.PlatformServletContextListener.contextInitialized(PlatformServletContextListener.java:44)
        at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4817)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5283)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1423)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1413)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        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 TABLE properties (id INTEGER NOT NULL,prop_key VARCHAR (512) NULL,resource_id INTEGER NULL,text_value TEXT NULL,user_id INTEGER NULL)
        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.v56.CreateInitialSchema.createProperties(CreateInitialSchema.java:866)
        at org.sonar.server.platform.db.migration.version.v56.CreateInitialSchema.execute(CreateInitialSchema.java:82)
        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)
        ... 29 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: relation "properties" already exists
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
        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)
        ... 35 common frames omitted
2019.11.18 15:58:06 INFO  web[][o.s.s.a.EmbeddedTomcat] HTTP connector enabled on port 9000
2019.11.18 15:58:06 INFO  web[][o.s.p.ProcessEntryPoint] Hard stopping process

Any ideas how to move further?

Hm, I dropped table properties and started sonar again. Then it failed with:

ERROR: relation “active_dashboards” already exists

I dropped table active_dashboards as well and retried:

ERROR: relation “active_rule_parameters” already exists

I dropped table active_rule_parameters and retried:

ERROR: relation “active_dashboards” already exists.

WTF? It looks like it thinks this is a new installation with no data over and over again…

Oh sh*t! I forgot to specify schema name in the connection string in sonar.properties…In all previous versions it was not needed.

The upgrade is running now…

Well, now it fails with

2019.11.19 11:50:38 INFO  web[][DbMigrations] #1941 'Populate table live_measures': success | time=56333ms
2019.11.19 11:50:38 INFO  web[][DbMigrations] #1942 'Add live_measures.metric_id index'...
2019.11.19 11:50:46 INFO  web[][DbMigrations] #1942 'Add live_measures.metric_id index': success | time=8016ms
2019.11.19 11:50:46 INFO  web[][DbMigrations] #1943 'Drop temporary table LIVE_MEASURES_P'...
2019.11.19 11:50:46 INFO  web[][DbMigrations] #1943 'Drop temporary table LIVE_MEASURES_P': success | time=5ms
2019.11.19 11:50:46 INFO  web[][DbMigrations] #1944 'Drop temporary index on SNAPSHOTS.ISLAST'...
2019.11.19 11:50:46 INFO  web[][DbMigrations] #1944 'Drop temporary index on SNAPSHOTS.ISLAST': success | time=3ms
2019.11.19 11:50:46 INFO  web[][DbMigrations] #1945 'Delete file measures'...
2019.11.19 11:50:51 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 17385 files processed (289 items/sec)
2019.11.19 11:50:51 INFO  web[][DbMigrations] #1945 'Delete file measures': success | time=4674ms
2019.11.19 11:50:51 INFO  web[][DbMigrations] #1946 'Create ORG_QUALITY_GATES table'...
2019.11.19 11:50:51 INFO  web[][DbMigrations] #1946 'Create ORG_QUALITY_GATES table': success | time=16ms
2019.11.19 11:50:51 INFO  web[][DbMigrations] #1947 'Add ORGANIZATIONS.DEFAULT_QUALITY_GATE_UUID'...
2019.11.19 11:50:51 ERROR web[][DbMigrations] #1947 'Add ORGANIZATIONS.DEFAULT_QUALITY_GATE_UUID': failure | time=2ms
2019.11.19 11:50:51 ERROR web[][DbMigrations] Executed DB migrations: failure | time=69976ms
2019.11.19 11:50:51 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=70047ms
2019.11.19 11:50:51 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 #1947 'Add ORGANIZATIONS.DEFAULT_QUALITY_GATE_UUID' 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 ALTER TABLE organizations ADD COLUMN default_quality_gate_uuid VARCHAR (40) NULL
	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.version.v70.AddDefaultQualityGateUuidToOrganizations.execute(AddDefaultQualityGateUuidToOrganizations.java:37)
	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: org.postgresql.util.PSQLException: ERROR: relation "organizations" does not exist
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
	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)
	... 13 common frames omitted

Hm, I found out that some tables are in public schema and some in other schema. I moved all tables to public schema by:

DO LANGUAGE plpgsql
$body$
DECLARE
   l_old_schema NAME = 'other schema';
   l_new_schema NAME = 'public';
   l_sql TEXT;
BEGIN
  FOR l_sql IN
    SELECT 
        format('ALTER TABLE %I.%I SET SCHEMA %I', n.nspname, c.relname, l_new_schema)
      FROM pg_class c
      JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE
        n.nspname = l_old_schema AND
        c.relkind = 'r'
  LOOP
    RAISE NOTICE 'appliying %', l_sql;
    EXECUTE l_sql;
  END LOOP;
END;
$body$;

Updated connection string and upgrade finished :slight_smile:

1 Like