From 2c67a20e2f883c1394e2a7732b7d6aa29f0b985e Mon Sep 17 00:00:00 2001 From: Julien Lancelot Date: Fri, 23 Aug 2013 12:05:14 +0200 Subject: [PATCH] SONAR-4586 SonarQube purge on MSSQL can fail with the following error : "Prepared or callable statement has more than 2000 parameter markers" --- .../org/sonar/core/purge/PurgeCommands.java | 12 +++-- .../org/sonar/core/purge/PurgeMapper.java | 6 ++- .../org/sonar/core/purge/PurgeMapper.xml | 16 ++++-- .../sonar/core/purge/PurgeCommandsTest.java | 53 +++++++++++++++++++ 4 files changed, 78 insertions(+), 9 deletions(-) diff --git a/sonar-core/src/main/java/org/sonar/core/purge/PurgeCommands.java b/sonar-core/src/main/java/org/sonar/core/purge/PurgeCommands.java index e37b0fef704..38a07064e49 100644 --- a/sonar-core/src/main/java/org/sonar/core/purge/PurgeCommands.java +++ b/sonar-core/src/main/java/org/sonar/core/purge/PurgeCommands.java @@ -158,7 +158,8 @@ class PurgeCommands { deleteSnapshots(purgeMapper.selectSnapshotIds(query)); } - private void deleteSnapshots(final List snapshotIds) { + @VisibleForTesting + protected void deleteSnapshots(final List snapshotIds) { List> snapshotIdsPartition = Lists.partition(snapshotIds, MAX_SNAPSHOTS_PER_QUERY); @@ -205,7 +206,8 @@ class PurgeCommands { purgeSnapshots(purgeMapper.selectSnapshotIds(query)); } - private void purgeSnapshots(final List snapshotIds) { + @VisibleForTesting + protected void purgeSnapshots(final List snapshotIds) { // note that events are not deleted List> snapshotIdsPartition = Lists.partition(snapshotIds, MAX_SNAPSHOTS_PER_QUERY); @@ -289,7 +291,11 @@ class PurgeCommands { private void deleteSnapshotDependencies(final List> snapshotIdsPartition) { profiler.start("deleteSnapshotDependencies (dependencies)"); for (List partSnapshotIds : snapshotIdsPartition) { - purgeMapper.deleteSnapshotDependencies(partSnapshotIds); + // SONAR-4586 + // On MsSQL, the maximum number of parameters allowed in a query is 2000, so we have to execute 3 queries instead of one with 3 or inside + purgeMapper.deleteSnapshotDependenciesFromSnapshotId(partSnapshotIds); + purgeMapper.deleteSnapshotDependenciesToSnapshotId(partSnapshotIds); + purgeMapper.deleteSnapshotDependenciesProjectSnapshotId(partSnapshotIds); } session.commit(); profiler.stop(); diff --git a/sonar-core/src/main/java/org/sonar/core/purge/PurgeMapper.java b/sonar-core/src/main/java/org/sonar/core/purge/PurgeMapper.java index d7e3c2d034f..da7a6b20c96 100644 --- a/sonar-core/src/main/java/org/sonar/core/purge/PurgeMapper.java +++ b/sonar-core/src/main/java/org/sonar/core/purge/PurgeMapper.java @@ -36,7 +36,11 @@ public interface PurgeMapper { void deleteSnapshot(@Param("snapshotIds") List snapshotIds); - void deleteSnapshotDependencies(@Param("snapshotIds") List snapshotIds); + void deleteSnapshotDependenciesFromSnapshotId(@Param("snapshotIds") List snapshotIds); + + void deleteSnapshotDependenciesToSnapshotId(@Param("snapshotIds") List snapshotIds); + + void deleteSnapshotDependenciesProjectSnapshotId(@Param("snapshotIds") List snapshotIds); void deleteSnapshotDuplications(@Param("snapshotIds") List snapshotIds); diff --git a/sonar-core/src/main/resources/org/sonar/core/purge/PurgeMapper.xml b/sonar-core/src/main/resources/org/sonar/core/purge/PurgeMapper.xml index ae75f3c77d6..2501ff8bd0d 100644 --- a/sonar-core/src/main/resources/org/sonar/core/purge/PurgeMapper.xml +++ b/sonar-core/src/main/resources/org/sonar/core/purge/PurgeMapper.xml @@ -122,18 +122,24 @@ - + delete from dependencies where from_snapshot_id in #{snapshotId} - or to_snapshot_id in + + + + delete from dependencies where to_snapshot_id in - #{snapshotId} + #{snapshotId} - or project_snapshot_id in + + + + delete from dependencies where project_snapshot_id in - #{snapshotId} + #{snapshotId} diff --git a/sonar-core/src/test/java/org/sonar/core/purge/PurgeCommandsTest.java b/sonar-core/src/test/java/org/sonar/core/purge/PurgeCommandsTest.java index 1f4ec0a07ca..de1f6b5d4c6 100644 --- a/sonar-core/src/test/java/org/sonar/core/purge/PurgeCommandsTest.java +++ b/sonar-core/src/test/java/org/sonar/core/purge/PurgeCommandsTest.java @@ -26,6 +26,9 @@ import org.sonar.core.persistence.AbstractDaoTestCase; import org.sonar.core.persistence.MyBatis; import java.util.Arrays; +import java.util.List; + +import static com.google.common.collect.Lists.newArrayList; public class PurgeCommandsTest extends AbstractDaoTestCase { @@ -53,6 +56,20 @@ public class PurgeCommandsTest extends AbstractDaoTestCase { "snapshots", "project_measures", "measure_data", "snapshot_sources", "duplications_index", "events", "dependencies", "snapshot_data"); } + /** + * Test that SQL queries execution do not fail with a huge number of parameter + */ + @Test + public void should_not_fail_when_deleting_huge_number_of_snapshots() { + SqlSession session = getMyBatis().openSession(); + try { + new PurgeCommands(session, profiler).deleteSnapshots(getHugeNumberOfIds()); + } finally { + MyBatis.closeQuietly(session); + } + // The goal of this test is only to check that the query do no fail, not to check result + } + /** * Test that all related data is purged. */ @@ -83,6 +100,20 @@ public class PurgeCommandsTest extends AbstractDaoTestCase { checkTables("shouldDeleteWastedMeasuresWhenPurgingSnapshot", "project_measures"); } + /** + * Test that SQL queries execution do not fail with a huge number of parameter + */ + @Test + public void should_not_fail_when_purging_huge_number_of_snapshots() { + SqlSession session = getMyBatis().openSession(); + try { + new PurgeCommands(session, profiler).purgeSnapshots(getHugeNumberOfIds()); + } finally { + MyBatis.closeQuietly(session); + } + // The goal of this test is only to check that the query do no fail, not to check result + } + @Test public void shouldDeleteResource() { setupData("shouldDeleteResource"); @@ -95,4 +126,26 @@ public class PurgeCommandsTest extends AbstractDaoTestCase { assertEmptyTables("projects", "snapshots", "events", "issues", "issue_changes", "authors"); } + /** + * Test that SQL queries execution do not fail with a huge number of parameter + */ + @Test + public void should_not_fail_when_deleting_huge_number_of_resources() { + SqlSession session = getMyBatis().openSession(); + try { + new PurgeCommands(session, profiler).deleteResources(getHugeNumberOfIds()); + } finally { + MyBatis.closeQuietly(session); + } + // The goal of this test is only to check that the query do no fail, not to check result + } + + private List getHugeNumberOfIds(){ + List hugeNbOfSnapshotIds = newArrayList(); + for (long i=0; i<4500; i++) { + hugeNbOfSnapshotIds.add(i); + } + return hugeNbOfSnapshotIds; + } + } -- 2.39.5