Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Flyway 4.0.1 Repeatable migrations running before versioned. #1313

Closed
hartrc opened this issue May 18, 2016 · 24 comments
Closed

Flyway 4.0.1 Repeatable migrations running before versioned. #1313

hartrc opened this issue May 18, 2016 · 24 comments

Comments

@hartrc
Copy link

@hartrc hartrc commented May 18, 2016

What version of Flyway are you using?

4.0.1

What database are you using (type & version)?

PostgreSQL 9.5.1

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)
Ran a migration that included both versioned and repeatable. We are using default flyway properties.

What did you expect to see?

Documentation states the versioned migrations always run first then the repeatable.
The repeatable ran first. The repeatable was dependent on the versioned therefore it failed.

What did you see instead?

INFO: Database: jdbc:postgresql://x.x.x.x/dw (PostgreSQL 9.5)
Pending Migrations:
    null - load daily account facts fnc
    116.1 - sofi 9722 qc fraud ddl
    117.1 - add-new-wealth-columns-for-email-marketing
May 18, 2016 9:43:01 PM org.flywaydb.core.internal.command.DbMigrate migrate
INFO: Current version of schema "dw": 115.8
May 18, 2016 9:43:01 PM org.flywaydb.core.internal.command.DbMigrate applyMigration
INFO: Migrating schema "dw" with repeatable migration load daily account facts fnc
May 18, 2016 9:43:02 PM org.flywaydb.core.internal.command.DbMigrate applyMigration
SEVERE: Migration of schema "dw" with repeatable migration load daily account facts fnc failed! Changes successfully rolled back.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented May 20, 2016

Could you provide a small test project repo to reproduce this?

@hartrc
Copy link
Author

@hartrc hartrc commented May 23, 2016

I've now been able to reproduce this on my local whereas previously i was only encountering on our production server. It seems like the version is incorrect like it is somehow using the created date.

May 23, 2016 8:05:06 AM org.flywaydb.core.internal.util.VersionPrinter printVersion
INFO: Flyway 4.0.1 by Boxfuse
May 23, 2016 8:05:06 AM org.flywaydb.core.internal.dbsupport.DbSupportFactory createDbSupport
INFO: Database: jdbc:postgresql://localhost/sofi_dw (PostgreSQL 9.5)
Pending Migrations: 
    null - load uw data fnc
    20160523094713 - create-underwriting-credit-facts-table
Current Version: 120.1 - create-credit-pull-tables

It appears to me that the migration trys to migrate in whatever order they are in the deploy. In this case load uw data fnc is an existing repeatable migration that I have changed. If I create a new repeatable migration then it comes last:

INFO: Flyway 4.0.1 by Boxfuse
May 23, 2016 8:08:25 AM org.flywaydb.core.internal.dbsupport.DbSupportFactory createDbSupport
INFO: Database: jdbc:postgresql://localhost/sofi_dw (PostgreSQL 9.5)
Pending Migrations: 
    null - load uw data fnc
    20160523094713 - create-underwriting-credit-facts-table
    null - test
Current Version: 120.1 - create-credit-pull-tables
[success] Total time: 2 s, completed May 23, 2016 8:08:27 AM

I can force my (load uw data fnc) repeatable migration to come last if i delete it's current entry from the schema_version table (obviously not going to play with that in prod!).

delete from schema_version
where installed_rank=294;

INFO: Database: jdbc:postgresql://localhost/sofi_dw (PostgreSQL 9.5)
Pending Migrations: 
    20160523094713 - create-underwriting-credit-facts-table
    null - load uw data fnc
    null - test
Current Version: 120.1 - create-credit-pull-tables
[success] Total time: 2 s, completed May 23, 2016 8:12:41 AM

One other thing to note is that our schema_version table does not have a "State" column. This showed in the https://flywaydb.org/blog/flyway-4.0 but that could be Database specific or redundant?

@nateww
Copy link

@nateww nateww commented May 24, 2016

See #1320 for a potential fix for this (verified that this fixes the issue in our test case).

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented May 24, 2016

As stated in my earlier comment, please provide a test repo that lets us reliably reproduce this issue. Thanks!

@nateww
Copy link

@nateww nateww commented May 24, 2016

What format would you like the repo? Our test repos depend on features of our environment (including setup that makes Flyway easier for us to use), that are not necessarily straight-forward to someone external.

