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