How to do an upgrade from 0.8.0 to 0.10.1 - Database issue - Migration checksum mismatch for migration version 1

[FlavioImbertDomingos]

Hi Square Team,

We have a big deployment of Keywhiz (over 23 servers) running 0.8.0. I am trying to test the upgrade and it is not easy because the Java 11 change and the DAtabase changes as well.

How you do this upgrade? Do you have any tip or steps that I can do to make it possible? Is my only option to drop the existing database, create a new db with the migrate and some how restore the data from the old database to the new one?

Here is what is happening when I try to start or migrate:

$ java -cp /usr/safenet/lunaclient/jsp/lib/LunaProvider.jar:./keywhiz-server-0.10.1-shaded.jar -Djava.library.path=/usr/safenet/lunaclient/jsp/lib/ keywhiz.KeywhizService server ./keywhiz-config.yml

WARNING: An illegal reflective access operation has occurred WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/keywhiz-server-0.10.1-shaded.jar) to method java.lang.ClassLoader.findLoadedClass(java.lang.String) WARNING: Please consider reporting this to the maintainers of com.fasterxml.jackson.module.afterburner.util.MyClassLoader WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations WARNING: All illegal access operations will be denied in a future release org.flywaydb.core.api.FlywayException: Validate failed: Migration checksum mismatch for migration version 1 -> Applied to database : -1043629835 -> Resolved locally : -118804294 at org.flywaydb.core.Flyway.doValidate(Flyway.java:1482) at org.flywaydb.core.Flyway.access$100(Flyway.java:85) at org.flywaydb.core.Flyway$2.execute(Flyway.java:1455) at org.flywaydb.core.Flyway$2.execute(Flyway.java:1448) at org.flywaydb.core.Flyway.execute(Flyway.java:1711) at org.flywaydb.core.Flyway.validate(Flyway.java:1448) at keywhiz.KeywhizService.validateDatabase(KeywhizService.java:171) at keywhiz.KeywhizService.run(KeywhizService.java:159) at keywhiz.KeywhizService.run(KeywhizService.java:71) at io.dropwizard.cli.EnvironmentCommand.run(EnvironmentCommand.java:43) at io.dropwizard.cli.ConfiguredCommand.run(ConfiguredCommand.java:87) at io.dropwizard.cli.Cli.run(Cli.java:78) at io.dropwizard.Application.run(Application.java:93) at keywhiz.KeywhizService.main(KeywhizService.java:78

This is what I see in the schema_version of my 0.8 deployment...

[keywhiz_stg]> select * from schema_version; +--------------+----------------+---------+----------------------------------------+------+--------------------------------------------------+-------------+--------------+---------------------+----------------+---------+ | version_rank | installed_rank | version | description | type | script | checksum | installed_by | installed_on | execution_time | success | +--------------+----------------+---------+----------------------------------------+------+--------------------------------------------------+-------------+--------------+---------------------+----------------+---------+ | 1 | 1 | 1 | create tables | SQL | V1create_tables.sql | -1043629835 | root | 2017-05-10 16:09:13 | 170 | 1 | | 2 | 2 | 1.1 | alter secret content to add expiry | SQL | V1.1__alter_secret_content_to_add_expiry.sql | -657209746 | root | 2017-05-10 16:09:13 | 40 | 1 | | 3 | 3 | 1.2 | alter secret content to remove version | SQL | V1.2alter_secret_content_to_remove_version.sql | 1602621448 | root | 2017-05-10 16:09:13 | 37 | 1 | | 4 | 4 | 1.3 | alter secrets to add current | SQL | V1.3alter_secrets_to_add_current.sql | -533763817 | root | 2017-05-10 16:09:13 | 18 | 1 | | 5 | 5 | 1.4 | add indexes | SQL | V1.4add_indexes.sql | 1723007867 | root | 2017-05-10 16:09:13 | 32 | 1 | | 6 | 6 | 1.5 | alter groups to add metadata | SQL | V1.5alter_groups_to_add_metadata.sql | -327466309 | root | 2017-05-10 16:09:13 | 20 | 1 | | 7 | 7 | 1.6 | add accessgrants index | SQL | V1.6add_accessgrants_index.sql | 195441156 | root | 2017-05-10 16:09:13 | 13 | 1 | | 8 | 8 | 1.7 | alter clients to add last seen | SQL | V1.7alter_clients_to_add_last_seen.sql | 327844707 | root | 2017-05-10 16:09:14 | 261 | 1 | | 9 | 9 | 4 | alter secret content to add hmac | SQL | V4__alter_secret_content_to_add_hmac.sql | 1486396107 | root | 2017-05-10 16:09:14 | 26 | 1 | | 10 | 10 | 5 | add index current to secrets | SQL | V5add_index_current_to_secrets.sql | 1338499923 | root | 2017-05-10 16:09:14 | 13 | 1 | | 11 | 11 | 6 | drop unique constraint on secret name | SQL | V6__drop_unique_constraint_on_secret_name.sql | -1693049885 | gpskwstg | 2017-08-03 18:31:38 | 218 | 1 |

Any idea how I can perform this upgrade from 0.8.0 to 0.10.1?

Thank you so much,

Flavio.

[mbyczkowski]

Did you try to migrate the database first? e.g. java -jar $SERVER_JAR migrate $KEYWHIZ_CONFIG?

At Square, we apply schema changes in a separate system (not via Flyway), but this should still work. In general, we update Keywhiz quite often, so I might 100% what effect it would have to run migration first, while 0.8 code is still running -- would have to look closer at the list of migrations.

[FlavioImbertDomingos]

Hi Mat,

I have a local db running with a dump from the stg cluster. the idea is see what happen with migrate.

url: jdbc:mysql://127.0.0.1/keywhiz_stg?useUnicode=true&characterEncoding=utf8

But when I run "migrate", I get the same error:

$ java -cp /usr/safenet/lunaclient/jsp/lib/LunaProvider.jar:./keywhiz-server-0.10.1-shaded.jar -Djava.library.path=/usr/safenet/lunaclient/jsp/lib/ keywhiz.KeywhizService migrate ./keywhiz-stg.yml

WARNING: An illegal reflective access operation has occurred WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/keywhiz-server-0.10.1-shaded.jar) to method java.lang.ClassLoader.findLoadedClass(java.lang.String) WARNING: Please consider reporting this to the maintainers of com.fasterxml.jackson.module.afterburner.util.MyClassLoader WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations WARNING: All illegal access operations will be denied in a future release org.flywaydb.core.api.FlywayException: Validate failed: Migration checksum mismatch for migration version 1 -> Applied to database : -1043629835 -> Resolved locally : -118804294

