Sonarqube 5.6 to 6.0 database migration fails in DropUnusedMeasuresColumns

Question

When upgrading from Sonarqube version 5.6 to 6.0 the upgrade process fails with the following error:

2016.08.12 14:56:35 ERROR web[o.s.s.d.m.DatabaseMigrator] Fail to execute database migration: org.sonar.db.version.v60.DropUnusedMeasuresColumns
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

The current table looks like this and has none of the columns that are supposed to be dropped.

CREATE TABLE `project_measures` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `value` decimal(38,20) DEFAULT NULL,
  `metric_id` int(11) NOT NULL,
  `snapshot_id` int(11) DEFAULT NULL,
  `text_value` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `alert_status` varchar(5) COLLATE utf8_bin DEFAULT NULL,
  `alert_text` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  `description` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  `variation_value_1` decimal(38,20) DEFAULT NULL,
  `variation_value_2` decimal(38,20) DEFAULT NULL,
  `variation_value_3` decimal(38,20) DEFAULT NULL,
  `variation_value_4` decimal(38,20) DEFAULT NULL,
  `variation_value_5` decimal(38,20) DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  `measure_data` longblob,
  PRIMARY KEY (`id`),
  KEY `measures_sid_metric` (`snapshot_id`,`metric_id`),
  KEY `measures_person` (`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=81903974 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

The table has 3.5 million records and over time we've upgraded without problems from 5.4 to 5.5 to 5.6.

Does anyone know how to fix this manually so we can continue the upgrade process?

Update

Below is the migration log when doing a second attempt. The DropUnusedMeasuresColumns seems to hang.

2016.08.16 08:29:12 INFO  web[o.s.s.d.m.PlatformDatabaseMigration] Starting DB Migration
2016.08.16 08:29:14 INFO  web[DbMigration] ==  UpdateUsersExternalIdentityWhenEmpty: migrating ===========================
2016.08.16 08:29:14 INFO  web[o.s.d.v.MassUpdate] 0 users processed (0 items/sec)
2016.08.16 08:29:14 INFO  web[DbMigration] ==  UpdateUsersExternalIdentityWhenEmpty: migrated (0.0950s) ==================
2016.08.16 08:29:14 INFO  web[DbMigration] 
2016.08.16 08:29:14 INFO  web[DbMigration] ==  CreatePermTemplatesCharacteristics: migrating =============================
2016.08.16 08:29:14 INFO  web[DbMigration] -- create_table("perm_tpl_characteristics", {})
2016.08.16 08:29:15 INFO  web[DbMigration]    -> 0.5600s
2016.08.16 08:29:15 INFO  web[DbMigration]    -> 0 rows
2016.08.16 08:29:15 INFO  web[DbMigration] -- index_exists?("perm_tpl_characteristics", ["template_id", "permission_key"], {:name=>"uniq_perm_tpl_charac"})
2016.08.16 08:29:15 INFO  web[DbMigration]    -> 0.0650s
2016.08.16 08:29:15 INFO  web[DbMigration] -- add_index("perm_tpl_characteristics", ["template_id", "permission_key"], {:name=>"uniq_perm_tpl_charac", :unique
=>true})
2016.08.16 08:29:15 INFO  web[DbMigration]    -> 0.6200s
2016.08.16 08:29:15 INFO  web[DbMigration]    -> 0 rows
2016.08.16 08:29:15 INFO  web[DbMigration] ==  CreatePermTemplatesCharacteristics: migrated (1.2580s) ====================
2016.08.16 08:29:15 INFO  web[DbMigration] 
2016.08.16 08:29:15 INFO  web[DbMigration] ==  AddColumnsWithUuidsToResourceIndex: migrating =============================
2016.08.16 08:29:54 INFO  web[DbMigration] ==  AddColumnsWithUuidsToResourceIndex: migrated (38.6700s) ===================
2016.08.16 08:29:54 INFO  web[DbMigration] 
2016.08.16 08:29:54 INFO  web[DbMigration] ==  PopulateUuidColumnsOfResourceIndex: migrating =============================
2016.08.16 08:30:42 INFO  ce[o.s.c.a.WebServerWatcherImpl] Still waiting for WebServer...
2016.08.16 08:31:00 INFO  web[o.s.d.v.MassUpdate] 149749 resource index entries processed (2495 items/sec)
2016.08.16 08:32:00 INFO  web[o.s.d.v.MassUpdate] 310999 resource index entries processed (2687 items/sec)
2016.08.16 08:32:19 INFO  web[o.s.d.v.MassUpdate] 362861 resource index entries processed (864 items/sec)
2016.08.16 08:32:19 INFO  web[DbMigration] ==  PopulateUuidColumnsOfResourceIndex: migrated (145.4370s) ==================
2016.08.16 08:32:19 INFO  web[DbMigration] 
2016.08.16 08:32:20 INFO  web[DbMigration] ==  CleanOrphanRowsInResourceIndex: migrating =================================
2016.08.16 08:32:20 INFO  web[o.s.d.v.MassUpdate] 0 resource index entries processed (0 items/sec)
2016.08.16 08:32:20 INFO  web[DbMigration] ==  CleanOrphanRowsInResourceIndex: migrated (0.1980s) ========================
2016.08.16 08:32:20 INFO  web[DbMigration] 
2016.08.16 08:32:20 INFO  web[DbMigration] ==  MakeUuidColumnsNotNullOnResourceIndex: migrating ==========================
2016.08.16 08:33:09 INFO  web[DbMigration] -- index_exists?(:resource_index, :component_uuid, {:name=>"resource_index_component"})
2016.08.16 08:33:09 INFO  web[DbMigration]    -> 0.0090s
2016.08.16 08:33:09 INFO  web[DbMigration] -- add_index(:resource_index, :component_uuid, {:name=>"resource_index_component"})
2016.08.16 08:33:18 INFO  web[DbMigration]    -> 9.4810s
2016.08.16 08:33:18 INFO  web[DbMigration]    -> 0 rows
2016.08.16 08:33:18 INFO  web[DbMigration] ==  MakeUuidColumnsNotNullOnResourceIndex: migrated (58.3240s) ================
2016.08.16 08:33:18 INFO  web[DbMigration] 
2016.08.16 08:33:18 INFO  web[DbMigration] ==  DropResourceIndexRidFromResourceIndex: migrating ==========================
2016.08.16 08:33:18 INFO  web[DbMigration] -- remove_index(:resource_index, {:name=>"resource_index_rid"})
2016.08.16 08:33:19 INFO  web[DbMigration]    -> 0.4860s
2016.08.16 08:33:19 INFO  web[DbMigration]    -> 0 rows
2016.08.16 08:33:19 INFO  web[DbMigration] ==  DropResourceIndexRidFromResourceIndex: migrated (0.4890s) =================
2016.08.16 08:33:19 INFO  web[DbMigration] 
2016.08.16 08:33:19 INFO  web[DbMigration] ==  DropIdColumnsFromResourceIndex: migrating =================================
2016.08.16 08:34:08 INFO  web[DbMigration] ==  DropIdColumnsFromResourceIndex: migrated (48.7900s) =======================
2016.08.16 08:34:08 INFO  web[DbMigration] 
2016.08.16 08:34:08 INFO  web[DbMigration] ==  DropUnusedMeasuresColumns: migrating ======================================
2016.08.16 08:34:57 INFO  ce[o.s.c.a.WebServerWatcherImpl] Still waiting for WebServer...
2016.08.16 08:46:31 INFO  ce[o.s.c.a.WebServerWatcherImpl] Still waiting for WebServer...

Did you check the backup? What is the state of this table before upgrade? — Jeroen Heier, Aug 12 '16 at 17:28
I rolled back to 5.6. In 5.6 the table has all the columns that the alter statement tries to drop. So it's more likely to be a timeout or an attempt to change the table again. It would be nice if the alter statements are idempotent so running them a second time doesn't cause issues. Any ideas how I can upgrade to 5.6 without losing my data? — Jorrit Salverda, Aug 15 '16 at 07:24
You said you rollbacked to 5.6 then request help to upgrade to 5.6. I'll assume you meant upgrade to 6.0. My question would be why would you lose any data? (it's not described in your question). Also, can you provide the description of the PROJECT_MEASURES table in 5.6 (ie. prior to upgrade to 6.0)? — Seb - SonarSource Team, Aug 16 '16 at 08:18
Yes, I meant upgrading to 6.0. As for the version, I'm using Google Cloud SQL first generation, it's mysql 5.6. I think the problem is that migrating the measures table with its large amount of data is taking too long and the query gets killed before it's completed. It was already running for 2 hours when failing. I'm retrying now with a higher cpu/mem tier (D32 instead of D0) to see if that makes a difference. — Jorrit Salverda, Aug 16 '16 at 08:52
When I check the project_measures now that it's still migrating according to Sonarqube the columns are already gone. — Jorrit Salverda, Aug 16 '16 at 08:55

score 0 · Answer 1 · answered Jan 17 '17 at 08:21

0

The problem is disk space it seems that mySql creates a temp table that is too big for the disk to handle. Free up some space and the upgrade will work without problems

answered Jan 17 '17 at 08:21

Paul Horvath

1
2

Thanks. Unfortunately I cannot try to confirm whether this works. In the meantime I started from scratch with Cloud SQL generation 2 and Sonarqube version 6. That second generation Cloud SQL grows required disk space automatically, but never shrinks it. Hopefully a smarter way than requiring this huge temp table can be used in the future to prevent this from costing money permanently after each upgrade :) – Jorrit Salverda Mar 12 '17 at 13:32

Sonarqube 5.6 to 6.0 database migration fails in DropUnusedMeasuresColumns

1 Answers1