Unable to clean schema that contains function calling another function and both have SCHEMABINDING

[JasonAUnrein]

What version of Flyway are you using?

Flyway 4.0.3 by Boxfuse

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

flyway-commandline-4.0.3-windows-x64

What database are you using (type & version)?

Microsoft SQL Server Standard (64-bit) 2012 (11.0.3000.0)

What operating system are you using?

Windows 10 Pro Version 1607 OS Build 14393.447

What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)
This appears to be a similar to bug #1193 that was closed. I have the a very similar config where I have two functions using schemabinding (not using computed columns).

1. Download Flyway
2. Create a flywaytest database
3. Configure flyway to use that db and look in c:\sql for my scripts
4. Create the migration script V01__fnA.sql

CREATE FUNCTION dbo.fnA()
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS
BEGIN
   RETURN 'function a';
END;

5. Create the migration script V02__fnB.sql

CREATE FUNCTION dbo.fnB()
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS 
BEGIN
   RETURN dbo.fnA();
END;

6. Execute Flyway to migrate our database.

flyway migrate

If all went well, you should see the following output

Flyway 4.0.3 by Boxfuse

Database: jdbc:jtds:sqlserver://pc/testdb;instance=flywaytest (Microsoft SQL Server 11.0)
Successfully validated 2 migrations (execution time 00:00.011s)
SQLServer does not support setting the schema for the current session. Default schema NOT changed to dbo
Current version of schema [dbo]: 00000001.1
Schema [dbo] is up to date. No migration necessary.

6. Execute Flyway to prints the details and status information about all the migration

flyway info

Just to confirm the migration is a success.

Flyway 4.0.3 by Boxfuse

Database: jdbc:jtds:sqlserver://pc/testdb;instance=flywaytest (Microsoft SQL Server 11.0)
SQLServer does not support setting the schema for the current session. Default schema NOT changed to dbo

+------------+-------------+---------------------+---------+
| Version    | Description | Installed on        | State   |
+------------+-------------+---------------------+---------+
| 00000001.0 | fnA          | 2016-11-11 12:18:18 | Success |
| 00000001.1 | fnB          | 2016-11-11 12:18:18 | Success |
+------------+-------------+---------------------+---------+

7. Finally, try to drop all objects in the configured schema by execute Flyway clean adding -X to the argument list to also print the debug output.

flyway clean -X

You should see the output below with error.

Flyway 4.0.3 by Boxfuse

DEBUG: Java 1.8.0_101 (Oracle Corporation)
DEBUG: Windows 10 10.0 x86

DEBUG: Loading config file: C:\Program Files (x86)\flyway\conf\flyway.conf
DEBUG: Unable to load config file: C:\Users\jason unrein\flyway.conf
DEBUG: Unable to load config file: C:\Users\jason unrein\flyway.conf
DEBUG: Loading config file: c:\sql\flyway.conf
DEBUG: Using configuration:
DEBUG: flyway.configFile -> c:\sql\flyway.conf
DEBUG: flyway.jarDirs -> C:\Program Files (x86)\flyway\jars
DEBUG: flyway.password -> *********
DEBUG: flyway.url -> jdbc:jtds:sqlserver://pc/testdb;instance=flywaytest
DEBUG: flyway.user -> sa
DEBUG: flyway.locations -> filesystem:c:\sql
DEBUG: Adding location to classpath: C:\Program Files (x86)\flyway\drivers\derby-10.12.1.1.jar
DEBUG: Adding location to classpath: C:\Program Files (x86)\flyway\drivers\derbyclient-10.12.1.1.jar
DEBUG: Adding location to classpath: C:\Program Files (x86)\flyway\drivers\h2-1.4.191.jar
DEBUG: Adding location to classpath: C:\Program Files (x86)\flyway\drivers\hsqldb-2.3.3.jar
DEBUG: Adding location to classpath: C:\Program Files (x86)\flyway\drivers\jtds-1.3.1.jar
DEBUG: Adding location to classpath: C:\Program Files (x86)\flyway\drivers\mariadb-java-client-1.4.5.jar
DEBUG: Adding location to classpath: C:\Program Files (x86)\flyway\drivers\postgresql-9.4.1208.jre6.jar
DEBUG: Adding location to classpath: C:\Program Files (x86)\flyway\drivers\sqlite-jdbc-3.7.15-M1.jar
Database: jdbc:jtds:sqlserver://pc/testdb;instance=flywaytest (Microsoft SQL Server 11.0)
DEBUG: DDL Transactions Supported: true
DEBUG: Schema: dbo
DEBUG: Spring Jdbc available: false
DEBUG: Scanning for SQL callbacks ...
DEBUG: Scanning for filesystem resources at 'c:/sql' (Prefix: '', Suffix: '.sql')
DEBUG: Scanning for resources in path: c:\sql (c:/sql)
DEBUG: Found filesystem resource: c:\sql\V01__fnA.sql
DEBUG: Found filesystem resource: c:\sql\V02__fnB.sql
DEBUG: Cleaning schema [dbo] ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
SQLServer does not support setting the schema for the current session. Default schema NOT changed to dbo
ERROR: Unexpected error
org.flywaydb.core.api.FlywayException: Unable to clean schema [dbo]
        at org.flywaydb.core.internal.dbsupport.Schema.clean(Schema.java:150)
        at org.flywaydb.core.internal.command.DbClean$4.doInTransaction(DbClean.java:182)
        at org.flywaydb.core.internal.command.DbClean$4.doInTransaction(DbClean.java:180)
        at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
        at org.flywaydb.core.internal.command.DbClean.cleanSchema(DbClean.java:180)
        at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:130)
        at org.flywaydb.core.Flyway$3.execute(Flyway.java:1025)
        at org.flywaydb.core.Flyway$3.execute(Flyway.java:1021)
        at org.flywaydb.core.Flyway.execute(Flyway.java:1373)
        at org.flywaydb.core.Flyway.clean(Flyway.java:1021)
        at org.flywaydb.commandline.Main.executeOperation(Main.java:135)
        at org.flywaydb.commandline.Main.main(Main.java:102)
Caused by: java.sql.SQLException: Cannot DROP FUNCTION 'dbo.fnA' because it is being referenced by object 'fnB'.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
        at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:784)
        at org.flywaydb.core.internal.dbsupport.JdbcTemplate.execute(JdbcTemplate.java:219)
        at org.flywaydb.core.internal.dbsupport.sqlserver.SQLServerSchema.doClean(SQLServerSchema.java:96)
        at org.flywaydb.core.internal.dbsupport.Schema.clean(Schema.java:148)
        ... 11 more

What did you expect to see?

If you remove the WITH SCHEMABINDING from the scripts, you can successfully remove them

1. Remove the schemabinding option
2. From a clean DB run the migrate
3. Run the clean (without -x)

Flyway 4.0.3 by Boxfuse

Database: jdbc:jtds:sqlserver://pc/testdb;instance=flywaytest (Microsoft SQL Server 11.0)
Successfully cleaned schema [dbo] (execution time 00:00.016s)
SQLServer does not support setting the schema for the current session. Default schema NOT changed to dbo

What did you see instead?

• see above

Notes

I'm curious if you need to remove items in reverse creation order? IE, It looks like you're doing this.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_schema='dbo' AND routine_type='FUNCTION'

but maybe modify it to this

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_schema='dbo' AND routine_type='FUNCTION'
order by created desc

[JasonAUnrein]

I tested my suggested change and it appears to be working fine for me. I have other concerns with using flyway in our situation so I'll hold off creating a pull request.

[axelfontaine]

Thank you very much for the awesome bug report and investigation! It was a tremendous help and time saver!!! 🥇