Right now, I can only think to make this upgrade happen is creating a new DB; run the migrate and somehow restore all the records/data from the existing db (0.8) to the new database (0.10). I need to find an easiest way to do this since I am concerned about our production data since our system is pretty mush very busy.

Thank you again Mat for any tip or idea.

[mbyczkowski]

What if you got an older version of Keywhiz? Could you check out earlier commit, maybe version 0.9.0 and try to run migration with that?

[FlavioImbertDomingos]

Hi @mbyczkowski :)

Yeah, I tried to build the 0.9.0 as well :) It is having an issue to build keywhiz-test... because the build can't find this file to download:

http://maven.java.net/content/repositories/releases/com/sun/istack/istack-commons-runtime/3.0.6/istack-commons-runtime-3.0.6.pom

See the log:

[root@keywhiz keywhiz-0.9.0]# mvn clean install [INFO] Scanning for projects... [WARNING] [WARNING] Some problems were encountered while building the effective model for com.squareup.keywhiz:keywhiz-api:jar:0.9.0 [WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-gpg-plugin is missing. @ com.squareup.keywhiz:keywhiz-parent:0.9.0, /home/flavio/keywhiz-0.9.0/pom.xml, line 337, column 15 [WARNING] [WARNING] Some problems were encountered while building the effective model for com.squareup.keywhiz:keywhiz-client:jar:0.9.0 [WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-gpg-plugin is missing. @ com.squareup.keywhiz:keywhiz-parent:0.9.0, /home/flavio/keywhiz-0.9.0/pom.xml, line 337, column 15 [WARNING] [WARNING] Some problems were encountered while building the effective model for com.squareup.keywhiz:keywhiz-cli:jar:0.9.0 [WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-gpg-plugin is missing. @ com.squareup.keywhiz:keywhiz-parent:0.9.0, /home/flavio/keywhiz-0.9.0/pom.xml, line 337, column 15 [WARNING] [WARNING] Some problems were encountered while building the effective model for com.squareup.keywhiz:keywhiz-hkdf:jar:0.9.0 [WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-gpg-plugin is missing. @ com.squareup.keywhiz:keywhiz-parent:0.9.0, /home/flavio/keywhiz-0.9.0/pom.xml, line 337, column 15 [WARNING] [WARNING] Some problems were encountered while building the effective model for com.squareup.keywhiz:keywhiz-model:jar:0.9.0 [WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-gpg-plugin is missing. @ com.squareup.keywhiz:keywhiz-parent:0.9.0, /home/flavio/keywhiz-0.9.0/pom.xml, line 337, column 15 [WARNING] [WARNING] Some problems were encountered while building the effective model for com.squareup.keywhiz:keywhiz-server:jar:0.9.0 [WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-gpg-plugin is missing. @ com.squareup.keywhiz:keywhiz-parent:0.9.0, /home/flavio/keywhiz-0.9.0/pom.xml, line 337, column 15 [WARNING] [WARNING] Some problems were encountered while building the effective model for com.squareup.keywhiz:keywhiz-testing:jar:0.9.0 [WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-gpg-plugin is missing. @ com.squareup.keywhiz:keywhiz-parent:0.9.0, /home/flavio/keywhiz-0.9.0/pom.xml, line 337, column 15 [WARNING] [WARNING] Some problems were encountered while building the effective model for com.squareup.keywhiz:keywhiz-log:jar:0.9.0 [WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-gpg-plugin is missing. @ com.squareup.keywhiz:keywhiz-parent:0.9.0, /home/flavio/keywhiz-0.9.0/pom.xml, line 337, column 15 [WARNING] [WARNING] Some problems were encountered while building the effective model for com.squareup.keywhiz:keywhiz-parent:pom:0.9.0 [WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-gpg-plugin is missing. @ line 337, column 15 [WARNING] [WARNING] It is highly recommended to fix these problems because they threaten the stability of your build. [WARNING] [WARNING] For this reason, future Maven versions might no longer support building such malformed projects. [WARNING] [WARNING] The project com.squareup.keywhiz:keywhiz-parent:pom:0.9.0 uses prerequisites which is only intended for maven-plugin projects but not for non maven-plugin projects. For such purposes you should use the maven-enforcer-plugin. See https://maven.apache.org/enforcer/enforcer-rules/requireMavenVersion.html [INFO] ------------------------------------------------------------------------ [INFO] Reactor Build Order: [INFO] [INFO] Keywhiz (Parent) [pom] [INFO] Keywhiz Testing [jar] [INFO] Keywhiz API [jar] [INFO] Keywhiz Client [jar] [INFO] Keywhiz CLI [jar] [INFO] Keywhiz HKDF [jar] [INFO] Keywhiz Model [jar] [INFO] Keywhiz Log [jar] [INFO] Keywhiz Server [jar] [INFO] [INFO] ----------------< com.squareup.keywhiz:keywhiz-parent >----------------- [INFO] Building Keywhiz (Parent) 0.9.0 [1/9] [INFO] --------------------------------[ pom ]--------------------------------- [INFO] [INFO] --- maven-clean-plugin:2.5:clean (default-clean) @ keywhiz-parent --- [INFO] [INFO] --- maven-enforcer-plugin:1.2:enforce (enforce-unlimited-crypto-policy) @ keywhiz-parent --- [INFO] [INFO] --- maven-enforcer-plugin:1.2:enforce (enforce-maven) @ keywhiz-parent --- [INFO] [INFO] --- jacoco-maven-plugin:0.8.2:prepare-agent (prepare-agent) @ keywhiz-parent --- [INFO] argLine set to -javaagent:/root/.m2/repository/org/jacoco/org.jacoco.agent/0.8.2/org.jacoco.agent-0.8.2-runtime.jar=destfile=/home/fdomin1/keywhiz-0.9.0/target/jacoco.exec,excludes=*/AutoValue:*/keywhiz/jooq/ [INFO] [INFO] --- maven-dependency-plugin:3.1.1:copy-dependencies (copy) @ keywhiz-parent --- [INFO] [INFO] --- maven-source-plugin:3.0.1:jar-no-fork (attach-sources) @ keywhiz-parent --- [INFO] [INFO] --- maven-install-plugin:2.4:install (default-install) @ keywhiz-parent --- [INFO] Installing /home/fdomin1/keywhiz-0.9.0/pom.xml to /root/.m2/repository/com/squareup/keywhiz/keywhiz-parent/0.9.0/keywhiz-parent-0.9.0.pom [INFO] [INFO] ----------------< com.squareup.keywhiz:keywhiz-testing >---------------- [INFO] Building Keywhiz Testing 0.9.0 [2/9] [INFO] --------------------------------[ jar ]--------------------------------- Downloading from central: https://repo.maven.apache.org/maven2/com/sun/istack/istack-commons-runtime/3.0.6/istack-commons-runtime-3.0.6.pom Downloading from releases.java.net: http://maven.java.net/content/repositories/releases/com/sun/istack/istack-commons-runtime/3.0.6/istack-commons-runtime-3.0.6.pom

