Upgrade from 6.7.6 to 7.8 Community mysql take very long to complete db upgrade

  • versions used upgrade from 6.7.6 tp 7.8 ce with mysql 5.7.24
  • error observed
    No error its takes forever to upgrade database
    web[DbMigrations] #1906 ‘Create table live_measures’: success | time=424ms
    2019.12.03 05:55:56 INFO web[DbMigrations] #1907 ‘Populate table live_measures’…
    2019.12.03 05:56:56 INFO web[o.s.s.p.d.m.s.MassUpdate] 53499 live measures processed (891 items/sec)
    2019.12.03 05:57:57 INFO web[o.s.s.p.d.m.s.MassUpdate] 132395 live measures processed (1314 items/sec)
    2019.12.03 05:58:57 INFO web[o.s.s.p.d.m.s.MassUpdate] 209999 live measures processed (1293 items/sec)
    2019.12.03 05:59:57 INFO web[o.s.s.p.d.m.s.MassUpdate] 300999 live measures processed (1516 items/sec)

P.S.: use the #bug:fault sub-category if you’re hitting a specific crash/error , or the #bug:fp sub-category for rules-related behaviour


From the log you’re reported, the migration is going well : around 1500 items/sec. is good.
How much time have you wait ? Could you please display logs at the end ?


Thanks Julien for reply.

It has completed after one hour and few mins

now issue started as below message

2019.12.03 07:25:12 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 1002 snapshots processed (0 items/sec)
2019.12.03 07:26:12 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 1038 snapshots processed (0 items/sec)
2019.12.03 07:27:12 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 1095 snapshots processed (0 items/sec)
2019.12.03 07:28:12 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 1138 snapshots processed (0 items/sec)
2019.12.03 07:29:12 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 1156 snapshots processed (0 items/sec)
2019.12.03 07:30:12 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 1179 snapshots processed (0 items/sec)

looks like it is doing nothing and loading more snapshot to try.

Ok, good news, the migration of live measures has been completed in one hour ?

How much time did you wait for the next migration you’ve reported ? And which migration is it by the way ?

That means that snapshots are correctly processed, and will be flush into the DB by batch. So you will see the number of snapshot processed increase until the flush size is reach.

Thanks Julien
“live measures processed” completed in one hour
“snapshots processed” completed in next one and half hours

Thanks Pierre for giveing awareness about “0 items/sec”

But after 4 hours and 30 minutes of upgrade process i got below message. and its failed

2019.12.03 09:49:18 INFO  web[][DbMigrations] #2707 'Update statuses of Security Hotspots'...
2019.12.03 09:50:08 WARN  web[][o.a.c.d.BasicDataSource] An internal object pool swallowed an Exception.
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during rollback(). Transaction resolution unknown.
        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:408)
        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:919)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
        at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4561)
        at org.apache.commons.dbcp2.DelegatingConnection.rollback(DelegatingConnection.java:473)
        at org.apache.commons.dbcp2.PoolableConnectionFactory.passivateObject(PoolableConnectionFactory.java:402)
        at org.apache.commons.pool2.impl.GenericObjectPool.returnObject(GenericObjectPool.java:559)
        at org.apache.commons.dbcp2.PoolableConnection.close(PoolableConnection.java:200)
        at org.apache.commons.dbcp2.DelegatingConnection.closeInternal(DelegatingConnection.java:239)
        at org.apache.commons.dbcp2.DelegatingConnection.close(DelegatingConnection.java:210)
        at org.apache.commons.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:247)
        at org.sonar.server.platform.db.migration.step.DataChange.execute(DataChange.java:42)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:67)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl$$Lambda$1149/1012447386.accept(Unknown Source)
        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 org.sonar.server.platform.db.migration.DatabaseMigrationImpl$$Lambda$1143/475132887.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)
2019.12.03 09:50:08 ERROR web[][o.s.s.w.WebServiceEngine] Fail to process request http://sonar.staging.com/api/system/db_migration_status
java.lang.IllegalStateException: Failed to read content of table schema_migrations

Hello, Good news is DB upgrade is done.
I have added more RAM to VM and started again after old database restore.
it took 6 hours and 30 minutes to complete.

Is it not quite long time of database upgrade ?
why such a long time ? can you highlight some point which can make upgrade faster ? I have done this for my test VM, now i am going to do for production.
in old version (< 6.7.6 ) upgrade was much faster.

We did some upgrade compacting when you upgrade to 7.9. Theses optimisations are not in 7.8. If you have time, you may want to benchmark an upgrade from 6.7.6 to the LTS directly. No promise that it will be faster, but if you got the opportunity, it’s worth the try. Please note that MySQL is not anymore supported with 7.9 version, so you will have to migrate your database first to PostgreSQL, Oracle, or SQL Server (See SonarQube requirements).

first i thought to go for 7.9 LTS. but migration from mysql to postgresql, I tired many time and did not get success, After few days i got tired and decided to go for 7.8
migration tool https://binaries.sonarsource.com/Distribution/mysql-migrator/mysql-migrator-
didn’t work at all for me atleast, also not giving clear error messages :frowning:

Maybe we can help you with the migration. Could you be more specific about the issue you encountered with mysql-migrator ?

sure I will create another topic and link here