Cannot clean schema with FileStream table in MS SQL

[philippedurocher]

Which version and edition of Flyway are you using?

• Flyway Community 6.4.1

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)

• The bug is also present in flyway 6.4.0

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

• Command-line

Which database are you using (type & version)?

• Bug happens with MS SQL 2019 Developper Edition and MS SQL 2016 Standard Edition

Which operating system are you using?

• Windows 10 and Windows Server 2012

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.)

1. Enabled FileStream in MS SQL Server
2. Created a FileGroup for FileStream
3. Created a Table with FileStream in a schema :
   CREATE TABLE [sadip].TableFileStreamTest ( [Guid] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Id] INTEGER UNIQUE, [Binaire] VARBINARY(MAX) FILESTREAM NULL )

When I delete the table from SSMS it works fine.

I run flyway trying to clean schema with following command (don't mind password it's on localhost) :
C:\Dev\SADIP\SADIP\SADIP.Sql\Deploiement\flyway-6.4.1\flyway.cmd -url=jdbc:sqlserver://localhost;databaseName=SADIP -user=APP_SADIP -password=sadip -validateOnMigrate=false -outOfOrder=true -schemas=sadip -table=sadip_flyway_history -placeholders.environnement=local -locations=filesystem:C:\Dev\SADIP\SADIP\SADIP.Sql\Deploiement/sql/sadip clean

What did you expect to see?

• Cleaning schema and dropping all objects as usual

What did you see instead?

Flyway Community Edition 6.4.1 by Redgate
Database: jdbc:sqlserver://localhost:1433;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=SADIP;columnEncryptionSetting=Disabled;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite; (Microsoft SQL Server 15.0)
Successfully dropped pre-schema database level objects (execution time 00:00.001s)
WARNING: Unable to drop schema [sadip]. Attempting clean instead...
ERROR:
Unable to clean schema [sadip]

---

SQL State : S0002
Error Code : 5505
Message : Une table comportant des colonnes FILESTREAM doit avoir une colonne unique non Null avec la propriété ROWGUIDCOL.

[philippedurocher]

The message say A table with FILESTREAM columns must have a unique not null column with ROWGUIDCOL property. My create table SQL seems to meet the requirements. Also I can delete the table with SSMS w/o any problem.

[juliahayward]

The error occurs when we come to clean constraints; the ROWGUIDCOL UNIQUE constraint can't be dropped independently of the table. A fix will be in the next release.