Create memory optimized tables ERROR

General Discussion
1

Hello,
I work with MSSQL and I need to create a procedure to create a Type memory optimized tables.

Liquibase produces an error of type

Error :
Unexpected error running Liquibase: DDL statements ALTER, DROP and CREATE inside user transactions are not supported with memory optimized tables. [Failed SQL: (12331)

How to add this kind of object with liquibase.

Thanks for your help

2

Can you provide the changeset so we can see what you are trying to do? I’m assuming you are trying to use a Liquibase change-type to create this type of object, maybe you need to just use custom SQL instead.

3

Hi, there,
My knowledge of Liquibase is poor.
I think you want the “dbchangelog.xml” files.

Here is the basic one:

<?xml version="1.0" encoding="UTF-8"?>
  <databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
	<include file="./01_System/dbchangelog.xml" relativeToChangelogFile="true"/>
	<include file="./02_Table/dbchangelog.xml" relativeToChangelogFile="true"/>
	<includeAll path="03_Function" relativeToChangelogFile="true" />
	<includeAll path="04_View" relativeToChangelogFile="true" />
	<includeAll path="05_StoreProcedure" relativeToChangelogFile="true" />
	<include file="./06_Index/dbchangelog.xml" relativeToChangelogFile="true"/>
	<includeAll path="07_Trigger" relativeToChangelogFile="true" />
	<include file="./99_Misc/dbchangelog.xml" relativeToChangelogFile="true"/>
  </databaseChangeLog>

And the one in the 01_System directory where the code is

<?xml version="1.0" encoding="UTF-8"?>
  <databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
	
	<changeSet author="mba" id="Add Type apprenticeship_Type">
		<validCheckSum>8:218491c3d244ac25fbb7f479ac9356f8</validCheckSum>
		<sqlFile 
				endDelimiter="\nGO"
				path="apprenticeship_Type.sql"
				relativeToChangelogFile="true"
				splitStatements="true"
				stripComments="true"/>
	</changeSet>

  </databaseChangeLog>

And the SQL file

/*----------------------------------------------------
Author:		mba
Date:		20.09.2021
Description:Add a data type for create table parameters
-----------------------------------------------------*/
-- Enable MEMORY_OPTIMIZED on the DB
/*
-- Creating a Memory Optimized Table
-- *********************************
--The key to having a table “In-Memory” is the use of the key word “MEMORY-OPTIMIZED” on the create statement when you first create the table. Note there is no ability to ALTER a table to make an existing one memory optimized; you will need to recreate the table and load the data in order to take advantage of this option on an existing table.  There are just a couple more settings you need to have configured to make this work as you can see from below.
-- **************************************************************

ALTER DATABASE searchService_test SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
ALTER DATABASE searchService_test ADD FILEGROUP searchService_test_mod CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE searchService_test ADD FILE (name='searchService_test_mod1', 
	filename='d:\data\searchService_test) TO FILEGROUP searchService_test_mod
*/
--GO

--DROP TYPE IF EXISTS [dbo].[apprenticeship_Type]

GO
IF TYPE_ID(N'apprenticeship_Type') IS NULL
BEGIN
/****** Object:  UserDefinedTableType [dbo].[apprenticeship_Type]    Script Date: 19.09.2021 21:53:52 ******/
	CREATE TYPE [dbo].[apprenticeship_Type] AS TABLE(
		[ID_] [int] IDENTITY(1,1) NOT NULL,
		[id] [int] NOT NULL,
		[id_apprenticeship] [int] NOT NULL,
		[name_de_mf] [nvarchar](255) COLLATE Latin1_General_CI_AI NOT NULL,
		[name_fr_mf] [nvarchar](255) COLLATE Latin1_General_CI_AI NOT NULL,
		[name_it_mf] [nvarchar](255) COLLATE Latin1_General_CI_AI NOT NULL,
		INDEX [IX_appenticesship_Type_ID_] NONCLUSTERED 
		(
			[ID_] ASC
		),
			INDEX [IX_appenticesship_Type_name_de_mf] NONCLUSTERED 
		(
			[name_de_mf] ASC
		),
			INDEX [IX_appenticesship_Type_name_fr_mf] NONCLUSTERED 
		(
			[name_fr_mf] ASC
		),
			INDEX [IX_appenticesship_Type_name_it_mf] NONCLUSTERED 
		(
			[name_it_mf] ASC
		),
			 PRIMARY KEY NONCLUSTERED 
		(
			[id] ASC
		)
	)
	WITH ( MEMORY_OPTIMIZED = ON )
END
GO
4

OK, that error is actually a SQL-Server error, not a Liquibase error.

So I think this might be the solution… add this attribute inside the “sqlFile” section: runInTransaction=“false”

5

I add your parameter on sqlFile section like that :slight_smile:

<?xml version="1.0" encoding="UTF-8"?>
  <databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
	
	<changeSet author="mba" id="Add Type apprenticeship_Type">
		<sqlFile 
			runInTransaction="false"
			endDelimiter="\nGO"
			path="apprenticeship_Type.sql"
			relativeToChangelogFile="true"
			splitStatements="true"
			stripComments="true"
			runOnChange="true"/>
	</changeSet>

And I have yet this answer :

Liquibase Community 4.4.3 by Datical
Unexpected error running Liquibase: Error parsing line 16 column 24 of v1.10.x/01_System/dbchangelog.xml: cvc-complex-type.3.2.2 : L'attribut 'runInTransaction' n'est pas autorisé dans l'élément 'sqlFile'.
6

That error means that sqlFile element does not support that attribute.
runInTransaction is an attribute of the changeSet element. Try to move it to the changeSet line.