Sonarqube upgrade to 5.6.6 to 6.7.6

Must-share information (formatted with Markdown):

  • which versions are you using (SonarQube, Scanner, Plugin, and any relevant extension) v5.6.6
  • what are you trying to achieve v6.7.6
  • what have you tried so far to achieve this
    We ran into production issue when we try to upgrade it to v6.7.6, any idea to resolve the issue.
2019.03.31 15:00:36 ERROR web[][DbMigrations] #1207 'Drop unused columns on PROJECT_MEASURES': failure | time=16ms
2019.03.31 15:00:36 ERROR web[][DbMigrations] Executed DB migrations: failure | time=17ms
2019.03.31 15:00:36 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=71ms
2019.03.31 15:00:36 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 #1207 'Drop unused columns on PROJECT_MEASURES' failed
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:79)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:67)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl$$Lambda$756/412742079.accept(Unknown Source)
        at java.util.Iterator.forEachRemaining(Iterator.java:116)
        at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
        at java.util.stream.ReferencePipeline$Head.forEachOrdered(ReferencePipeline.java:590)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:52)
        at org.sonar.server.platform.db.migration.engine.MigrationEngineImpl.execute(MigrationEngineImpl.java:50)
        at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doUpgradeDb(DatabaseMigrationImpl.java:105)
        at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doDatabaseMigration(DatabaseMigrationImpl.java:80)
        at org.sonar.server.platform.db.migration.DatabaseMigrationImpl$$Lambda$751/1241681920.run(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE project_measures DROP COLUMN rules_category_id, DROP COLUMN tendency, DROP COLUMN measure_date, DROP COLUMN url, DROP COLUMN rule_priority, DROP COLUMN characteristic_id, DROP COLUMN rule_id
        at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:97)
        at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:77)
        at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:117)
        at org.sonar.server.platform.db.migration.version.v60.DropUnusedMeasuresColumns.execute(DropUnusedMeasuresColumns.java:48)
        at org.sonar.server.platform.db.migration.step.DdlChange.execute(DdlChange.java:45)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
        ... 13 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Can't DROP 'rules_category_id'; check that column/key exists
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
        at com.mysql.jdbc.Util.getInstance(Util.java:408)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2444)
        at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
        at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:82)
        ... 18 common frames omitted

You cannot upgrade 5.6.6 to 6.7.6. According to the upgrade guide you have to upgrade your server to the latest 5.X version (5.6.7, download). Next you can finally upgrade to the latest 6.X version (at this moment 6.7.6 - the same as you tried).

5.6.6 → 5.6.7 → 6.7.6

Actually Adam, any point release of a previous LTS fits the bill, so the migration path he’s laid out is actually fine. :slight_smile:

:open_mouth: Thanks, I didn’t know :bowing_man:

Does it have any sense to try do the upgrade as I wrote?

Sorry, but not really. There shouldn’t be any database migrations from one point version to another, and the migrations are really the point of hitting the intermediate LTSs. Once upon a time, we accumulated database migrations indefinitely but with the 4.x LTS (IIRC) we started keeping only migrations since the previous LTS which is why you must hit each one in between. (Tell me if that’s not clear.)

 
:slight_smile:
Ann

1 Like

Great :slight_smile: Now I fully understand :+1: I accidentally started a new thread here…

The problem is:

And unfortunately, I cannot help you. I hope that somebody else will figure out how to fix it.


Of course you can add this column and execute the upgrade one more time, but changing manually anything in the database is not recommended.

1 Like

Appreciate your response Adam! I am puzzling manual deletion of column might cause some other issue

Hi Ann Campbell,

Is there any way to troubleshoot this issue, we are caused to roll back the changes to 5.6.6 again in production and in development environment we are in still seeing the same error.

Troubleshooting steps carried so far:

  1. Increased the temp space to 65 GB as it consuming more
  2. Increased the installation directory to 200 GB + hazzle free to store the database

Really appreciate your effort to resolve the issue

The column project_measures.rules_category_id is supposed to exist before upgrading. Did you try to manually drop it? Anyway a workaround is to create it back and run again the upgrade.

The funny part is that MySQL does not support DROP COLUMN ... IF EXISTS. The feature request exists since 2005!

1 Like

Hi Simon,

Thanks for your response!

I could see “project_measures.rules_category_id” does exist before upgrade and when we initiate the upgrade, it does missing on the table.

mysql> desc project_measures;
±------------------±---------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------------±---------------±-----±----±--------±---------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| value | decimal(38,20) | YES | | NULL | |
| metric_id | int(11) | NO | | NULL | |
| snapshot_id | int(11) | YES | MUL | NULL | |
| rule_id | int(11) | YES | | NULL | |
| rules_category_id | int(11) | YES | | NULL | |

Regards
Dinesh A

Must-share information (formatted with Markdown):

  • which versions are you using (SonarQube, Scanner, Plugin, and any relevant extension)
    Sonarqube 5.6.6
  • what are you trying to achieve
    Planning to upgrade the Sonarqube version from 5.6.6 to 6.7.6
  • what have you tried so far to achieve this
  1. We have tried to upgrade the sonarqube server but ran with the errors as mentioned in the ticket Link
  2. We tried with several possibility to get the upgrade done successfully but with no luck
    a. Increased the max open files count, ulimit, swappiness, mem in application for sonarapp user
    sysctl -w vm.max_map_count=262144
    sysctl -w fs.file-max=65536
    ulimit -n 65536
    ulimit -u 2048 as per the link
    b. So SonarQube doc says MySQL is not recommended for large DBs… can you confirm
    62775.9(DB size) - 51082.41(Project_measures size) is considerably so high for not able to manage?
    c. We tried with this upgrade path: 5.6.6 -> 5.6.7 LTS -> 6.7.x LTS but no luck
    d. We tried to cleanup the obsolete project more than 1 year and i dont see much difference post cleanup of project in database size
    e. Currently we are having the count of 1166 active projects and LOC count of 22275656

Need your confirmation if the above is not feasible for Mysql community version and also plan to go with Oracle if it will work there in the DB

As per the pricing model, link which edition will be compatible for us to upgrade further.

Need point of contact to discuss further more details about proposal plan and architecture to be proposed for our environment.

Someone assist on this request please

Could you please clarify? Does that mean that the column has been successfully dropped?
Could you also share the full web.log file of 6.7.6 when upgrading for the first time from the 5.6 db?

Thanks

weblog.txt (12.5 KB)

Hi Simon,

I have uploaded the requested web.log from the server where it got failed. Kindly review and confirm for the same.

And also we wish to go for licensed edition if not working as expected and i would like to have conatct person details to discuss about architecture review and how to plan for implementation plan to get it implemented without any issues.

Regards
Dinesh A
+91 - 9944347023

Hi Team,

Can i get the enterprise support contact details?

Hi @Dinesh_A,

You’ll need to go through sales first. I suggest you start here: https://www.sonarsource.com/plans-and-pricing/

 
Ann