DB Migration error from SQ 7.9.6 LTS to 8.9.8 LTS with PostgreSQL

ssanantonio · June 3, 2022, 8:49am

I am trying to update my version of SonarQube from version 7.9.6 to 8.9.8 but the process fail. Previously upgraded from version 6.7.7 to 7.9.6 successfully.

My DB is PostgreSQL 9.6.24

Error: (web.log)

2022.06.03 10:30:44 INFO  web[][o.s.s.p.d.m.DatabaseMigrationImpl] Starting DB Migration and container restart
2022.06.03 10:30:44 INFO  web[][DbMigrations] Executing DB migrations...
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3000 'Set Organizations#guarded column nullable'...
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3000 'Set Organizations#guarded column nullable': success | time=19ms
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3001 'Create ProjectQualityGates table'...
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3001 'Create ProjectQualityGates table': success | time=41ms
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3002 'Make index on DEPRECATED_RULE_KEYS.RULE_ID non unique'...
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3002 'Make index on DEPRECATED_RULE_KEYS.RULE_ID non unique': success | time=15ms
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3003 'Populate ProjectQualityGate table from Properties table'...
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3003 'Populate ProjectQualityGate table from Properties table': success | time=8ms
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3004 'Rename ANALYSIS_PROPERTIES.SNAPSHOT_UUID to ANALYSIS_UUID'...
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3004 'Rename ANALYSIS_PROPERTIES.SNAPSHOT_UUID to ANALYSIS_UUID': success | time=29ms
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3005 'Remove default quality gate property from Properties table'...
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3005 'Remove default quality gate property from Properties table': success | time=7ms
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3006 'Create NEW_CODE_PERIOD table'...
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3006 'Create NEW_CODE_PERIOD table': success | time=23ms
2022.06.03 10:30:44 INFO  web[][DbMigrations] #3007 'Populate NEW_CODE_PERIOD table'...
2022.06.03 10:30:44 ERROR web[][DbMigrations] #3007 'Populate NEW_CODE_PERIOD table': failure | time=488ms
2022.06.03 10:30:44 ERROR web[][DbMigrations] Executed DB migrations: failure | time=636ms
2022.06.03 10:30:44 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=697ms
2022.06.03 10:30:44 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 #3007 'Populate NEW_CODE_PERIOD 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: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:829)
Caused by: java.sql.BatchUpdateException: Batch entry 157 INSERT INTO new_code_periods(uuid, project_uuid,branch_uuid,type,value,updated_at,created_at) VALUES ('AYEosDJuN11aySI_Kl4O', 'AWRlLBJmdIYZWWQJpJXk', 'AWRlLBJmdIYZWWQJpJXk', 'SPECIFIC_ANALYSIS', 'AWhAt46KfBFcTyyi7RZ0', 1654245044697, 1654245044697) was aborted: ERROR: duplicate key value violates unique co
nstraint "uniq_new_code_periods"
  Detail: Key (project_uuid, branch_uuid)=(AWRlLBJmdIYZWWQJpJXk, AWRlLBJmdIYZWWQJpJXk) already exists.  Call getNextException to see other errors in the batch.
        at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:559)
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:887)
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910)
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1638)
        at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)
        at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)
        at org.sonar.server.platform.db.migration.step.UpsertImpl.addBatch(UpsertImpl.java:56)
        at org.sonar.server.platform.db.migration.version.v80.PopulateNewCodePeriodTable.insert(PopulateNewCodePeriodTable.java:180)
        at org.sonar.server.platform.db.migration.version.v80.PopulateNewCodePeriodTable.populateWithSettings(PopulateNewCodePeriodTable.java:145)
        at org.sonar.server.platform.db.migration.version.v80.PopulateNewCodePeriodTable.populateFromSettings(PopulateNewCodePeriodTable.java:109)
        at org.sonar.server.platform.db.migration.version.v80.PopulateNewCodePeriodTable.execute(PopulateNewCodePeriodTable.java:60)
        at org.sonar.server.platform.db.migration.step.DataChange.execute(DataChange.java:44)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
        ... 9 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uniq_new_code_periods"
  Detail: Key (project_uuid, branch_uuid)=(AWRlLBJmdIYZWWQJpJXk, AWRlLBJmdIYZWWQJpJXk) already exists.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
        ... 22 common frames omitted
