Clean error when deleting table with indexed computed column

[zikato]

Which version and edition of Flyway are you using?

Flyway Community Edition 7.5.4 by Redgate
Running Flyway in docker against SQL Server 2019-CU9 also in Docker

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)

Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64)
Jan 25 2021 20:16:12
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS)

Which operating system are you using?

Windows 10 Pro

What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran)

Apply multiple scripts with flyway migrate
Attempt to clean the database using flyway clean - this fails

Could not reproduce the minimal steps. When creating just the table with computed column and index the error did not occur.

What did you expect to see?

Completion message.
I expected the table to be dropped along with indexes. It looks like the computed column is being dropped ahead of the depending index

What did you see instead?

Error message - Index is depending on the computed column - sanitized debug output:

Database: jdbc:sqlserver://db:1433;sendTemporalDataTypesAsStringForBulkCopy=true;delayLoadingLobs=true;useFmtOnly=false;useBulkCopyForBatchInsert=false;cancelQueryTimeout=-1;sslProtocol=TLS;jaasConfigurationName=SQLJDBCDriver;statementPoolingCacheSize=0;serverPreparedStatementDiscardThreshold=10;enablePrepareOnFirstPreparedStatementCall=false;fips=false;socketTimeout=0;authentication=NotSpecified;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustStoreType=JKS;trustServerCertificate=false;TransparentNetworkIPResolution=true;serverNameAsACE=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;queryTimeout=-1;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=MyDb;columnEncryptionSetting=Disabled;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite; (Microsoft SQL Server 15.0)
DEBUG: Driver : Microsoft JDBC Driver 8.4 for SQL Server 8.4.1.0
DEBUG: DDL Transactions Supported: true
DEBUG: Schemas:
DEBUG: Default schema: null
DEBUG: Scanning for SQL callbacks ...
DEBUG: Dropping pre-schema database level objects...
Successfully dropped pre-schema database level objects (execution time 00:00.002s)
DEBUG: Cleaning schema [dbo] ...
DEBUG: Ignoring dependency-related error: The object 'unrelatedTable' is dependent on column 'unrelatedPartitionColumn'.
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Memory usage: 13 of 68M
ERROR: Unexpected error
org.flywaydb.core.internal.exception.FlywaySqlException:
Unable to clean schema [dbo]

---

SQL State : S0001
Error Code : 5074
Message : The index 'IX_myTable_myComputedColumn' is dependent on column 'myComputedColumn'.

    at org.flywaydb.core.internal.database.base.Schema.clean(Schema.java:124)
    at org.flywaydb.core.internal.command.DbClean.lambda$cleanSchema$4(DbClean.java:205)
    at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:66)
    at org.flywaydb.core.internal.command.DbClean.cleanSchema(DbClean.java:204)
    at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:103)
    at org.flywaydb.core.Flyway.doClean(Flyway.java:346)
    at org.flywaydb.core.Flyway.access$400(Flyway.java:77)
    at org.flywaydb.core.Flyway$4.execute(Flyway.java:362)
    at org.flywaydb.core.Flyway$4.execute(Flyway.java:358)
    at org.flywaydb.core.Flyway.execute(Flyway.java:570)
    at org.flywaydb.core.Flyway.clean(Flyway.java:358)
    at org.flywaydb.commandline.Main.executeOperation(Main.java:219)
    at org.flywaydb.commandline.Main.main(Main.java:144)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 'IX_myTable_myComputedColumn' is dependent on column 'myComputedColumn'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:505)
at org.flywaydb.core.internal.jdbc.JdbcTemplate.execute(JdbcTemplate.java:222)
at org.flywaydb.core.internal.database.sqlserver.SQLServerSchema.doClean(SQLServerSchema.java:263)
at org.flywaydb.core.internal.database.base.Schema.clean(Schema.java:122)
... 12 more

[Rixium]

Hey @zikato ,

We have already solved this issue and released the fix,
can you try updating to the latest version?

Thanks 😀

[zikato]

I'm using the latest docker version. Not sure how can I update it?

[zikato]

So the :latest tag in docker didn't work as expected.
I've used the :7.7.0-alpine so I get this version:

Flyway Community Edition 7.7.0 by Redgate

And the problem is still there. I managed to find a minimal reproducible script.

CREATE PARTITION FUNCTION [PartitionFc](TINYINT)
    AS RANGE
    FOR VALUES (1, 2);

GO
CREATE PARTITION SCHEME [PartitionScheme]
    AS PARTITION [PartitionFc]
    TO ([Primary], [Primary], [Primary]);
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
CREATE TABLE [dbo].[myTable](
	[PK] [bigint] IDENTITY(1,1) NOT NULL,
	[DateCol] [datetime] NOT NULL,
	[PartitionCol]  AS (CONVERT([tinyint],datepart(month,[DateCol]))) PERSISTED NOT NULL
 CONSTRAINT [PK_myTables] PRIMARY KEY CLUSTERED 
(
	[PK] ASC,
	[PartitionCol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartitionScheme]([PartitionCol])
) ON [PartitionScheme]([PartitionCol])
GO

[Rixium]

Hey @zikato,

We've managed to reproduce this issue, and will be looking in to it,

Thanks 😀

[zikato]

Thank you!