Database unique constraint error after upgrade

Isabelle Guimiot

unread,

Aug 31, 2016, 8:50:00 AM8/31/16

to SonarQube

Hi,

I recently updated our 9Gb sonar database (oracle 11) from v 5.1 to v 5.6.1, and now I can't create any new project, I always get this error :

### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SONAR.SYS_C0056700) violated
### The error may involve org.sonar.db.component.ResourceIndexMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into resource_index (kee, position, name_size, resource_id, root_project_id, qualifier) values (?, ?, ?, ?, ?, ?)
### Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SONAR.SYS_C0056700) violated

It happens at the end of the audit, while trying to insert the data, and it also happens when I try to manually create the project through the GUI. I think the database is corrupted...

I already had this error a few month ago on a test DB that was almost empty, so I destroyed the DB and created a new one and everything was OK, but this time I'd like to keep the data : restoring from the backup we made prior to migration is possible but not easy... Is there a way to repair the DB ?

Thanks !

Isabelle

Simon Brandhof

unread,

Aug 31, 2016, 11:10:12 AM8/31/16

to Isabelle Guimiot, SonarQube

Hi Isabelle,

When you say that you "can't create any new project", does that mean that the error is raised whatever the project key or name ? Did you try multiple values ?

And could you also give me information about the Oracle constraint SONAR.SYS_C0056700 ?

Thanks

--
You received this message because you are subscribed to the Google Groups "SonarQube" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sonarqube+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sonarqube/50747721-4fbc-4ec9-baa1-10fb89d6e500%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Simon BRANDHOF | SonarSource
Tech Lead & Co-Founder
http://twitter.com/SimonBrandhof

Isabelle Guimiot

unread,

Aug 31, 2016, 2:16:35 PM8/31/16

to Simon Brandhof, SonarQube

Hi Simon,

I tried many different names, unfortunately the issue is not the project name...

Here's the index definition :

CREATE UNIQUE INDEX "SONAR"."SYS_C0056700" ON "SONAR"."RESOURCE_INDEX" ("ID")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 35651584 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SONAR_DATA"

And the RESOURCE_INDEX table contains 5.289.016 rows.

Any clues ?

2016-08-31 11:10 GMT-04:00 Simon Brandhof <simon.b...@sonarsource.com>:

Hi Isabelle,

When you say that you "can't create any new project", does that mean that the error is raised whatever the project key or name ? Did you try multiple values ?
And could you also give me information about the Oracle constraint SONAR.SYS_C0056700 ?

Thanks

On Wed, 31 Aug 2016 at 14:50 Isabelle Guimiot <isabelle...@gmail.com> wrote:

Hi,

I recently updated our 9Gb sonar database (oracle 11) from v 5.1 to v 5.6.1, and now I can't create any new project, I always get this error :

### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SONAR.SYS_C0056700) violated
### The error may involve org.sonar.db.component.ResourceIndexMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into resource_index (kee, position, name_size, resource_id, root_project_id, qualifier) values (?, ?, ?, ?, ?, ?)
### Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SONAR.SYS_C0056700) violated

It happens at the end of the audit, while trying to insert the data, and it also happens when I try to manually create the project through the GUI. I think the database is corrupted...

I already had this error a few month ago on a test DB that was almost empty, so I destroyed the DB and created a new one and everything was OK, but this time I'd like to keep the data : restoring from the backup we made prior to migration is possible but not easy... Is there a way to repair the DB ?

Thanks !

Isabelle

--
You received this message because you are subscribed to the Google Groups "SonarQube" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sonarqube+unsubscribe@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sonarqube/50747721-4fbc-4ec9-baa1-10fb89d6e500%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Simon Brandhof

unread,

Aug 31, 2016, 3:18:11 PM8/31/16

to Isabelle Guimiot, SonarQube

The Oracle sequence RESOURCE_INDEX_SEQ used to generate ids is not correct. Value is smaller than max(id) of the table RESOURCE_INDEX.

It can be verified by comparing results of :

SELECT resource_index_seq.nextval FROM dual;
SELECT max(id) FROM resource_index;

That may occur when database has been restored from a backup that does not contain sequence values. Do you know the maintenance operations that have been done on SonarQube database ?

To unsubscribe from this group and stop receiving emails from it, send an email to sonarqube+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sonarqube/50747721-4fbc-4ec9-baa1-10fb89d6e500%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Simon BRANDHOF | SonarSource
Tech Lead & Co-Founder
http://twitter.com/SimonBrandhof

Isabelle Guimiot

unread,

Aug 31, 2016, 3:24:45 PM8/31/16

to Simon Brandhof, SonarQube

It's a DB used for the validation environment, we asked the DBA to take the data from production and to import them to the validation DB. I will contact the DBA and see if he missed anything during the export...

Thank you !

To unsubscribe from this group and stop receiving emails from it, send an email to sonarqube+unsubscribe@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sonarqube/50747721-4fbc-4ec9-baa1-10fb89d6e500%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Simon BRANDHOF | SonarSource
Tech Lead & Co-Founder
http://twitter.com/SimonBrandhof

Isabelle Guimiot

unread,

Sep 1, 2016, 11:59:51 AM9/1/16

to Simon Brandhof, SonarQube

OK, I finally had an explanation : the export was fully made by the DBA, but the prod server wasn't stopped during the export, and some audits were running during the export, making the sequences inconsistent in the DB dump...

Good to know for the next time, we'll stop the DB before we run a dump ! :)

2016-08-31 15:24 GMT-04:00 Isabelle Guimiot <isabelle...@gmail.com>:

It's a DB used for the validation environment, we asked the DBA to take the data from production and to import them to the validation DB. I will contact the DBA and see if he missed anything during the export...

Thank you !

Simon Brandhof

unread,

Sep 1, 2016, 12:22:37 PM9/1/16

to Isabelle Guimiot, SonarQube

Good news. Does that mean that the SonarQube instance is now fully upgraded and operational ?

On Thu, 1 Sep 2016 at 17:59 Isabelle Guimiot <isabelle...@gmail.com> wrote:

OK, I finally had an explanation : the export was fully made by the DBA, but the prod server wasn't stopped during the export, and some audits were running during the export, making the sequences inconsistent in the DB dump...

Good to know for the next time, we'll stop the DB before we run a dump ! :)

2016-08-31 15:24 GMT-04:00 Isabelle Guimiot <isabelle...@gmail.com>:

It's a DB used for the validation environment, we asked the DBA to take the data from production and to import them to the validation DB. I will contact the DBA and see if he missed anything during the export...

Thank you !

To unsubscribe from this group and stop receiving emails from it, send an email to sonarqube+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sonarqube/50747721-4fbc-4ec9-baa1-10fb89d6e500%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Simon BRANDHOF | SonarSource
Tech Lead & Co-Founder
http://twitter.com/SimonBrandhof

Reply all

Reply to author

Forward