Analysis fails with "unique constraint (SONAR.SYS_C006850) violated" after upgrade to 7.9

SonarQube Server / Community Build
,
1

Hello,

I have upgrade from SonarQube 6.7.5 over 6.7.7 to 7.9.5. Small issues in the database migration could be solved by creating missing indices to be dropped in the migration. Now, 7.9.5 is running (no additional plugins), but any analysis fails to be added to the database with the following exception:

    org.apache.ibatis.exceptions.PersistenceException: 
    ### Error committing transaction.  Cause: org.apache.ibatis.executor.BatchExecutorException: org.sonar.db.measure.MeasureMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: ORA-00001: unique constraint (SONAR.SYS_C006850) violated
    
    ### Cause: org.apache.ibatis.executor.BatchExecutorException: org.sonar.db.measure.MeasureMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: ORA-00001: unique constraint (SONAR.SYS_C006850) violated
    
    	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:226)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:217)
    	at org.sonar.db.DbSessionImpl.commit(DbSessionImpl.java:42)
    	at org.sonar.db.BatchSession.commit(BatchSession.java:168)
    	at org.sonar.ce.task.projectanalysis.step.PersistMeasuresStep.execute(PersistMeasuresStep.java:72)
    	at org.sonar.ce.task.step.ComputationStepExecutor.executeStep(ComputationStepExecutor.java:81)
    	at org.sonar.ce.task.step.ComputationStepExecutor.executeSteps(ComputationStepExecutor.java:72)
    	at org.sonar.ce.task.step.ComputationStepExecutor.execute(ComputationStepExecutor.java:59)
    	at org.sonar.ce.task.projectanalysis.taskprocessor.ReportTaskProcessor.process(ReportTaskProcessor.java:81)
    	at org.sonar.ce.taskprocessor.CeWorkerImpl$ExecuteTask.executeTask(CeWorkerImpl.java:209)
    	at org.sonar.ce.taskprocessor.CeWorkerImpl$ExecuteTask.run(CeWorkerImpl.java:191)
    	at org.sonar.ce.taskprocessor.CeWorkerImpl.findAndProcessTask(CeWorkerImpl.java:158)
    	at org.sonar.ce.taskprocessor.CeWorkerImpl$TrackRunningState.get(CeWorkerImpl.java:133)
    	at org.sonar.ce.taskprocessor.CeWorkerImpl.call(CeWorkerImpl.java:85)
    	at org.sonar.ce.taskprocessor.CeWorkerImpl.call(CeWorkerImpl.java:53)
    	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
    	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: org.apache.ibatis.executor.BatchExecutorException: org.sonar.db.measure.MeasureMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: ORA-00001: unique constraint (SONAR.SYS_C006850) violated
    
    	at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:148)
    	at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:129)
    	at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:122)
    	at org.apache.ibatis.executor.BaseExecutor.commit(BaseExecutor.java:242)
    	at org.apache.ibatis.executor.CachingExecutor.commit(CachingExecutor.java:119)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:223)
    	... 21 more
    Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (SONAR.SYS_C006850) violated
    
    	at oracle.jdbc.driver.OraclePreparedStatement.executeLargeBatch(OraclePreparedStatement.java:9711)
    	at oracle.jdbc.driver.T4CPreparedStatement.executeLargeBatch(T4CPreparedStatement.java:1447)
    	at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9487)
    	at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:237)
    	at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223)
    	at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223)
    	at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:122)
    	... 26 more

I already tried to raise the nextval of the respective sequence, but to no avail.
Any ideas what might have gone wrong or how to fix this problem?

Regards,
Christian

2

So, does that mean there’s no chance to upgrade our installation of SonarQube?

4

Hi, could you please give us some details about this constraint? It does not look like it has been created by SonarQube.

5

Hi. It is the primary key constraint of PROJECT_MEASURES. All the constraint names look like this, already in the SonarQube 6.7.5 version of the database. But there everything works well.

6

Thanks for you reply, btw. Is there probably a way to recreate all the constraints? Maybe they should look different.

7

Ah ok no the name is fine, we don’t control PK names. In this table’s case, it’s an auto-increment column using an Oracle sequence.

What’s the current value of this sequence? What’s the maximum value of PROJECT_MEASURES.ID?

8

Well, that’s what I tried (cf. original post) - max(PROJECT_MEASURES.ID) is 4872811566 and PROJECT_MEASURES_SEQ.LAST_NUMBER is 4872811540. I’ll try to raise it to 4872811640, again, and run an analysis.
max(PROJECT_MEASURES.ID) is 4872811736 and PROJECT_MEASURES_SEQ.LAST_NUMBER is 4872811740, now. That seems to be fine.
But it fails with another exception: unique constraint (SONAR67.SYS_C006831) violated, which is the PK of ISSUES. Another sequence issue? What’s wrong with those? Should I try to raise all of them?

9

ah, well but max(ISSUES.ID) is 6079563 and ISSUES_SEQ.nextval is 6079584.

10

It looks like indeed that your sequences are not aligned anymore. One reason might be a backup/restore that did not include the sequence state.

Just so you know, we removed all database-generated ID in SonarQube 8.X series, mostly to get rid of this kind of issue (we switched to java-generated uuid).

You have two solutions out of this situation:

  • sort out your sequence issue, fixing all nextval
  • give a try to upgrade on the latest 8.X version, that won’t use any sequence anymore

Whenever path you choose, make sure to have proper database backups before trying anything :slight_smile:

11

Yes, increasing all the sequences fixed it. Thanks for your support.
We did not re-import any backup, but maybe while copying the database the process was not properly shut down and data and sequences did not match.

12

Looks like indeed the most plausible cause. Glad you sorted this out :+1: