SonarQube 升级 5.6.5 到 6.7 失败， "Table ' perm_tpl_characteristics' already exists"

Question

将 SonarQube 从 v5.6.5 升级到 V6.7 时，运行 \setup产生了以下错误:

2017.12.01 10:33:12 WARN  web[][o.s.s.p.DatabaseServerCompatibility] Database must be upgraded. Please backup database and browse /setup
....
2017.12.01 10:33:12 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin Findbugs / 3.6.0 / 
....
2017.12.01 10:33:12 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarTS / 1.1.0.1079 / 042c9e65239a47d92d305f9767f730b3cc1e5ed3
2017.12.01 10:33:12 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarXML / 1.4.3.1027 / 39588245cecf538bb27be4e496ff303b0143d20b
2017.12.01 10:33:12 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin Sonargraph / 3.5
2017.12.01 10:33:12 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin Sonargraph Integration / 2.0.1
2017.12.01 10:33:12 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin Web / 2.5.0.476 / 636872f5d37fa7a440fe07d08d504e1a881225e5
2017.12.01 10:33:12 INFO  web[][o.s.s.p.ServerPluginRepository] Deploy plugin jDepend / 1.1.1
2017.12.01 10:33:13 INFO  web[][o.s.s.p.Platform] Database needs migration
2017.12.01 10:33:13 INFO  web[][o.s.s.p.w.MasterServletFilter] Initializing servlet filter org.sonar.server.ws.WebServiceFilter@5851f091 [pattern=UrlPattern{inclusions=[/api/system/migrate_db/*, ...], exclusions=[/api/properties*, ...]}]
2017.12.01 10:33:13 INFO  web[][o.s.s.a.EmbeddedTomcat] HTTP connector enabled on port 9000
2017.12.01 10:33:29 INFO  web[][o.s.s.p.d.m.DatabaseMigrationImpl] Starting DB Migration and container restart
2017.12.01 10:33:29 INFO  web[][DbMigrations] Executing DB migrations...
2017.12.01 10:33:29 INFO  web[][DbMigrations] #1200 'Create table PERM_TPL_CHARACTERISTICS'...
2017.12.01 10:33:29 ERROR web[][DbMigrations] #1200 'Create table PERM_TPL_CHARACTERISTICS': failure | time=12ms
2017.12.01 10:33:29 ERROR web[][DbMigrations] Executed DB migrations: failure | time=13ms
2017.12.01 10:33:29 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=57ms
2017.12.01 10:33:29 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 #1200 'Create table PERM_TPL_CHARACTERISTICS' 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:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.IllegalStateException: Fail to execute CREATE TABLE perm_tpl_characteristics (id INTEGER NOT NULL AUTO_INCREMENT,template_id INTEGER NOT NULL,permission_key VARCHAR (64) NOT NULL,with_project_creator TINYINT(1) DEFAULT false NOT NULL,created_at BIGINT NOT NULL,updated_at BIGINT NOT NULL, CONSTRAINT pk_perm_tpl_characteristics PRIMARY KEY (id)) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
    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.CreatePermTemplatesCharacteristics.execute(CreatePermTemplatesCharacteristics.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)
    ... 11 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'perm_tpl_characteristics' already 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:423)
    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)
    ... 16 common frames omitted

从一个 LTS 版本升级到下一个版本是否过多？我应该先升级到中间版本吗？如果是这样，最短的升级路径是什么？

各种 MySql 版本是:

+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.20                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.20-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+

v5.6.5 和 v6.7 实例之间的插件不是 100% 相同的。 6.x 版不再支持某些插件。根据错误消息，我认为这不是问题，但这是两种配置之间的差异。

Answer 1

问题是该数据库最初是作为全新 V6.2 安装的一部分创建的。插件有问题，所以回滚到v5.6.5安装。不知道是不是/upgrade必须在“降级”期间调用。无论哪种方式，升级过程都没有想到这种边缘情况，所以我手动修复了它。

我运行了 MySql 查询 show table status;它显示了 perm_tpl_characteristics是空的，所以我删除了它。我重新运行升级，另一个失败，另一个表出现类似错误。我删除了表并重新运行升级，直到它成功。这就是我最终的结果:

USE DATABASE sonar;
DROP TABLE perm_tpl_characteristics;
DROP TABLE ce_task_input;
DROP TABLE ce_scanner_context;
DROP TABLE internal_properties;
DROP TABLE qprofile_changes;
DROP TABLE rule_repositories;
DROP TABLE organizations;
DROP TABLE webhook_deliveries;

我在 prod 实例上执行了这些 DDL 语句，升级成功。谢谢大家的建议。他们为我指明了正确的方向。