Hey sonar experts,
I am trying to upgrade from 7.6 to 7.9.x LTS but I’m getting many DB issues.
Developper Edition
Either I tried going through 7.7 , 8.0, 8.1 no matter what I do, I have always the same exact error with that index.
As a workaround the database admin in my team tried to create the index missing but it doesn’t work as expected, after the creation of the index we have after many issues with some constraints, issue after issue.
We need to upgarde asap.
Any news or solution for this??
I see there is a similar post some months ago but nothing posted as a solution.
I have this error:
2020.02.17 10:21:17 ERROR web[o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=262ms
2020.02.17 10:21:17 ERROR web[o.s.s.p.d.m.DatabaseMigrationImpl] DB migration ended with an exception
org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #2606 ‘Drop DATA_TYPE column from FILE_SOURCES table’ failed
…
Caused by: java.lang.IllegalStateException: Fail to execute DROP INDEX file_sources_uuid_type ON file_sources
…
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot drop the index ‘file_sources.file_sources_uuid_type’, because it does not exist or you do not have permission.
For the records, we created the missing index, then we had a contraint error…
We removed the constraint , then we had other errors.
We don’t want to fix errors everytime they appear, we don’t know how it will impact in some other tables…
I hope you can find the solution soon.
Our constraint error after the creation of the index:
After we created that index we had these type of issues:
2020.02.14 12:41:57 INFO web[DbMigrations] #2606 ‘Drop DATA_TYPE column from FILE_SOURCES table’…
…
2020.02.14 12:41:57 ERROR web[o.s.s.p.d.m.DatabaseMigrationImpl] DB migration ended with an exception
org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #2606 ‘Drop DATA_TYPE column from FILE_SOURCES table’ faile
…
Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE file_sources DROP COLUMN data_type
…
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The object ‘DF__file_sour__data___74A4331B’ is dependent on column ‘data_type’.
It’s hard to tell what’s wrong and what to do to fix it without having hands on your DB.
I can only give you hints into doing that yourself.
Have you tried starting the exact version of SQ you’re running in production today on an brand new schema? (the more similar to production the SGDB where this schema will be, the better)
SQ would initialize that schema. You could then find a tool to compare your production DB schema with that vanilla schema and find out what the difference are and probably fix them.
Hi Sonar experts,
We run a ms sql schema comparison between a brand new schema created with version 7.6 and our current sonarqube DB , as well running on 7.6 developper Ed . The reports returned a list of many differences. For example :
the index [dbo].[file_sources].[file_sources_uuid_type] exists in the brand new DB, but doesn’t exist in our current DB
the schema m2ss doesn’t exist in the new DB but it exists in our current DB.
As a plus, there’s about 60 tables that are different between the 2 databases.
Can you give us a hint of what to do in this case? Last time we didn’t have any issues upgrading Sonar. How comes now we can’t upgrade as before?
You are the only competent people who can understand the possible impacts , we can share the report with you if you are willing to help us .Our DBA, don’t know what’s relevant to change for this kind of migrations.
We were thinking to do a copy of tables from our current DB to the new one to keep the Brand new schema, we need your input cause some tables have more columns in our current DB, this would mean copying the data while dropping columns…not the best solution either.
We really need your help or a possible ETA of the fix you are working on : https://jira.sonarsource.com/browse/SONAR-12464
I have no idea what that “m2ss” schema can be. SonarQube uses only one schema.
(but a quick googling digs up posts related to Microsoft Migration Server Assistant)
In 7.6, SQ has 63 tables in total. Having 60 different tables is quite suspicious. Comparison could be raising false positive differences.
Do you mean that your 7.6 database has more columns that a brand new 7.6 DB?
To all visible extends, your DB seem to have some history which lead to its current state. Could very well be operations totally outside SQ’s control (such as DB restore).
Feel free to share the output of that comparison you made.
Update: We were able to run the upgrade by renaming some indexes that appeared as table_name$index_name and removing some sql constraints that used columns that need to be drop when running the upgrade.All those differences that we were seeing in the report were about the collation but it was not the main issue. We think the schema and indexes were changed when we migrated from my sql to ms sql.Good news we were able to upgrade, firstt to LTS 7.9.2 then to 8.2 dev edition