SQ 7.1 > SQ 7.2 Migration fails

sebastian2209 · June 20, 2018, 3:03pm

Hi there,

I got an SQLException when upgrading from 7.1 to 7.2.

2018.06.20 16:37:23 INFO  web[][DbMigrations] #2128 'Purge duplicate rules_parameters and their orphans'...
2018.06.20 16:37:23 ERROR web[][DbMigrations] #2128 'Purge duplicate rules_parameters and their orphans': failure | time=48ms
2018.06.20 16:37:23 ERROR web[][DbMigrations] Executed DB migrations: failure | time=50ms
2018.06.20 16:37:23 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=163ms
2018.06.20 16:37:23 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 #2128 'Purge duplicate rules_parameters and their orphans' 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.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 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.sql.SQLException: You can't specify target table 'p1' for update in FROM clause
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.sonar.server.platform.db.migration.step.UpsertImpl.execute(UpsertImpl.java:66)
        at org.sonar.server.platform.db.migration.version.v72.PurgeDuplicateRulesParameters.execute(PurgeDuplicateRulesParameters.java:64)
        at org.sonar.server.platform.db.migration.step.DataChange.execute(DataChange.java:44)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
        ... 11 common frames omitted

versions:

SQ 7.2
DB: MySQL 5.7

janos · June 21, 2018, 10:14am

Thank you for reporting this.

We don’t fully understand the problem yet: the migration is well tested and we should have caught it. We need a bit more details to understand what is going wrong and how to fix. Can you please send:

All the server logs
The sonar.properties configuration file

I created a ticket to track this issue:
https://jira.sonarsource.com/browse/SONAR-10923

Thanks,
Janos

sebastian2209 · June 21, 2018, 1:16pm

Hi,

this is the first time I got this problems. The DB was migrated several times without any problem.

Logs and sonar.properties (it’s actually a zip file, please remove the txt extension before opening )

sonar.zip.txt (14.1 KB)

Before I update SQ, I start it to install/remove some plugins.

Used plugins:

checkstyle-sonar-plugin-4.10.jar
sonar-css-plugin-4.18.jar
sonar-findbugs-plugin-3.7.0.jar
sonar-issueresolver-plugin-1.0.2.jar
sonar-java-plugin-5.4.0.14284.jar
sonar-javascript-plugin-4.1.0.6085.jar
sonar-jdepend-plugin-1.1.1.jar
sonar-ldap-plugin-2.2.0.608.jar
sonar-pmd-plugin-2.6.jar
sonar-rci-plugin-1.0.1.jar
sonar-scm-svn-plugin-1.7.0.1017.jar
sonar-web-plugin-2.6.0.1053.jar
sonar-xml-plugin-1.5.1.1452.jar

simon.brandhof · June 22, 2018, 7:43pm

We don’t reproduce the error with MySQL 5.6 and 5.7.22 with the default settings. Changes in the query optimizer in 5.7.6 may have an impact on the DB migration that fails. Could you share the result of show variables like 'optimizer_switch' ? Except the extensions/plugins/ directory, are there some specific changes on the SonarQube installation, like a different JDBC driver ?

sebastian2209 · June 25, 2018, 12:27pm

Here is the result of the optimizer_switch query:

index_merge=on
index_merge_union=on
index_merge_sort_union=on
index_merge_intersection=on
engine_condition_pushdown=on
index_condition_pushdown=on
mrr=on
mrr_cost_based=on
block_nested_loop=on
batched_key_access=off
materialization=on
semijoin=on
loosescan=on
firstmatch=on
duplicateweedout=on
subquery_materialization_cost_based=on
use_index_extensions=on
condition_fanout_filter=on
derived_merge=on

Our MySQL is an 5.7.12 but we never changed that. Only the sonar schema was migrated several times.

There are no other changes in the installation. We use the delivered jdbc driver.

sebastian2209 · June 25, 2018, 1:54pm

Maybe I have a closer look to the actual problem.
I configured the log level to trace and I saw the query which might be the problem:

2018.06.25 14:45:50 TRACE web[][sql] time=16ms | sql=DELETE p1 FROM rules_parameters as p1 WHERE id NOT IN (SELECT id FROM (SELECT MIN(id) as id FROM rules_parameters GROUP BY rule_id, name) temp)
2018.06.25 14:45:50 ERROR web[][DbMigrations] #2128 'Purge duplicate rules_parameters and their orphans': failure | time=27ms

Trying to run that query in MySQL workbench, showed me an 1093 error.

Error Code: 1093. You can't specify target table 'p1' for update in FROM clause

Searching this code led me to https://stackoverflow.com/a/45498

As a workaround I could set derived_merge=off before migrating and turn it on after the migration.

UPDATE: derived_merge=off doesn’t help, still get the exception

janos · June 26, 2018, 12:51pm

Just to give you an update, on MySQL 5.7.22, having the same value of optimizer_switch as you (the default, apparently), the query works for me. I continue to try to reproduce, or to find another way to write the query.

janos · June 26, 2018, 1:30pm

I am able to reproduce the problem on MySQL 5.7.12.

Is it possible for you to upgrade MySQL to the latest GA version, 5.7.22?

janos · June 26, 2018, 2:59pm

Lazy workaround: manually insert the migration that doesn’t work.

insert into schema_migrations values (2128);

And then restart SonarQube, visit /setup page to complete the database upgrade, by performing the rest of the migrations.

This migration deletes records that are duplicate by rule_id + name on the rules_parameters table. If there are no duplicates, then the workaround will work flawlessly. If there are some duplicates, then a next migration step will fail. In that case, the duplicates must be deleted manually before continuing with the database upgrade.

antosik · July 25, 2018, 7:11am

Hi I’m on mysql 5.7.11 (centos 7) I’m trying to upgrade Sonar from 6.7.4 LTS to 7.2 and i have exactly the same issue

2018.07.24 18:44:02 ERROR web[][DbMigrations] #2128 'Purge duplicate rules_parameters and their orphans': failure | time=48ms
2018.07.24 18:44:02 ERROR web[][DbMigrations] Executed DB migrations: failure | time=26659526ms
2018.07.24 18:44:02 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=26659596ms
2018.07.24 18:44:02 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 #2128 'Purge duplicate rules_parameters and their orphans' 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.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 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.sql.SQLException: You can't specify target table 'p1' for update in FROM clause
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.sonar.server.platform.db.migration.step.UpsertImpl.execute(UpsertImpl.java:66)
        at org.sonar.server.platform.db.migration.version.v72.PurgeDuplicateRulesParameters.execute(PurgeDuplicateRulesParameters.java:64)
        at org.sonar.server.platform.db.migration.step.DataChange.execute(DataChange.java:44)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
        ... 11 common frames omitted

I will try to follow lazy workaround.

simon.brandhof · August 30, 2018, 7:45pm

@antosik same answer, could you please give a try to a more recent version of MySQL ?

asunnys · August 31, 2018, 9:49am

非感谢您的帮助,我用您的方法解决了问题…

Ann’s Edit: According to Google Translate:

Thank you for your help, I solved the problem with your method…