sonar数据库project垃圾数据清理

sonar（3.5.1版本）扫描时，如果某个项目第一次扫描的时候出错导致扫描终止，但是在出错之前已经往数据库中写入了一些数据。之后再次运行扫描就会出错，提示信息如下：

Exception in thread "main" org.sonar.runner.RunnerException: javax.persistence.NonUniqueResultException: Expected single result, but got : [4175, 4176]
	at org.sonar.runner.Runner.delegateExecution(Runner.java:288)
	at org.sonar.runner.Runner.execute(Runner.java:151)
	at org.sonar.runner.Main.execute(Main.java:84)
	at org.sonar.runner.Main.main(Main.java:56)
Caused by: javax.persistence.NonUniqueResultException: Expected single result, but got : [4175, 4176]
	at org.sonar.jpa.session.JpaDatabaseSession.getSingleResult(JpaDatabaseSession.java:218)
	at org.sonar.batch.components.TimeMachineConfiguration.buildProjectSnapshot(TimeMachineConfiguration.java:79)
	at org.sonar.batch.components.TimeMachineConfiguration.initPastSnapshots(TimeMachineConfiguration.java:57)
	at org.sonar.batch.components.TimeMachineConfiguration.<init>(TimeMachineConfiguration.java:53)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
	at org.picocontainer.injectors.AbstractInjector.newInstance(AbstractInjector.java:147)
	at org.picocontainer.injectors.ConstructorInjector$1.run(ConstructorInjector.java:348)
	at org.picocontainer.injectors.AbstractInjector$ThreadLocalCyclicDependencyGuard.observe(AbstractInjector.java:272)
	at org.picocontainer.injectors.ConstructorInjector.getComponentInstance(ConstructorInjector.java:370)
	at org.picocontainer.injectors.AbstractInjectionFactory$LifecycleAdapter.getComponentInstance(AbstractInjectionFactory.java:56)
	at org.picocontainer.behaviors.AbstractBehavior.getComponentInstance(AbstractBehavior.java:64)
	at org.picocontainer.behaviors.Stored.getComponentInstance(Stored.java:91)
	at org.picocontainer.DefaultPicoContainer.getLocalInstance(DefaultPicoContainer.java:606)
	at org.picocontainer.DefaultPicoContainer.getComponents(DefaultPicoContainer.java:587)
	at org.sonar.api.platform.ComponentContainer.getComponentsByType(ComponentContainer.java:123)
	at org.sonar.api.batch.BatchExtensionDictionnary.completeBatchExtensions(BatchExtensionDictionnary.java:91)
	at org.sonar.api.batch.BatchExtensionDictionnary.getExtensions(BatchExtensionDictionnary.java:85)
	at org.sonar.api.batch.BatchExtensionDictionnary.getFilteredExtensions(BatchExtensionDictionnary.java:98)
	at org.sonar.api.batch.BatchExtensionDictionnary.select(BatchExtensionDictionnary.java:57)
	at org.sonar.api.batch.BatchExtensionDictionnary.selectMavenPluginHandlers(BatchExtensionDictionnary.java:65)
	at org.sonar.batch.scan.maven.MavenPluginsConfigurator.execute(MavenPluginsConfigurator.java:48)
	at org.sonar.batch.phases.Phases.execute(Phases.java:95)
	at org.sonar.batch.scan.ScanContainer.doStart(ScanContainer.java:147)
	at org.sonar.batch.bootstrap.Container.start(Container.java:72)
	at org.sonar.batch.scan.ScanTask.scan(ScanTask.java:65)
	at org.sonar.batch.scan.ScanTask.scanRecursively(ScanTask.java:56)
	at org.sonar.batch.scan.ScanTask.execute(ScanTask.java:49)
	at org.sonar.batch.bootstrap.TaskContainer.doStart(TaskContainer.java:188)
	at org.sonar.batch.bootstrap.Container.start(Container.java:72)
	at org.sonar.batch.bootstrap.TaskBootstrapContainer.executeTask(TaskBootstrapContainer.java:73)
	at org.sonar.batch.bootstrap.TaskBootstrapContainer.doStart(TaskBootstrapContainer.java:62)
	at org.sonar.batch.bootstrap.Container.start(Container.java:72)
	at org.sonar.batch.bootstrap.BootstrapContainer.doStart(BootstrapContainer.java:104)
	at org.sonar.batch.bootstrap.Container.start(Container.java:72)
	at org.sonar.batch.Batch.execute(Batch.java:106)
	at org.sonar.runner.internal.batch.Launcher.executeBatch(Launcher.java:69)
	at org.sonar.runner.internal.batch.Launcher.execute(Launcher.java:61)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:616)
	at org.sonar.runner.Runner.delegateExecution(Runner.java:285)
	... 3 more

 此时如果把sonar.projectKey属性改一下，也是可以正常运行代码扫描的，不过数据库里面就永久存储了一些垃圾数据，并且计划好的projectKey也要被迫更换，比较别扭。

还有一个方法就是直接修改sonar的数据库，从数据库层面直接清除掉第一次扫描失败时写入的垃圾数据。

假设第一次扫描时，sonar.projectKey属性值为D01:Project1:java，则使用以下sql语句将垃圾数据删除掉：

delete from action_plans_reviews where action_plan_id in (select id from action_plans where project_id in (select id from projects where kee like 'D01:Project1:java%'));
delete from action_plans where project_id in (select id from projects where kee like 'D01:Project1:java%');
delete from project_links where project_id in (select id from projects where kee like 'D01:Project1:java%');
delete from project_measures where project_id in (select id from projects where kee like 'D01:Project1:java%');
delete from review_comments where review_id in(select id from reviews  where project_id in (select id from projects where kee like 'D01:Project1:java%'));
delete from reviews where project_id in (select id from projects where kee like 'D01:Project1:java%');
delete from duplications_index where snapshot_id in(select id from snapshots where project_id in (select id from projects where kee like 'D01:Project1:java%'));
delete from events where snapshot_id in(select id from snapshots where project_id in (select id from projects where kee like 'D01:Project1:java%'));
delete from graphs where snapshot_id in(select id from snapshots where project_id in (select id from projects where kee like 'D01:Project1:java%'));
delete from measure_data where snapshot_id in(select id from snapshots where project_id in (select id from projects where kee like 'D01:Project1:java%'));
delete from project_measures where snapshot_id in(select id from snapshots where project_id in (select id from projects where kee like 'D01:Project1:java%'));
delete from rule_failures where snapshot_id in(select id from snapshots where project_id in (select id from projects where kee like 'D01:Project1:java%'));
delete from snapshot_sources where snapshot_id in(select id from snapshots where project_id in (select id from projects where kee like 'D01:Project1:java%'));
delete from snapshots where project_id in (select id from projects where kee like 'D01:Project1:java%');
delete from projects where kee like 'D01:Project1:java%';