Do you have an example of what you are looking for?

@nateww
Copy link

@nateww nateww commented May 25, 2016

Ping! Would love to provide you what you are looking for, but don't know what that is...

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented May 26, 2016

Either a simple GitHub repo containing migration files and instructions or a test case as part of your PR.

@Nthalk
Copy link

@Nthalk Nthalk commented May 31, 2016

Been bit by this myself, I had repeatable migrations that depended on versioned migrations and the repeatable was running, and failing before the pending versioned script:

+---------+--------------------------------------------+---------------------+---------+
| Version | Description                                | Installed on        | State   |
+---------+--------------------------------------------+---------------------+---------+
|         | ZZZ D ROUTINE ORDERING                     | 2016-05-31 10:30:01 | Failed  |
| 00063   | update install routine                     |                     | Pending |

@markus-s24
Copy link

@markus-s24 markus-s24 commented Jun 8, 2016

Because this bug is still "waiting for feedback" here are my steps to reproduce this bug:

  1. Create a versioned migration (V1) and a repeatable migration (R1).
  2. Execute Flyway migration. R1 will be executed after V1 -> so far OK.
  3. Modify R1, create a new versioned migration (V2)
  4. Execute Flyway migration. Now R1 will be reapplied before V2 -> Bug.

@davidkarlsen
Copy link

@davidkarlsen davidkarlsen commented Jun 8, 2016

We see the same:

[INFO] Flyway 4.0.1 by Boxfuse
[INFO] Database: jdbc:oracle:thin:@localhost:1521/orcl (Oracle 12.1)
[INFO] Successfully validated 122 migrations (execution time 00:00.065s)
[INFO] Current version of schema "APPDATA": 69
[INFO] Migrating schema "APPDATA" with repeatable migration proc due date processing
[INFO] Migrating schema "APPDATA" with repeatable migration sto api pkg
[INFO] Migrating schema "APPDATA" with repeatable migration sto auth pkg
[INFO] Migrating schema "APPDATA" with repeatable migration sto hist pkg
[INFO] Migrating schema "APPDATA" to version 69.1 - Pwh so add account related columns to paymentinfo
[INFO] Migrating schema "APPDATA" to version 69.2 - Pwh so foreignkey update to recurrece exclude
[INFO] Migrating schema "APPDATA" to version 69.3 - pwh create error log table
[INFO] Migrating schema "APPDATA" with repeatable migration errapi pkg
[INFO] Migrating schema "APPDATA" with repeatable migration errmsg pkg
[INFO] Migrating schema "APPDATA" with repeatable migration sto c pkg
[INFO] Successfully applied 10 migrations to schema "APPDATA" (execution time 00:02.087s).
[INFO] Executing SQL callback: afterMigrate

The repeatables should go after the versions according to documentation.

axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Jun 9, 2016
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jun 9, 2016

@markus-s24 @davidkarlsen Thanks for the steps! That was very helpful!

@axelfontaine axelfontaine reopened this Jun 9, 2016
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jun 9, 2016

Beware: As @nateww rightly pointed out, it would appear my "fix" for 4.0.2 actually causes even more trouble than before!

@nateww
Copy link

@nateww nateww commented Jun 9, 2016

The new fix does the opposite of what we desire.

09-Jun-2016 15:53:42 INFO: Database: jdbc:postgresql://localhost:20601/sofi_dw (PostgreSQL 9.5)
09-Jun-2016 15:53:42 Pending Migrations: 
09-Jun-2016 15:53:43 null - add commonline id fnc
09-Jun-2016 15:53:43 null - audit insert update fnc
09-Jun-2016 15:53:43 null - conv addr fnc
09-Jun-2016 15:53:43 null - conv borr fnc
09-Jun-2016 15:53:43 null - conv phone fnc
09-Jun-2016 15:53:43 null - convert loan status fnc
09-Jun-2016 15:53:43 null - create metro2 file fnc
09-Jun-2016 15:53:43 null - create metro2 file for audit fnc
09-Jun-2016 15:53:43 null - create metro2 transunion file fnc
09-Jun-2016 15:53:43 null - fix dupes fnc
09-Jun-2016 15:53:43 null - fixer fnc
09-Jun-2016 15:53:43 null - get balance asof date
09-Jun-2016 15:53:43 null - get daily loan facts asof date fnc
09-Jun-2016 15:53:43 null - get function sql
09-Jun-2016 15:53:43 null - get last libor change date fnc
09-Jun-2016 15:53:43 null - get loan info asof date fnc
09-Jun-2016 15:53:43 null - get status asof date fnc
09-Jun-2016 15:53:43 null - get wa loan bal fnc
09-Jun-2016 15:53:43 null - is leap year fnc
09-Jun-2016 15:53:43 null - load account marketing facts accounts fnc
09-Jun-2016 15:53:43 null - load account marketing facts affilates fnc
09-Jun-2016 15:53:43 null - load account status history fnc
09-Jun-2016 15:53:43 null - load accounts fnc
09-Jun-2016 15:53:43 null - load accrued int fnc
09-Jun-2016 15:53:43 null - load app data fnc
09-Jun-2016 15:53:43 null - load app status fnc
09-Jun-2016 15:53:43 null - load approved submit fnc
09-Jun-2016 15:53:43 null - load bond id fnc
09-Jun-2016 15:53:43 null - load borr demo fnc
09-Jun-2016 15:53:43 null - load borrower current uw data fnc
09-Jun-2016 15:53:43 null - load campaign fnc
09-Jun-2016 15:53:43 null - load cash noncash files fnc
09-Jun-2016 15:53:43 null - load credit consent fnc
09-Jun-2016 15:53:43 null - load credit pull fnc
09-Jun-2016 15:53:43 null - load daily account facts fnc
09-Jun-2016 15:53:43 null - load daily fact fnc fixer
09-Jun-2016 15:53:43 null - load daily file fnc
09-Jun-2016 15:53:43 null - load daily file missing fnc
09-Jun-2016 15:53:43 null - load daily file missing fnc run date
09-Jun-2016 15:53:43 null - load daily pl file fnc
09-Jun-2016 15:53:43 null - load daily pl transaction fnc
09-Jun-2016 15:53:43 null - load daily transactions fnc
09-Jun-2016 15:53:43 null - load daily transactions fnc run date
09-Jun-2016 15:53:43 null - load dates fnc
09-Jun-2016 15:53:43 null - load disclosure fnc
09-Jun-2016 15:53:43 null - load dob fnc
09-Jun-2016 15:53:43 null - load employees fnc
09-Jun-2016 15:53:43 null - load expected payoff fnc
09-Jun-2016 15:53:43 null - load fico date fnc
09-Jun-2016 15:53:43 null - load final uw fnc
09-Jun-2016 15:53:43 null - load initial uw decision fnc
09-Jun-2016 15:53:43 null - load ln90 data daily fnc
09-Jun-2016 15:53:43 null - load ln90 data fnc
09-Jun-2016 15:53:43 null - load mlo underwriter assigned fnc
09-Jun-2016 15:53:43 null - load mort fund info fnc
09-Jun-2016 15:53:43 null - load mr50 data fnc
09-Jun-2016 15:53:43 null - load omlf shell fnc
09-Jun-2016 15:53:43 null - load owner wa fnc
09-Jun-2016 15:53:43 null - load owner wa mohela after sale fnc
09-Jun-2016 15:53:43 null - load partner affiliation fnc
09-Jun-2016 15:53:43 null - load pl monthly fnc
09-Jun-2016 15:53:43 null - load product data fnc
09-Jun-2016 15:53:43 null - load school data fnc
09-Jun-2016 15:53:43 null - load selected offer fnc
09-Jun-2016 15:53:43 null - load tracking pmt fnc
09-Jun-2016 15:53:43 null - load ts cash noncash files fnc
09-Jun-2016 15:53:43 null - load uw data fnc
09-Jun-2016 15:53:43 null - process multiple owner sale fnc
09-Jun-2016 15:53:43 null - qc check flag fnc
09-Jun-2016 15:53:43 null - qc check fnc
09-Jun-2016 15:53:43 null - remove funded loan fnc
09-Jun-2016 15:53:43 null - set funded date existing loans fnc
09-Jun-2016 15:53:43 null - test create function
09-Jun-2016 15:53:43 null - update ln90 staging rev fnc
09-Jun-2016 15:53:43 null - update ln90 staging rev fnc date
09-Jun-2016 15:53:43 null - update ssn fnc
09-Jun-2016 15:53:43 null - update ssn history fnc
09-Jun-2016 15:53:43 1.1 - initial-DDL
09-Jun-2016 15:53:43 1.2 - update loan info function
09-Jun-2016 15:53:43 2.1 - credit consent date
09-Jun-2016 15:53:43 2.2 - credit consent date function
09-Jun-2016 15:53:43 2.3 - load app status fnc modify
09-Jun-2016 15:53:43 3.1 - loan sale owner percent
09-Jun-2016 15:53:43 4.1 - daily file col change
09-Jun-2016 15:53:43 5.1 - mlo-and-underwriter-assigned
09-Jun-2016 15:53:43 5.2 - create-mlo-underwriter-staging-table
09-Jun-2016 15:53:43 5.3 - create-employees-staging
09-Jun-2016 15:53:43 5.4 - create-load-employees-fnc
09-Jun-2016 15:53:43 5.5 - load-mlo-underwriter-assigned-fnc
09-Jun-2016 15:53:43 6.1 - additional-mortgage-data-add-tables-and-columns
09-Jun-2016 15:53:43 6.2 - mortgage-data-modify-load-functions
09-Jun-2016 15:53:43 7.1 - daily transaction new table
09-Jun-2016 15:53:43 7.2 - daily transaction load function
09-Jun-2016 15:53:43 8.1 - drop-redundant-tables-and-functions
09-Jun-2016 15:53:43 9.1 - create tables
...

With a brand-new DB, now all repeatable migrations are first, and non-repeatable are after. Previously (V4.0.1) it worked fine with a brand-new database, but failed after-thefact when a new repeatable migration and versioned migration was added at the same. With the fix I proposed in #1302 , it worked in both situations, but now it doesn't. :(

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jun 9, 2016

@nateww @markus-s24 @davidkarlsen @Nthalk @hartrc Please test the latest fix and confirm it works so we can get 4.0.3 out the door ASAP. Thanks!

@nateww
Copy link

@nateww nateww commented Jun 9, 2016

How do we test the fix w/out a release? (Can you make a beta release?)

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jun 9, 2016

Compile from source with -DskipTests
On Jun 9, 2016 10:32 PM, "Nate" notifications@github.com wrote:

How do we test the fix w/out a release? (Can you make a beta release?)


You are receiving this because you modified the open/close state.
Reply to this email directly, view it on GitHub
#1313 (comment), or mute
the thread
https://github.com/notifications/unsubscribe/AApzGO6PSrG6wDetknAViDTbBj7pkfbdks5qKHhcgaJpZM4IhytF
.

@nateww
Copy link

@nateww nateww commented Jun 9, 2016

Looks better

INFO: Database: jdbc:postgresql://localhost/data (PostgreSQL 9.5)
Pending Migrations: 
        20160609170010 - test
        null - repeatable

@PeddyOfficer
Copy link

@PeddyOfficer PeddyOfficer commented Jun 13, 2016

Seems to work with latest snapshot. I'm @davidkarlsen "deputy" atm.

SNAPSHOT:

[INFO] --- flyway-maven-plugin:0-SNAPSHOT:migrate (pwh-migration) @ pen-srv-ddl ---
[INFO] Database: jdbc:oracle:thin:@localhost:1521/orcl (Oracle 12.1)
[INFO] Executing SQL callback: beforeValidate
[INFO] Successfully validated 212 migrations (execution time 00:00.077s)
[INFO] Creating Metadata table: "APPDATA"."schema_version"
[INFO] Successfully baselined schema with version: 0
[INFO] Current version of schema "APPDATA": 0
[WARNING] outOfOrder mode is active. Migration of schema "APPDATA" may not be reproducible.
[INFO] Migrating schema "APPDATA" to version 2 - Pwh create advice status process tables
[INFO] Migrating schema "APPDATA" to version 3.1 - Pwh add account service reference to settlement table
…..
[INFO] Migrating schema "APPDATA" with repeatable migration errapi pkg
[INFO] Migrating schema "APPDATA" with repeatable migration errmsg pkg
[INFO] Migrating schema "APPDATA" with repeatable migration proc accumulatedamount
[INFO] Migrating schema "APPDATA" with repeatable migration proc advice
[INFO] Migrating schema "APPDATA" with repeatable migration proc due date processing

4.0.2:

