square/keywhiz

Postgres flywaydb migration failing.

sureshg opened this issue · 2 comments

sureshg commented

Just tried to generate Keywhiz JOOQ Model for postgres and it's failing with Found more than one migration with version 3.6 error. As a workaround, I had to rename one of the migration file

$ mv V3.6__alter_clients_to_add_last_seen.sql V3.8__alter_clients_to_add_last_seen.sql

Error,

[INFO] Keywhiz CLI ........................................ SUCCESS [  6.104 s]
[INFO] Keywhiz HKDF ....................................... SUCCESS [  1.001 s]
[INFO] Keywhiz Model ...................................... FAILURE [  5.610 s]
[INFO] Keywhiz Log ........................................ SKIPPED
[INFO] Keywhiz Server ..................................... SKIPPED
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 19.717 s
[INFO] Finished at: 2017-01-08T05:09:43+00:00
[INFO] Final Memory: 43M/466M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:3.2:migrate (default) on project keywhiz-model: org.flywaydb.core.api.FlywayException: Found more than one migration with version 3.6
[ERROR] Offenders:
[ERROR] -> /root/keywhiz/model/../server/src/main/resources/db/postgres/migration/V3.6__add_accessgrants_index.sql (SQL)
[ERROR] -> /root/keywhiz/model/../server/src/main/resources/db/postgres/migration/V3.6__alter_clients_to_add_last_seen.sql (SQL)
[ERROR] -> [Help 1]
org.apache.maven.lifecycle.LifecycleExecutionException: Failed to execute goal org.flywaydb:flyway-maven-plugin:3.2:migrate (default) on project keywhiz-model: org.flywaydb.core.api.FlywayException: Found more than one migration with version 3.6
Offenders:
-> /root/keywhiz/model/../server/src/main/resources/db/postgres/migration/V3.6__add_accessgrants_index.sql (SQL)
-> /root/keywhiz/model/../server/src/main/resources/db/postgres/migration/V3.6__alter_clients_to_add_last_seen.sql (SQL)
	at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:212)
	at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153)
	at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145)
	at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:116)
	at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:80)

The documentation for production setup is really sparse. It would be great to add more documentation on how to do a production setup (eg: Postgres server running on a different machine and how to generate the jooq models)

By the way, I am really surprised to see these kinds of errors 😄

sureshg commented

One more issue, this time is syntax error 😟

[INFO] Keywhiz Model ...................................... FAILURE [  5.432 s]
[INFO] Keywhiz Log ........................................ SKIPPED
[INFO] Keywhiz Server ..................................... SKIPPED
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 19.135 s
[INFO] Finished at: 2017-01-08T05:24:38+00:00
[INFO] Final Memory: 41M/454M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:3.2:migrate (default) on project keywhiz-model: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:
[ERROR] Migration V5__add_index_current_to_secrets.sql failed
[ERROR] -----------------------------------------------------
[ERROR] SQL State  : 42601
[ERROR] Error Code : 0
[ERROR] Message    : syntax error at or near "`"
[ERROR] Location   : /root/keywhiz/model/../server/src/main/resources/db/postgres/migration/V5__add_index_current_to_secrets.sql (/root/keywhiz/model/../server/src/main/resources/db/postgres/migration/V5__add_index_current_to_secrets.sql)
[ERROR] Line       : 1
[ERROR] Statement  : CREATE INDEX secrets_current_idx ON secrets (`current`)
[ERROR] -> [Help 1]
org.apache.maven.lifecycle.LifecycleExecutionException: Failed to execute goal org.flywaydb:flyway-maven-plugin:3.2:migrate (default) on project keywhiz-model: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:
Migration V5__add_index_current_to_secrets.sql failed
-----------------------------------------------------
SQL State  : 42601
Error Code : 0
Message    : syntax error at or near "`"
Location   : /root/keywhiz/model/../server/src/main/resources/db/postgres/migration/V5__add_index_current_to_secrets.sql (/root/keywhiz/model/../server/src/main/resources/db/postgres/migration/V5__add_index_current_to_secrets.sql)
Line       : 1
Statement  : CREATE INDEX secrets_current_idx ON secrets (`current`)

The fix is to remove backticks - CREATE INDEX secrets_current_idx ON secrets (current)

Seems like need to do some testing for dbs except H2.

mcpherrinm commented

See #291

We are planning to remove postgres support as it's unused & untested