SonarQube 6.7 LTS to 7.6 migration: Execution of migration step #1907 'Populate table live_measures' failed

agabrys · February 10, 2019, 10:16pm

Hello,
We have SonarQube Community 6.7.6 LTS with about 4600 projects (we scan a lot of branches). Developer edition provides better branching mechanism, so we decided to migrate to it. We got a trial version, installed SonarQube 7.6 and started an upgrade process. Unfortunately, after 3 days (yes, very long) the migration process hit the following issue:

2019.02.08 12:29:18 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 #1907 'Populate table live_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 java.lang.Iterable.forEach(Iterable.java:75)
		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:68)
		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 java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
		at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
		at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.IllegalStateException: Error during processing of row: [Unavailable: Bad format for BigDecimal ';112=2427;118=2427;119=2427;124=2427;129=809ûû123=0;12' in column 6.]
		at org.sonar.server.platform.db.migration.step.SelectImpl.newExceptionWithRowDetails(SelectImpl.java:89)
		at org.sonar.server.platform.db.migration.step.SelectImpl.scroll(SelectImpl.java:81)
		at org.sonar.server.platform.db.migration.step.MassUpdate.execute(MassUpdate.java:92)
		at org.sonar.server.platform.db.migration.version.v70.PopulateLiveMeasures.execute(PopulateLiveMeasures.java:57)
		at org.sonar.server.platform.db.migration.step.DataChange.execute(DataChange.java:45)
		at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
		... 9 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Application was streaming results when the connection failed. Consider raising value of 'net_write_timeout' on the server.
		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:423)
		at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
		at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
		at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3562)
		at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3462)
		at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3903)
		at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871)
		at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1999)
		at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:374)
		at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:354)
		at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6312)
		at org.apache.commons.dbcp2.DelegatingResultSet.next(DelegatingResultSet.java:1160)
		at org.apache.commons.dbcp2.DelegatingResultSet.next(DelegatingResultSet.java:1160)
		at org.sonar.server.platform.db.migration.step.SelectImpl.scroll(SelectImpl.java:77)
		... 13 common frames omitted
Caused by: java.net.SocketException: Connection reset
		at java.net.SocketInputStream.read(SocketInputStream.java:210)
		at java.net.SocketInputStream.read(SocketInputStream.java:141)
		at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:101)
		at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144)
		at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:174)
		at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3011)
		at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3522)
		... 23 common frames omitted

On the page SonarQube Update Database for Version 7 Fails somebody suggested to remove live_measures_component index before the database upgrade:

DROP INDEX "live_measures_component";
CREATE INDEX live_measures_component ON live_measures (component_uuid,metric_id);

Could somebody confirm that it is safe?

Cheers

Krzysztof_Jazgara · February 28, 2019, 4:40pm

Hello Adam,

Unfortunately, after 3 days (yes, very long)

What DB engine are you using?

On the page SonarQube Update Database for Version 7 Fails somebody suggested to remove live_measures_component index before the database upgrade:

The advise shared there was not to completely remove the live_measures_component.
The response there says to first drop the index and then recreate it without unique constraint.
We do not authorize this db schema change at the moment - however it seems like we need to decide what to do with those kind of cases (probably caused by third party/community plugins).

There is also a SQL SELECT statement that may pretty much tell you how many records/projects are affected. Can you run it and share some feedback on the output ?
Here is the query:

SELECT p.uuid, p.name, p.kee, p.created_at, p.long_name, p.language,  pm.metric_id, COUNT(1) 
FROM project_measures pm 
INNER JOIN projects p on p.uuid = pm.component_uuid 
INNER JOIN snapshots s on s.uuid = pm.analysis_uuid 
WHERE s.islast = TRUE and pm.person_id is null 
GROUP BY p.uuid, p.name, p.kee, p.created_at, p.long_name, p.language,  pm.metric_id HAVING 
COUNT(1) > 1;

Awaiting your feedback.
Pozdr
Krzysztof

agabrys · March 6, 2019, 7:58am

Hello Krzysztof,
I apologize for delay, but I didn’t have access to our SonarQube instance for a while. The drop index solution does not work. Luckily our infrastructure team was able to “fix” the problem. They increased the amout of RAM memory (from 16 to 48 GB) and after that the upgrade process has been finished successfully.

At this moment we use MySQL database and the following community plugins:

I’ll ask our infrastructure team if they can execute the SQL query which you wrote.

Cheers

ganncamp · March 7, 2019, 8:07am

Hi Adam,

I’m glad you got this worked out.

Tangentially, I feel the need to point out that SonarCSS is maintained. Also, badges are now natively available.

Ann

agabrys · March 7, 2019, 9:44am

Hi Ann,

Thank you, I didn’t know

Yes, but it has less rules and forces us to install Node.js on CI (Node.js is a one big security hole).

Cheers

ganncamp · July 10, 2019, 12:55pm

A post was merged into an existing topic: Upgrade from 6.7.3 to 7.9 (LTS) -> Fail to execute CREATE UNIQUE INDEX live_measures_component