upgrading AWS Redshift DB from Flyway 3.2.1 to 4.0 failed - couldn't upgrade metadata table

[wgillett]

What version of Flyway are you using?

3.2.1

What database are you using (type & version)?

AWS Redshift

What operating system are you using?

Linux

What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)

Changed my code to use Flyway 4.0. On calling Flyway#migrate, Flyway tried to upgrade and failed (details below).

What did you expect to see?

Expected to see successful Flyway init.

What did you see instead?

Flyway init failed:

2016-03-03 23:09:14,251 INFO [main] o.f.c.i.m.MetaDataTableImpl - Upgrading metadata table "public"."schema_version" to the Flyway 4.0 format
...
org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:

Script failed

SQL State : XX000
Error Code : 500310
Message : Amazon Invalid operation: cannot insert/update into table after dropping non-nullable column;
Line : 20
Statement : UPDATE "public"."schema_version" SET "version_temp"="version"
at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)
at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.upgradeIfNecessary(MetaDataTableImpl.java:79)
at org.flywaydb.core.Flyway.execute(Flyway.java:1356)
at org.flywaydb.core.Flyway.migrate(Flyway.java:917)

[axelfontaine]

@nathanvick Can you have a look at this? Thanks!

[nathanvick]

Can you tell me what version of Redshift is being used? The error is not familiar to me, but it makes me wonder if it is an old version of Redshift. I have been dropping not null columns and then updating data for a couple years.

http://docs.aws.amazon.com/redshift/latest/dg/r_VERSION.html

PS: I tested this with flyway 4 (command line), upgrading from flyway 3.2 (command line), and it worked for me, using both the Postgres jdbc driver and the Redshift jdbc driver.

[wgillett]

Hmmm. Not aware there was a choice of Redshift version, it's SaaS. See below for version info nonetheless. I'm invoking Flyway through the API rather than the command line.

Which version of the Redshift driver are you using? On the download page there is a JDBC 4.0 driver and a 4.1 driver. We are using version 41.1.1.7.1007. I see there is a more recent driver version available, will see if the newer driver works any better.

Sounds like not much more you can do on your side. Thanks for looking into it,

Walter

select version; returns:

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1036

[nathanvick]

It is possible to refuse major version upgrades...

"Amazon Redshift provides a setting, Allow Version Upgrade, to specify whether to automatically upgrade the Amazon Redshift engine in your cluster if a new version of the engine becomes available. This setting does not affect the database version upgrades, which are applied during the maintenance window that you specify for your cluster. Amazon Redshift engine upgrades are major version upgrades, and Amazon Redshift database upgrades are minor version upgrades. You can disable automatic version upgrades for major versions only. For more information about maintenance windows for minor version upgrades, see Maintenance Windows."

http://docs.aws.amazon.com/redshift/latest/mgmt/working-with-clusters.html

...but I don't think that's the case here. Unfortunately, I can't confirm what version of Redshift I was using because I accepted a new position at a different company, and my last day was today.

I'm still confused as to why you hit this error and I didn't. Perhaps the best solution is to change tge migration to the following.

• Rename old metadata table (so as to lock it)
• Create new metadata table
• Copy data to new metadata table
• Drop old metadata table

[wgillett]

This AWS forum thread cannot insert/update into table after dropping non-nullable column looks relevant. Because the AWS forums require a login, I'm including the content below. Will try including a commit in the upgrade script and see if that helps.

"Hi, I was running a migration against my Redshift database and found a weird behavior- running all the migrations in one shot fails with the message

cannot insert/update into table after dropping non-nullable column

while running them individually succeeds. It turns out that two of our migrations modify a column on the same table- by adding a temp column, copying data to the temp column, and dropping the old column. For some reason, dropping the column then puts some kind of restriction on the db that we can't add data to that table until we do a commit. Running a commit manually gets things to work again, but we don't understand the purpose of this restriction, and couldn't find anything in the docs to explain exactly what is going on (running the same script against postgres works)"

[nathanvick]

Ok, thanks for the details from the forum post. Please let me know how the commit workaround goes.

However, I still think creating a new table would be a better solution because if the migration fails for any reason, it can all be rolled back, whereas committing midway would prevent that.

[wgillett]

That is a good point. I'll try your suggestion. Having trouble building Flyway because of MS SQL jar, which I understand can't be mavenized publicly because of licensing issues, but should be able to locally install it.

[wgillett]

I could run the script standalone but if I do it as part of Flyway I'll be able to submit a pull request.

[wgillett]

Possible for me to test a change to flyway-core/src/main/resources/org/flywaydb/core/internal/dbsupport/redshift/upgradeMetaDataTable.sql without building Flyway at the top level? I've manually installed MS SQL Server jar and the Oracle driver jar, now the build is failing on the IBM jar, fearing that it's going to take a while to work through all these proprietary drivers that I don't actually need for this test. Tried building flyway-core by itself but that has the same problem.

[axelfontaine]

You can build Flyway with -DskipTests and run the Redshift tests in your IDE

[nathanvick]

I made a suggested fix here: nathanvick@4f0b1d8

I hope to be able to test it out sometime this week.

[wgillett]

Thanks! Just tried it & there is a problem. I was able to fix the problem but not sure my fix is generally acceptable. Nathan, see my comment on your commit for details.

[axelfontaine]

@nathanvick Can you submit a PR for this? Thanks!

[nathanvick]

@alexfontaine,

Sure, I'll try to get to it this week.