2022.06.03 10:36:48 ERROR web[][o.s.s.w.WebServiceEngine] Fail to process request http://sonar-pre.gva.es/api/system/db_migration_status
java.lang.IllegalStateException: Failed to read content of table schema_migrations
        at org.sonar.server.platform.db.migration.history.MigrationHistoryImpl.getLastMigrationNumber(MigrationHistoryImpl.java:75)
        at org.sonar.server.platform.db.migration.version.DatabaseVersion.getVersion(DatabaseVersion.java:51)
        at org.sonar.server.platform.ws.DbMigrationStatusAction.handle(DbMigrationStatusAction.java:68)
        at org.sonar.server.ws.WebServiceEngine.execute(WebServiceEngine.java:110)
        at org.sonar.server.platform.web.WebServiceFilter.doFilter(WebServiceFilter.java:84)
        at org.sonar.server.platform.web.MasterServletFilter$GodFilterChain.doFilter(MasterServletFilter.java:139)
        at org.sonar.server.platform.web.MasterServletFilter.doFilter(MasterServletFilter.java:108)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:194)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:167)
        at org.sonar.server.platform.web.UserSessionFilter.doFilter(UserSessionFilter.java:81)
        at org.sonar.server.platform.web.UserSessionFilter.doFilter(UserSessionFilter.java:68)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:194)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:167)
        at org.sonar.server.platform.web.CacheControlFilter.doFilter(CacheControlFilter.java:76)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:194)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:167)
        at org.sonar.server.platform.web.SecurityServletFilter.doHttpFilter(SecurityServletFilter.java:76)
        at org.sonar.server.platform.web.SecurityServletFilter.doFilter(SecurityServletFilter.java:48)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:194)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:167)
        at org.sonar.server.platform.web.RedirectFilter.doFilter(RedirectFilter.java:58)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:194)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:167)
        at org.sonar.server.platform.web.RequestIdFilter.doFilter(RequestIdFilter.java:61)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:194)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:167)
        at org.sonar.server.platform.web.RootFilter.doFilter(RootFilter.java:62)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:194)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:167)
        at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:109)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:194)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:167)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:544)

Do you know what’s the problem?

Thank you so much

King regards

ssanantonio · June 6, 2022, 6:20am

@ganncamp What solution do I apply here?

Colin · June 7, 2022, 9:06am

Hey there.

Please don’t tag individuals who are not engaged in a thread.

Since you’re encountering an error in your upgrade, the best thing to do first is to restore from a backup of your database so that you are unblocked, and can copy the database elsewhere to further test your migration before you try again.

Specifically, it looks like SonarQube is trying to place the same key/value pair (which must be unique) multiple times in the new table.

Since both the project_uuid and branch_uuid are the same, we can tell this relates to the main branch of some project.

This is the specific database migration being run. The database migration should prevent duplicate key/value pairs from slipping through, but something didn’t work here.

I would suggest running the following database query on a database that has run into the issue (such as this one, before restoring from a backup)

SELECT * FROM new_code_periods WHERE project_uuid='AWRlLBJmdIYZWWQJpJXk' AND branch_uuid='AWRlLBJmdIYZWWQJpJXk'

This will help us understand what the duplicate value is (and where it might have come from)

ssanantonio · June 8, 2022, 6:20am

Hi Colin,

Thank you very much for the reply.

My DBA companion has executed the query that you have told me and surprisingly it has not shown any rows. Is it possible that when an update step fails, only that step is rolled back?

On the other hand, I’m going to look for the project to which that uuid belongs in case it has something different from the rest, but my more than 800 projects in SonarQube have a leak period by date assigned to them that disappears in the new version, I don’t know if it’s due to having that assigned leak period may be the cause of the error.

Regards.

Colin · June 8, 2022, 9:05am

Hey there.

There will be no rollback.

What this tells us is that the duplicate values are being found in the second part of this database migration. This is the SQL query being used to grab the values that are inserted.

SELECT projs.uuid, projs.main_branch_project_uuid, props.text_value
        FROM properties props INNER JOIN projects projs ON props.resource_id = projs.id
        WHERE props.prop_key = 'sonar.leak.period' AND props.resource_id IS NOT NULL

Maybe you can upload the results of this query, which should not contain any sensitive data.

ssanantonio · June 8, 2022, 1:38pm

I was finally able to continue the migration to SonarQube 8.9.8.

In one of the projects (AWRlLBJmdIYZWWQJpJXk) it had a date leak period assigned and its default value was also date.

The latter surprises me because by default for all projects I have a new period of “previous version”.I don’t know how that got there, but by resetting the value I was able to continue the migration.

Colin · June 8, 2022, 1:56pm

Thanks for the follow-up @ssanantonio

I tried to reproduce this (setting the global leak period to a date in v7.9 LTS, setting a project’s leak period to the same date used as default, and triggering the upgrade). I couldn’t reproduce it.

In more than a year this is the first report of such a migration error – we can probably call it a fluke. Glad you were able to finish your upgrade.