[INFO] --- flyway-maven-plugin:4.0.2:migrate (pwh-migration) @ pen-srv-ddl ---
[INFO] Database: jdbc:oracle:thin:@localhost:1521/orcl (Oracle 12.1)
[INFO] Executing SQL callback: beforeValidate
[INFO] Successfully validated 212 migrations (execution time 00:00.099s)
[INFO] Creating Metadata table: "APPDATA"."schema_version"
[INFO] Successfully baselined schema with version: 0
[INFO] Current version of schema "APPDATA": 0
[WARNING] outOfOrder mode is active. Migration of schema "APPDATA" may not be reproducible.
[INFO] Migrating schema "APPDATA" with repeatable migration errapi pkg
[INFO] Migrating schema "APPDATA" with repeatable migration errmsg pkg
[INFO] Migrating schema "APPDATA" with repeatable migration proc accumulatedamount
[INFO] Migrating schema "APPDATA" with repeatable migration proc advice
…..
[INFO] Migrating schema "APPDATA" to version 2 - Pwh create advice status process tables
[INFO] Migrating schema "APPDATA" to version 3.1 - Pwh add account service reference to settlement table
[INFO] Migrating schema "APPDATA" to version 3.2 - Pwh add bank transaction code to settlement table
[INFO] Migrating schema "APPDATA" to version 3.3 - Pwh make bookingdate nullable in settlement table
[INFO] Migrating schema "APPDATA" to version 3.4 - Pwh make valuedate nullable in settlement table
[INFO] Migrating schema "APPDATA" to version 5.1 - Pwh add bankname and data to bictoorgid table

@tomaszglinski
Copy link

@tomaszglinski tomaszglinski commented Jun 14, 2016

Hi, when can we expect releasing the fix?

@Jeetah
Copy link

@Jeetah Jeetah commented Jun 15, 2016

4.0.2 makes it worse (against complete empty DB!):

16:23:57.000 INFO [restartedMain] org.flywaydb.core.internal.util.VersionPrinter [?:?:?] Flyway 4.0.2 by Boxfuse
16:23:57.015 INFO [restartedMain] org.flywaydb.core.internal.dbsupport.DbSupportFactory [?:?:?] Database: jdbc:postgresql://192.168.33.10:5432/gos_app (PostgreSQL 9.4)
16:23:57.177 INFO [restartedMain] org.flywaydb.core.internal.command.DbValidate [?:?:?] Successfully validated 176 migrations (execution time 00:00.120s)
16:23:57.181 INFO [restartedMain] org.flywaydb.core.internal.command.DbSchemas [?:?:?] Creating schema "public" ...
16:23:57.185 INFO [restartedMain] org.flywaydb.core.internal.metadatatable.MetaDataTableImpl [?:?:?] Creating Metadata table: "public"."schema_version"
16:23:57.224 INFO [restartedMain] org.flywaydb.core.internal.command.DbMigrate [?:?:?] Current version of schema "public": 0
16:23:57.225 INFO [restartedMain] org.flywaydb.core.internal.command.DbMigrate [?:?:?] Migrating schema "public" with repeatable migration COUNTRY META
16:23:57.820 INFO [restartedMain] org.flywaydb.core.internal.command.DbMigrate [?:?:?] Migrating schema "public" with repeatable migration DYNAMIC CONTENT

with 4.0.1 this is working (and even did with 4.0!)!

@markus-s24
Copy link

@markus-s24 markus-s24 commented Jun 16, 2016

I tested with the latest snapshot and it works like it should. Thanks for the fix!

@axelfontaine axelfontaine added this to the Flyway 4.0.3 milestone Jun 16, 2016
@axelfontaine axelfontaine removed this from the Flyway 4.0.2 milestone Jun 16, 2016
@davidkarlsen
Copy link

@davidkarlsen davidkarlsen commented Jun 16, 2016

@PeddyOfficer thanks! @axelfontaine could you please roll a release?

@maciejmakowski
Copy link

@maciejmakowski maciejmakowski commented Dec 20, 2016

Is this issue actually solved? I have just upgraded to 4.0.3 and I was able to run the repeatable migration correctly after the versioned ones.

However, it seems that the repeatable migrations are now only running once (both the .sql and .java ones are not running a 2nd time).

EDIT: I replied too quickly, sorry! Actually found the issue on my side where the scripts were not running because they were said not to be changed. Still need to figure out solution to make the JDBC migration scripts run every time even if they didn't change (something similar to this feature request: #1453)

@hartrc
Copy link
Author

@hartrc hartrc commented Dec 21, 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
10 participants