[FlavioImbertDomingos]

Okay, now I am trying migrate in a empty database (new DB, no table there yet) and I am getting this:

$ java -jar ./keywhiz-server-0.10.1-shaded.jar migrate ./keywhiz-stg.yml WARNING: An illegal reflective access operation has occurred WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/keywhiz-server-0.10.1-shaded.jar) to method java.lang.ClassLoader.findLoadedClass(java.lang.String) WARNING: Please consider reporting this to the maintainers of com.fasterxml.jackson.module.afterburner.util.MyClassLoader WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations WARNING: All illegal access operations will be denied in a future release [keywhiz@keywhiz-233048331-1-258246470 opt]$

$ java --version openjdk 11.0.3 2019-04-16 LTS OpenJDK Runtime Environment 18.9 (build 11.0.3+7-LTS)

Do you know why I am getting this error?

Thank you so much,

Flavio.

[mbyczkowski]

@FlavioImbertDomingos I talked to @jbpeirce who remembered some of migration issues that we came across. IIUC, there was some breaking change in Flyway (3.x -> 4.x but would need to double check) and we ended up manually changing checksum in our DB some time in early 2018.

With regards to your latest comment, I see warnings in the logs but not actual error. Did the migration on the empty DB work as expected or did it stop half-way?

[FlavioImbertDomingos]

