SONAR-13613 DB migrations on upgrade fail due to orphan foreign keys

14 files changed, 100 insertions, 0 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/activerules/PopulateActiveRulesRuleUuidColumn.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/activerules/PopulateActiveRulesRuleUuidColumn.java
index 492c4b8861d..8f2b3a06565 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/activerules/PopulateActiveRulesRuleUuidColumn.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/activerules/PopulateActiveRulesRuleUuidColumn.java
@@ -45,5 +45,15 @@ public class PopulateActiveRulesRuleUuidColumn extends DataChange {
       update.setLong(2, row.getLong(1));
       return true;
     });
+
+    massUpdate = context.prepareMassUpdate();
+
+    massUpdate.select("select uuid from active_rules where rule_uuid is null");
+    massUpdate.update("delete from active_rules where uuid = ?");
+
+    massUpdate.execute((row, update) -> {
+      update.setString(1, row.getString(1));
+      return true;
+    });
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/deprecatedrulekeys/PopulateDeprecatedRuleKeysRuleUuidColumn.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/deprecatedrulekeys/PopulateDeprecatedRuleKeysRuleUuidColumn.java
index 8e35f83f766..506ec9735ed 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/deprecatedrulekeys/PopulateDeprecatedRuleKeysRuleUuidColumn.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/deprecatedrulekeys/PopulateDeprecatedRuleKeysRuleUuidColumn.java
@@ -45,5 +45,15 @@ public class PopulateDeprecatedRuleKeysRuleUuidColumn extends DataChange {
       update.setLong(2, row.getLong(1));
       return true;
     });
+
+    massUpdate = context.prepareMassUpdate();
+
+    massUpdate.select("select uuid from deprecated_rule_keys where rule_uuid is null");
+    massUpdate.update("delete from deprecated_rule_keys where uuid = ?");
+
+    massUpdate.execute((row, update) -> {
+      update.setString(1, row.getString(1));
+      return true;
+    });
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/rulesmetadata/PopulateRulesMetadataRuleUuidColumn.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/rulesmetadata/PopulateRulesMetadataRuleUuidColumn.java
index 6021a6975b4..4a9a26fa4a8 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/rulesmetadata/PopulateRulesMetadataRuleUuidColumn.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/rules/rulesmetadata/PopulateRulesMetadataRuleUuidColumn.java
@@ -45,5 +45,16 @@ public class PopulateRulesMetadataRuleUuidColumn extends DataChange {
       update.setLong(2, row.getLong(1));
       return true;
     });
+
+    massUpdate = context.prepareMassUpdate();
+
+    massUpdate.select("select rule_id, organization_uuid from rules_metadata where rule_uuid is null");
+    massUpdate.update("delete from rules_metadata where rule_id = ? and organization_uuid = ?");
+
+    massUpdate.execute((row, update) -> {
+      update.setLong(1, row.getLong(1));
+      update.setString(2, row.getString(2));
+      return true;
+    });
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/groupsusers/PopulateGroupsUsersUserUuid.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/groupsusers/PopulateGroupsUsersUserUuid.java
index 48ef8e8f113..02c1a80abfc 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/groupsusers/PopulateGroupsUsersUserUuid.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/groupsusers/PopulateGroupsUsersUserUuid.java
@@ -49,5 +49,17 @@ public class PopulateGroupsUsersUserUuid extends DataChange {
       update.setLong(3, userId);
       return true;
     });
+
+    massUpdate = context.prepareMassUpdate();
+
+    massUpdate.select("select group_uuid, user_id from groups_users where user_uuid is null");
+    massUpdate.update("delete from groups_users where group_uuid = ? and user_id = ?");
+
+    massUpdate.execute((row, update) -> {
+      update.setString(1, row.getString(1));
+      update.setLong(2, row.getLong(2));
+
+      return true;
+    });
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/organizationmembers/PopulateOrganizationMembersUserUuid.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/organizationmembers/PopulateOrganizationMembersUserUuid.java
index 536693a24de..aec75a73ca3 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/organizationmembers/PopulateOrganizationMembersUserUuid.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/organizationmembers/PopulateOrganizationMembersUserUuid.java
@@ -50,5 +50,17 @@ public class PopulateOrganizationMembersUserUuid extends DataChange {
       update.setLong(3, userId);
       return true;
     });
