Uploaded image for project: 'SonarQube'
  1. SonarQube
  2. SONAR-13613

DB migrations on upgrade fail due to orphan foreign keys

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 8.4
    • Fix Version/s: 8.4.1, 8.5
    • Component/s: Database
    • Labels:
    • Edition:
      Community
    • Production Notes:
      None

      Description

      In the upgrade to v8.4 there are many migrations affecting the primary key of a large number of tables. We don't always ensure that tables referencing these primary keys have valid values. Since foreign key constraints are not enforced at DB level, we might have orphan foreign keys, i.e., values that reference another table that, for unknown reasons, no longer exists. 

      When a new foreign key is generated, we should rows that have a null values. These are rows that couldn't be migrated because we couldn't find the referenced data with the foreign key. We already do it for many of the migrations but not all.

       

      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 #3647 'Make 'user_uuid' not-null for 'ORGANIZATION_MEMBERS'' 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 organization_members ALTER COLUMN user_uuid NVARCHAR (255) NOT 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.v84.users.fk.util.MakeUserUuidColumnNotNullable.execute(MakeUserUuidColumnNotNullable.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)
      	... 9 common frames omitted
      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'user_uuid', table 'SonarQube.dbo.organization_members'; column does not allow nulls. UPDATE fails.
      	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 

        Attachments

          Activity

            People

            Assignee:
            duarte.meneses Duarte Meneses
            Reporter:
            duarte.meneses Duarte Meneses
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Due:
              Created:
              Updated:
              Resolved: