Unable to decrease block depth below 0 error in version 6.1.1

[SDepn]

Which version and edition of Flyway are you using?

6.1.1

Which client are you using?

Command-line

Which database are you using (type & version)?

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Which operating system are you using?

Windows / CentOS

What did you do?

Hi,

this morning i found that our automated migration testing crashed, which didn't happen yesterday. It crashed with the following error:

Unable to parse statement in ./sql/migration/<file>.sql at line 1 col 1: Flyway parsing bug: unable to decrease block depth below 0

I can't provide the whole migration file which crashed, but this is the general idea:

declare
    ...
begin
    ...
    for r in (
        ...
    )
    loop
        ...
    end loop;
end;
/

alter table ... add (
    constraint ...
    unique (...)
);

This file has not been changed since yesterday and as I found out, our docker image updated since yesterday from version 6.1.0 to 6.1.1. So it seems like something in 6.1.1 changed, which caused this error.

Hope this helps tracking down this issue.

Edit:

This is the debug output for that command:

ERROR: Unexpected error
org.flywaydb.core.api.FlywayException: Unable to parse statement in ./sql/migration/<file>.sql at line 1 col 1: Flyway parsing bug: unable to decrease block depth below 0
	at org.flywaydb.core.internal.parser.Parser.getNextStatement(Parser.java:284)
	at org.flywaydb.core.internal.parser.Parser.access$000(Parser.java:41)
	at org.flywaydb.core.internal.parser.Parser$ParserSqlStatementIterator.<init>(Parser.java:632)
	at org.flywaydb.core.internal.parser.Parser.parse(Parser.java:109)
	at org.flywaydb.core.internal.sqlscript.ParserSqlScript.parse(ParserSqlScript.java:79)
	at org.flywaydb.core.internal.sqlscript.ParserSqlScript.validate(ParserSqlScript.java:127)
	at org.flywaydb.core.internal.sqlscript.ParserSqlScript.executeInTransaction(ParserSqlScript.java:196)
	at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.canExecuteInTransaction(SqlMigrationExecutor.java:75)
	at org.flywaydb.core.internal.command.DbMigrate.isExecuteGroupInTransaction(DbMigrate.java:312)
	at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:275)
	at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:244)
	at org.flywaydb.core.internal.command.DbMigrate.access$100(DbMigrate.java:54)
	at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:162)
	at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:159)
	at org.flywaydb.core.internal.database.base.Connection$1.call(Connection.java:131)
	at org.flywaydb.core.internal.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74)
	at org.flywaydb.core.internal.database.base.Connection.lock(Connection.java:127)
	at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:139)
	at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:159)
DEBUG: Memory usage: 51 of 376M
	at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:137)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:187)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:147)
	at org.flywaydb.core.Flyway.execute(Flyway.java:511)
	at org.flywaydb.core.Flyway.migrate(Flyway.java:147)
	at org.flywaydb.commandline.Main.executeOperation(Main.java:168)
	at org.flywaydb.commandline.Main.main(Main.java:120)
Caused by: org.flywaydb.core.api.FlywayException: Flyway parsing bug: unable to decrease block depth below 0
	at org.flywaydb.core.internal.parser.ParserContext.decreaseBlockDepth(ParserContext.java:48)
	at org.flywaydb.core.internal.database.oracle.OracleParser.adjustBlockDepth(OracleParser.java:373)
	at org.flywaydb.core.internal.parser.Parser.getNextStatement(Parser.java:202)
	... 25 more

[alextercete]

@SDepn Thanks for reporting this.

Flyway 6.1.1 introduces some changes to the parser which allow us to calculate the block depth, so it seems like your script contains a structure we didn't anticipate.

I'm going to attempt to reproduce the issue with the information you've provided, but it might be that will need a bit more details to get to the bottom of it.

[SDepn]

@alextercete I'll try to help out as good as i can.

[alextercete]

Is there any chance you can share a bit more context on the tokens that come immediately before BEGIN and LOOP? The error message suggests that one of the two isn't being identified as something that should increase the block depth.

Also, are there any other END statements in the script (other than the ones you shared already)?

[SDepn]

declare
    var1 number;
    var2 number;
begin
    var1 := -1;
    var2 := -1;
    for r in (
        select * from (
            select t.*,
            count(*) over (partition by [...]) ct
            from [...] t
        )
        where  ct > 1
        order by
		[...]
		case when [...] then 0 else
		case when [...] then 1 else 2 end end,
		case when [...] then [...]-sys_extract_utc(current_timestamp) else
		sys_extract_utc(current_timestamp)-[...] end
    )
    loop
        if (var2 <> r.[...] or var1 <> r.[...]) then
            var2 := r.[...];
            var1 := r.[...];
        else
            delete from [...]
            where [...] = r.[...];
        end if;
    end loop;
end;
/

alter table [...] add (
    constraint [...]
    unique ([...])
);

This is the whole script without variable names, table names, etc.

Hope this helps.

[alextercete]

@SDepn Thanks for taking the time to put that script together, that's really helpful 👍

[alextercete]

@SDepn We've been able to reproduce the issue and we're working on a fix which should be available in the next release.

Thanks again for your help!

[SDepn]

Thank you for the quick replies.

I am looking forward to it.

[juliahayward]

Merged, just confirming it will be in v6.1.2