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

Unable to clean schema that contains a user-defined function with schemabinding in SQL Server #2028

Closed
macjohnny opened this issue May 28, 2018 · 1 comment

Comments

@macjohnny
Copy link

macjohnny commented May 28, 2018

Which version and edition of Flyway are you using?

4.2.0

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

could not test it, since 5.1.0 is not compatible with SQL Server 2012

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

Maven plugin, Java API

Which database are you using (type & version)?

SQL Server 2012

Which operating system are you using?

Windows 10

What did you do?

SQL Migration:

IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'MySchema')
  BEGIN
    EXEC sp_executesql N'CREATE SCHEMA [MySchema]';
  END

IF OBJECT_ID('[MySchema].[fn_SomeMyTableValue]') IS NULL -- Check if View Exists
  EXEC('CREATE FUNCTION [MySchema].[fn_SomeMyTableValue] () RETURNS int WITH SCHEMABINDING AS  BEGIN RETURN 1 END') -- Create dummy/empty SP
GO


CREATE TABLE [MySchema].[MyTable] (
  [OrderId] [int] NOT NULL,
  [MyDate] [date] NOT NULL
)

with the following repeatable

CREATE FUNCTION [MySchema].[fn_SomeMyTableValue]
  (
    @OrderId INT
  )
  RETURNS DATE
  WITH SCHEMABINDING
  AS
  BEGIN
    RETURN (
      SELECT MyDate
      FROM [MySchema].[MyTable]
      WHERE OrderId = @OrderId
    )
  END
What did you expect to see?

no error

What did you see instead?

Similar to #1193, we get a

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot DROP TABLE 'MySchema.MyTable' because it is being referenced by object 'fn_SomeMyTableValue'.

after migrating the code and running

            public void migrate(Flyway flyway) {
                flyway.clean();
                flyway.migrate();
            }
@axelfontaine
Copy link
Contributor

axelfontaine commented May 28, 2018

Thanks for the report. I can confirm that Flyway clean does indeed not handle the case where a function depends on a table. The opposite works though.

This is the minimal migration required to demonstrate this:

CREATE TABLE [MyTable123] (
  [OrderId] [int] NOT NULL,
  [MyDate] [date] NOT NULL
)

GO

CREATE FUNCTION [fn_SomeMyTableValue]
  (
    @OrderId INT
  )
  RETURNS DATE
  WITH SCHEMABINDING
  AS
  BEGIN
    RETURN (
      SELECT MyDate
      FROM [dbo].[MyTable123]
      WHERE OrderId = @OrderId
    )
  END

@axelfontaine axelfontaine changed the title Bug: Unable to clean schema that contains a user-defined function with schemabinding in SQL Server Unable to clean schema that contains a user-defined function with schemabinding in SQL Server Jun 5, 2018
@axelfontaine axelfontaine modified the milestones: Flyway 5.2.0, Flyway 5.1.2 Jun 5, 2018
axelfontaine pushed a commit to flyway/flywaydb.org that referenced this issue Jun 5, 2018
@axelfontaine axelfontaine modified the milestones: Flyway 5.1.2, Flyway 5.2.0 Jun 19, 2018
dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
…d function with schemabinding in SQL Server
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants