Liquibase cannot create DATABASECHANGELOGLOCK table on Azure Synapse database

[0leksei]

Environment

Liquibase Version: 4.6.1

Liquibase Integration & Version: <Pick one: CLI, maven, gradle, spring boot, servlet, etc.> n/a

Liquibase Extension(s) & Version: driver: com.microsoft.sqlserver.jdbc.SQLServerDriver classpath:mssql-jdbc-8.4.0.jre8.jar

Database Vendor & Version: Azure Dedicated SQL Pool in Synapse Analytics

Operating System Type & Version: Ubuntu 18.04

Description

Liquibase cannot be used with Azure Synapse (formerly SQL DW) database. Liquibase can connect to Synapse database successfully and "liquibase status" works. It fails at "liquibase generateChangeLog" command with an error and DATABASECHANGELOGLOCK table is not created.

As a result, Liquibase cannot create its DATABASECHANGELOGLOCK table. When Liquibase tries to create the DATABASECHANGELOGLOCK table, it runs a CREATE TABLE query that includes a primary key constraint which is not supported in Azure Synapse Analytics: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-table-constraints

Steps To Reproduce

• In Azure, create "Dedicated SQL pool (formerly SQL DW)"
• In the created Dedicated SQL pool, create a database
• Use Liquibase to connect to this database using com.microsoft.sqlserver.jdbc.SQLServerDriver driver that comes with Liquibase installation
• Run liquibase generateChangeLog

Actual Behavior

liquibase generateChangeLog

Unexpected error running Liquibase: Enforced unique constraints are not supported. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement. [Failed SQL: (104467) CREATE TABLE DATABASECHANGELOGLOCK (ID int NOT NULL, LOCKED bit NOT NULL, LOCKGRANTED datetime2(3), LOCKEDBY nvarchar(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]

Expected/Desired Behavior

DATABASECHANGELOGLOCK table is created successfully on Azure Synapse (formerly SQL DW) database.

[molivasdat]

Hi @0leksei Thanks for creating this issue. If you create the databasechangeloglock table ahead of running Liquibase generateChangeLog commands does it work as a workaround for now? We will add this to the list of issues that we are processing.

[ShahD33]

Hi @molivasdat, Unfortunately, this doesn't work :-( There seem to be a solution to disable the creation of this table here - https://github.com/liquibase/liquibase-nochangeloglock

[avandras]

@molivasdat I've tried that, which leads to further errors (NONCLUSTERED and NOT ENFORCED are necessary adjustments for Synapse dedicated SQL pools):

CREATE TABLE DATABASECHANGELOGLOCK (
    ID int NOT NULL, 
    LOCKED bit NOT NULL, 
    LOCKGRANTED datetime2(3), 
    LOCKEDBY nvarchar(255), 
    CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY NONCLUSTERED (ID) NOT ENFORCED 
);

CREATE TABLE DATABASECHANGELOG (
    ID VARCHAR(255),
    AUTHOR VARCHAR(255),
    FILENAME VARCHAR(255),
    DATEEXECUTED DATETIME,
    ORDEREXECUTED INT,
    EXECTYPE VARCHAR(10),
    MD5SUM VARCHAR(35),
    DESCRIPTION VARCHAR(255),
    COMMENTS VARCHAR(255),
    TAG VARCHAR(255),
    LIQUIBASE VARCHAR(20),
    CONTEXTS VARCHAR(255),
    LABELS VARCHAR(255),
    DEPLOYMENT_ID VARCHAR(10)
);

Then trying to run an update:

mvn liquibase:update
[...]
[ERROR] Error setting up or running Liquibase:
[ERROR] liquibase.exception.DatabaseException: 'COLUMNPROPERTY' is not a recognized built-in function name. [Failed SQL: (104162) IF EXISTS(select TABLE_NAME
[ERROR]             from INFORMATION_SCHEMA.COLUMNS
[ERROR]            where TABLE_NAME = 'DATABASECHANGELOGLOCK'
[ERROR]              and TABLE_SCHEMA = 'dbo'
[ERROR]              and COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1)
[ERROR]         SET IDENTITY_INSERT DATABASECHANGELOGLOCK ON]

As it turns out, the latter is coming from liquibase-mssql. With a simple change one can definitely can live without it, so the proposed workaround seems to work. There are other issues with Synapse dedicated SQL pools, though (link to a separate issue coming).