SonarQube upgrade 5.6.5 to 6.7 fails with "Table 'perm_tpl_characteristics' already exists"

Question

When upgrading SonarQube from v5.6.5 to V6.7, running \setup produced the following error:

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

Is the upgrade from one LTS version to the next too much? Should I upgrade to an intermediate version first? If so, what would be the shortest upgrade path?

The various MySql versions are:

+-------------------------+------------------------------+
| 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                        |
+-------------------------+------------------------------+

The plugins between the v5.6.5 and v6.7 instances aren't 100% identical. Some plugins are no longer supported in version 6.x. Based on the error message, I don't think that's the issue, but it is a difference between the two configurations.

Answer 1

The problem was this database was initially created as part of a fresh V6.2 installation. There was a problem with a plugin, so the installation was rolled back to v5.6.5. I don't know if /upgrade had to be invoked during the 'downgrade'. Either way, the upgrade process didn't expect this edge case, so I manually fixed it.

I ran the MySql query show table status; and it showed the perm_tpl_characteristics was empty, so I deleted it. I re-ran the upgrade and another it failed with a similar error for another table. I deleted tables and re-ran the upgrade until it was successful. This is what I ended up with:

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;

I executed these DDL statements on the prod instance and the upgrade was successful. Thank you all for your suggestions. They pointed me in the right direction.

Answer 2

You have to upgrade to the latest 5.X LTS version before you upgrade to 6.X LTS.

The latest 5.X LTS version is 5.6.7 (see release notes), so first you have to upgrade 5.6.5 to 5.6.7 and next to 6.7.