Search code examples
sql-serverdeploymentflyway

Using flyway - How can memory optimized tables be deployed

I am using Flyway Community Edition 6.3.2 by Redgate and attempting to deploy a memory optimized table.

The content of my versioned script is...

CREATE TABLE temp_memory_optimized.test 
( 
    id INT NOT NULL PRIMARY KEY NONCLUSTERED
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY); 
GO

On deploy time I am seeing this error...

ERROR: Migration of schema [dbo] to version 1.0.2 - add memory optimized objects failed! Changes successfully rolled back.
ERROR:
Migration v1.0.2__add_memory_optimized_objects.sql failed
---------------------------------------------------------
SQL State  : S000109
Error Code : 12331
Message    : DDL statements ALTER, DROP and CREATE inside user transactions are not supported with memory optimized tables.
Location   : C:\...\v1.0.2__add_memory_optimized_objects.sql (C:\...\v1.0.2__add_memory_optimized_objects.sql)
Line       : 1
Statement  : CREATE TABLE temp_memory_optimized.test
(
    id INT NOT NULL PRIMARY KEY NONCLUSTERED
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

MO filegroup is configured correctly and I can successfully manually deploy onto my test box.

I have set -mixed=true on the migrate command.

I know I cannot be the first person to hit this problem however internet searches are proving fruitless in trying to track down a solution.

Solution

  • As mentioned in issue 2062 Flyway is not detecting that CREATE TABLE WITH MEMORY_OPTIMIZED = ON is not valid in a transaction automatically. You will need to override this behaviour on a per-script basis as detailed here: https://flywaydb.org/documentation/scriptconfigfiles and will need to do so for each CREATE/ALTER/DELETE on in-memory objects.