Thanks @mbyczkowski

Regarding my last comment... I was trying to do a test with an empty database.

I have a new mysql database and I was trying to run "migrate" for the first time

$ java --illegal-access=warn -jar ./keywhiz-server-0.10.1-shaded.jar migrate ./keywhiz-stg.yml

WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/keywhiz-server-0.10.1-shaded.jar) to method java.lang.ClassLoader.findLoadedClass(java.lang.String) WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/keywhiz-server-0.10.1-shaded.jar) to method java.lang.ClassLoader.defineClass(java.lang.String,byte[],int,int) WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/keywhiz-server-0.10.1-shaded.jar) to method java.lang.ClassLoader.resolveClass(java.lang.Class) ... WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/keywhiz-server-0.10.1-shaded.jar) to method java.lang.ClassLoader.findLoadedClass(java.lang.String) WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/keywhiz-server-0.10.1-shaded.jar) to method java.lang.ClassLoader.defineClass(java.lang.String,byte[],int,int) WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/keywhiz-server-0.10.1-shaded.jar) to method java.lang.ClassLoader.resolveClass(java.lang.Class)

Am I missing anything?

[FlavioImbertDomingos]

Thanks @mbyczkowski :) Hi @jbpeirce :) This is the first time we are planning to upgrade after 2 or 3 years.. We met at Square I believe 3 years ago (Suresh, Luis, ..) you probably remember :) Anyways, since I am not a Java developer (unfortunately), I am trying to figure out how I can "manually changing checksum in our DB ". Should I just update the checksum table/field in the database? Do you have in the top of you head the steps that I should do to be able to execute the following:

$ java -cp /usr/safenet/lunaclient/jsp/lib/LunaProvider.jar:./keywhiz-server-0.10.1-shaded.jar -Djava.library.path=/usr/safenet/lunaclient/jsp/lib/ keywhiz.KeywhizService migrate ./keywhiz-stg.yml

WARNING: An illegal reflective access operation has occurred WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/keywhiz-server-0.10.1-shaded.jar) to method java.lang.ClassLoader.findLoadedClass(java.lang.String) WARNING: Please consider reporting this to the maintainers of com.fasterxml.jackson.module.afterburner.util.MyClassLoader WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations WARNING: All illegal access operations will be denied in a future release org.flywaydb.core.api.FlywayException: Validate failed: Migration checksum mismatch for migration version 1 -> Applied to database : -1043629835 -> Resolved locally : -118804294

Thank you so much!

Flavio.