+
+    massUpdate = context.prepareMassUpdate();
+
+    massUpdate.select("select organization_uuid, user_id from organization_members where user_uuid is null");
+    massUpdate.update("delete from organization_members where organization_uuid = ? and user_id = ?");
+
+    massUpdate.execute((row, update) -> {
+      update.setString(1, row.getString(1));
+      update.setLong(2, row.getLong(2));
+
+      return true;
+    });
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/permtemplatesusers/PopulatePermTemplatesUsersUserUuid.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/permtemplatesusers/PopulatePermTemplatesUsersUserUuid.java
index 79a02a42d56..9c383611c81 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/permtemplatesusers/PopulatePermTemplatesUsersUserUuid.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/permtemplatesusers/PopulatePermTemplatesUsersUserUuid.java
@@ -48,5 +48,15 @@ public class PopulatePermTemplatesUsersUserUuid extends DataChange {
       update.setString(2, permTemplatesUuid);
       return true;
     });
+
+    massUpdate = context.prepareMassUpdate();
+
+    massUpdate.select("select uuid from perm_templates_users where user_uuid is null");
+    massUpdate.update("delete from perm_templates_users where uuid = ?");
+
+    massUpdate.execute((row, update) -> {
+      update.setString(1, row.getString(1));
+      return true;
+    });
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/qprofileeditusers/PopulateQProfileEditUsersUserUuid.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/qprofileeditusers/PopulateQProfileEditUsersUserUuid.java
index c45e42db93a..bb40326d801 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/qprofileeditusers/PopulateQProfileEditUsersUserUuid.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/users/fk/qprofileeditusers/PopulateQProfileEditUsersUserUuid.java
@@ -48,5 +48,15 @@ public class PopulateQProfileEditUsersUserUuid extends DataChange {
       update.setString(2, permTemplatesUuid);
       return true;
     });
