db/migration/V4.14.0.1__5822-export-var-meta.sql deployment failures on develop

[donsizemore]

[2019-05-20T18:56:39.496+0000] [glassfish 4.1] [SEVERE] [] [javax.enterprise.system.tools.deployment.common] [tid: _ThreadID=46 _ThreadName=admin-listener(5)] [timeMillis: 1558378599496] [levelValue: 1000] [[ Exception while invoking class org.glassfish.ejb.startup.EjbApplication start method javax.ejb.EJBException: javax.ejb.CreateException: Initialization failed for Singleton StartupFlywayMigrator

Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: Migration V4.14.0.1__5822-export-var-meta.sql failed SQL State : 42701 Error Code : 0 Message : ERROR: column "postquestion" of relation "variablemetadata" already exists Location : db/migration/V4.14.0.1__5822-export-var-meta.sql

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration V4.14.0.1__5822-export-var-meta.sql failed SQL State : 42701 Error Code : 0 Message : ERROR: column "postquestion" of relation "variablemetadata" already exists Location : db/migration/V4.14.0.1__5822-export-var-meta.sql

etc. observed via EC2-create/dataverse-ansible/phoenix

[pdurbin]

As I mentioned at http://irclog.iq.harvard.edu/dataverse/2019-05-20#i_94408 I'm seeing the same problems on the "phoenix" server. The deployment phase does not succeed so the API test phase never runs.

https://build.hmdc.harvard.edu:8443/job/phoenix.dataverse.org-deploy-develop/407/console is an example failure and server.log shows similar lines that @donsizemore is seeing:

Message : ERROR: column "postquestion" of relation "variablemetadata" already exists

Location : db/migration/V4.14.0.1__5822-export-var-meta.sql (/usr/local/glassfish4/glassfish/domains/doma​in1/applications/dataverse/WEB-INF/classes/db​/migration/V4.14.0.1__5822-export-var-meta.sql)

This Flyway script was added in pull request #5833 and I'm wondering... is this the first time we've added a column using Flyway? From a quick look it seems like it is. We must be doing it wrong.

Right now we're doing this:

ALTER TABLE variablemetadata ADD COLUMN postquestion text;

Maybe we need to add some sort of "if doesn't exist" to V4.14.0.1__5822-export-var-meta.sql?

[poikilotherm]

Just a quick shot: this is a new deployment, right? Has the Postgres DB been initialized from scratch? Then the persistence framework might have setup all fields already, causing migration to fail.

See also:

dataverse/src/main/resources/META-INF/persistence.xml

Line 14 in aa59755

<property name="eclipselink.ddl-generation" value="create-tables"/>

From what I read on articles about Flyway + some kind of persistence framework: don't let the framework manage DDL generation for you. Maybe @MrK191 can shed some light on this, too?

Some useful HOWTO/best practices on this: https://rieckpil.de/howto-best-practices-for-flyway-and-hibernate-with-spring-boot Also this is for Hibernate + Spring, not EclipseLink + (Java|Jakarta) EE, strategy should be the same in both cases... 😉

[pdurbin]

According to https://stackoverflow.com/questions/12597465/how-to-add-column-if-not-exists-on-postgresql/38721951#38721951 as of PostgreSQL 9.6 we can use syntax like this:

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;

So, for us I guess that would be

ALTER TABLE variablemetadata ADD COLUMN IF NOT EXISTS postquestion text;

[MrK191]

@poikilotherm Yes exactly, <property name="eclipselink.ddl-generation" value="create-tables"/> needs to be value="none" otherwise it will fail since eclipselink will generate and execute sql, and we want only flyway to do that.

[poikilotherm]

As requested by @pdurbin on IRC today, I'll open another issue for this. See #5871

#5833 contains a quick fix/workaround, but might get refactored depending on the results from the new issue.

[pdurbin]

As discussed in http://irclog.iq.harvard.edu/dataverse/2019-05-21 a pull request with the quick fix suggested above doesn't exist yet but @donsizemore is going to make one. Thanks!!

@poikilotherm @MrK191 thanks for your feedback and please post the new issue here when you're ready. Please note that in pull request #5858 we recently decided to stop making the "create" scripts, the dataverse_VDCNet-ejbPU_createDDL.jdbc file but if these create scripts are needed for what you're envisioning in the new issue, you might want to spell this out. Thanks!!

[djbrooke]

Big thanks @MrK191 @donsizemore @poikilotherm and @pdurbin for the troubleshooting and the fix!

[pdurbin]

Pull request #5866 looks like exactly what I was suggesting above as a quick fix. Thanks, @donsizemore . I hope it works.

To test: try to install Dataverse fresh from the develop branch. It should fail with the errors above. Then try the pull request above. Hopefully it works.

[donsizemore]

@pdurbin I tested it in EC2 against my fork and branch; I encourage checking behind me!