Schema history gets corrupted during "repair"

[AigleeK]

Which version and edition of Flyway are you using?

7.0.4

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Command-Line

Which database are you using (type & version)?

SQL SERVER 2017

Which operating system are you using?

Windows

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)

• Create SP on a R file, migrate
• Modify SP on the same R file, migrate
• Delete R file, migrate
• Execute Flyway Repair. (It creates a delete entry for each migrate in the history table)
• Try to Flyway migrate again. It shows error: "ERROR: Corrupted schema history: multiple delete entries for description SPName"

We saw this in a QA DB that needed to be repaired because of a checksum change on a V file, so we can't migrate without the repair and we can't migrate after the repair.

What did you expect to see?

Been able to migrate after repair

What did you see instead?

Corruption issue

[AigleeK]

The R file can be anything, but attaching one used for reproduce
R__Test.zip

[AigleeK]

• Downloaded version 7.1.1
• Did a migrate, failed validation
• Did a repair, it showed successful
• Tried migrate again, failed validation again
• Did a migrate without validation, it migrates correctly
• Did another migrate (no new migrations), it still says failed validation (generic error)

I think it's related to this same issue/fix, and validate was not fixed correctly. I get ZERO information about what part of the validation failed

[DoodleBobBuffPants]

Thanks for spotting this, we will fix this immediately. In the mean time, you can run validate with -outputType=json to view the specific validate error messages

[AigleeK]

Thanks, confirmed it is this same bug. Fix is not working correctly

[korolar]

Hi @DoodleBobBuffPants ,
i have the following beahviour on the "V__" script.

I am on my develop branch where i have added my new V script. Migration goes succesful.
then, i checkout to a branch that doesnt have this "V" script and run "flyway repair" to be sure that i have a clean state of the versioning in case some previous migration failed. Then i get a similar output as above:

[INFO] Repairing Schema History table for version "13.3.0.0" (Marking as DELETED) ... [INFO] Successfully repaired schema history table "XXX"."TB_SCHEMA_VERSION" (execution time 00:00.792s).

Is this normal behaviour, that this newer script gets marked as 'DELETED'?

EDIT: used version : maven 7.7.2

[DoodleBobBuffPants]

Yes

In Flyway V7 we changed repair to mark any missing migration as deleted

We would suggest that instead of running repair like this, you first run validate. With validate you can set ignoreMissingMigrations so that missing migrations do not cause a validation error, and you can skip repairing in this case

[korolar]

Follow up.

What if there is a corrupted migration to be fixed with repair? How will then the missing migration be treated?

[DoodleBobBuffPants]

repair will repair everything, so missing migrations do get marked as DELETED. The way around it is to use cherryPick as this lets you repair specific migrations. You can automate this kind of workflow by utilizing the JSON output of validate, selecting the migrations that failed to validate for reasons other than being missing, and constructing a list to pass to cherryPick when running repair