+
+    massUpdate = context.prepareMassUpdate();
+
+    massUpdate.select("select uuid from qprofile_edit_users where user_uuid is null");
+    massUpdate.update("delete from qprofile_edit_users where uuid = ?");
+
+    massUpdate.execute((row, update) -> {
+      update.setString(1, row.getString(1));
+      return true;
+    });
   }
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/activerules/PopulateActiveRulesRuleUuidColumnTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/activerules/PopulateActiveRulesRuleUuidColumnTest.java
index 7591b9e6cc8..4ba7bb81754 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/activerules/PopulateActiveRulesRuleUuidColumnTest.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/activerules/PopulateActiveRulesRuleUuidColumnTest.java
@@ -46,6 +46,8 @@ public class PopulateActiveRulesRuleUuidColumnTest {
     insertActiveRule("uuid-ar-1", 1L, "uuid-profile-1");
     insertActiveRule("uuid-ar-2", 1L, "uuid-profile-2");
     insertActiveRule("uuid-ar-3", 2L, "uuid-profile-1");
+    // orphan FK
+    insertActiveRule("uuid-ar-4", 10L, "uuid-profile-1");
   }
 
   @Test
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/deprecatedrulekeys/PopulateDeprecatedRuleKeysRuleUuidColumnTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/deprecatedrulekeys/PopulateDeprecatedRuleKeysRuleUuidColumnTest.java
index 7f9c48b61c0..edd75678eb7 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/deprecatedrulekeys/PopulateDeprecatedRuleKeysRuleUuidColumnTest.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/deprecatedrulekeys/PopulateDeprecatedRuleKeysRuleUuidColumnTest.java
@@ -47,6 +47,8 @@ public class PopulateDeprecatedRuleKeysRuleUuidColumnTest {
     insertDeprecatedRuleKeyRow("uuid-drk-1", 1L);
     insertDeprecatedRuleKeyRow("uuid-drk-2", 1L);
     insertDeprecatedRuleKeyRow("uuid-drk-3", 2L);
+    // orphan FK
+    insertDeprecatedRuleKeyRow("uuid-drk-4", 10L);
   }
 
   @Test
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/rulesmetadata/PopulateRulesMetadataRuleUuidColumnTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/rulesmetadata/PopulateRulesMetadataRuleUuidColumnTest.java
index 22f0b0c47c6..c5ffc76e8e8 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/rulesmetadata/PopulateRulesMetadataRuleUuidColumnTest.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/rules/rulesmetadata/PopulateRulesMetadataRuleUuidColumnTest.java
@@ -47,6 +47,8 @@ public class PopulateRulesMetadataRuleUuidColumnTest {
     insertRuleMetadata(1L, "org-1");
     insertRuleMetadata(1L, "org-2");
     insertRuleMetadata(2L, "org-1");
+    // orphan FK
+    insertRuleMetadata(10L, "org-1");
   }
 
   @Test
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/groupsusers/PopulateGroupsUsersUserUuidTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/groupsusers/PopulateGroupsUsersUserUuidTest.java
index aa1217b9387..7e7787bdc28 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/groupsusers/PopulateGroupsUsersUserUuidTest.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/groupsusers/PopulateGroupsUsersUserUuidTest.java
@@ -62,9 +62,13 @@ public class PopulateGroupsUsersUserUuidTest {
     insertGroupUser(userId_3, groupUuid_3);
     String groupUuid_4 = Uuids.createFast();
     insertGroupUser(userId_1, groupUuid_4);
+    // orphan FK
+    String groupUuid_5 = Uuids.createFast();
+    insertGroupUser(100L, groupUuid_5);
 
     underTest.execute();
 
+    assertThat(db.countRowsOfTable("groups_users")).isEqualTo(4);
     assertThatGroupsUserUserUuidIsEqualTo(userId_1, groupUuid_1, userUuid_1);
     assertThatGroupsUserUserUuidIsEqualTo(userId_2, groupUuid_2, userUuid_2);
     assertThatGroupsUserUserUuidIsEqualTo(userId_3, groupUuid_3, userUuid_3);
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/organizationmembers/PopulateOrganizationMembersUserUuidTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/organizationmembers/PopulateOrganizationMembersUserUuidTest.java
index 554a985b455..913ae086fc2 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/organizationmembers/PopulateOrganizationMembersUserUuidTest.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/organizationmembers/PopulateOrganizationMembersUserUuidTest.java
@@ -64,8 +64,13 @@ public class PopulateOrganizationMembersUserUuidTest {
     String organizationUuid_5 = Uuids.createFast();
     insertOrganizationMember(userId_1, organizationUuid_5);
 
+    // orphan FK
+    String organizationUuid_6 = Uuids.createFast();
+    insertOrganizationMember(100L, organizationUuid_6);
+
     underTest.execute();
 
+    assertThat(db.countRowsOfTable("organization_members")).isEqualTo(5);
     assertThatQProfileChangeRulesProfileUuidIsEqualTo(userId_1, organizationUuid_1, userUuid_1);
     assertThatQProfileChangeRulesProfileUuidIsEqualTo(userId_2, organizationUuid_2, userUuid_2);
     assertThatQProfileChangeRulesProfileUuidIsEqualTo(userId_3, organizationUuid_3, userUuid_3);
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/permtemplatesusers/PopulatePermTemplatesUsersUserUuidTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/permtemplatesusers/PopulatePermTemplatesUsersUserUuidTest.java
index 9e167a77981..16cf78b061c 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/permtemplatesusers/PopulatePermTemplatesUsersUserUuidTest.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/permtemplatesusers/PopulatePermTemplatesUsersUserUuidTest.java
@@ -64,8 +64,13 @@ public class PopulatePermTemplatesUsersUserUuidTest {
     String permTemplatesUserUuid_5 = Uuids.createFast();
     insertPermTemplatesUser(permTemplatesUserUuid_5, userId_1);
 
+    // orphan FK
+    String permTemplatesUserUuid_6 = Uuids.createFast();
+    insertPermTemplatesUser(permTemplatesUserUuid_6, 100L);
+
     underTest.execute();
 
+    assertThat(db.countRowsOfTable("perm_templates_users")).isEqualTo(5);
     assertThatPermTemplatesUsersUserUuidIsEqualTo(permTemplatesUserUuid_1, userUuid_1);
     assertThatPermTemplatesUsersUserUuidIsEqualTo(permTemplatesUserUuid_2, userUuid_2);
     assertThatPermTemplatesUsersUserUuidIsEqualTo(permTemplatesUserUuid_3, userUuid_3);
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/qprofileeditusers/PopulateQProfileEditUsersUserUuidTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/qprofileeditusers/PopulateQProfileEditUsersUserUuidTest.java
index 05a194a1b64..1a1863829dd 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/qprofileeditusers/PopulateQProfileEditUsersUserUuidTest.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/users/fk/qprofileeditusers/PopulateQProfileEditUsersUserUuidTest.java
@@ -64,8 +64,13 @@ public class PopulateQProfileEditUsersUserUuidTest {
     String qprofileEditUserUuid_5 = Uuids.createFast();
     insertQProfileEditUser(qprofileEditUserUuid_5, userId_1);
 
+    // orphan FK
+    String qprofileEditUserUuid_6 = Uuids.createFast();
+    insertQProfileEditUser(qprofileEditUserUuid_6, 100L);
+
     underTest.execute();
 
+    assertThat(db.countRowsOfTable("qprofile_edit_users")).isEqualTo(5);
     assertThatQProfileEditUserUserUuidIsEqualTo(qprofileEditUserUuid_1, userUuid_1);
     assertThatQProfileEditUserUserUuidIsEqualTo(qprofileEditUserUuid_2, userUuid_2);
     assertThatQProfileEditUserUserUuidIsEqualTo(qprofileEditUserUuid_3, userUuid_3);