sonar数据库project渣滓数据清理
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%';