Is liquibase suitable for Azure Synapse(SQLDW)

General Discussion
1

i have a error when i run the liquibase update command for Azure Synapse(SQLDW). But runbubf updatesql and status successfully. Can you help to deal with this?

  1. env:
  • liquibase: 4.17.0
  • Azure Synapse(SQLDW): sql server 12.0.2000.8
  • driver： mssql-jdbc.jar
  • url: jdbc:sqlserver://$(db_server):1433;database=$(db_database);authentication=ActiveDirectoryPassword;encrypt=true;trustServerCertificate=true;
  1. changelog:
  • A is a schema and B is a table in a database.
--liquibase formatted sql

--changeset 41:2 labels:example-label context:test

--comment: example comment

CREATE TABLE [A].[B]

(

[COUNTRY_CD] [varchar](20) NULL,

[FILE_TYPE] [varchar](20) NULL,

[PIPELINE_NAME] [varchar](100) NOT NULL

)

WITH

(

DISTRIBUTION = ROUND_ROBIN,

CLUSTERED COLUMNSTORE INDEX

)

--rollback DROP TABLE [A].[B];
  1. error datails when run the liquibase update command:
Operation cannot be performed within a transaction. [Failed SQL: (111212) CREATE TABLE [A].[B]

(

[COUNTRY_CD] [varchar](20) NULL,

[FILE_TYPE] [varchar](20) NULL,

[PIPELINE_NAME] [varchar](100) NOT NULL

)

WITH

(

DISTRIBUTION = ROUND_ROBIN,

CLUSTERED COLUMNSTORE INDEX

)]
1 Like
2

By default, Liquibase will attempt to run every changeset within a transaction, committing at the end.

It looks like azure doesn’t allow your “create table” to be ran in a transaction. You can add runInTransaction:false to the changesets that can’t run in a transaction. For example:

--changeset 41:2 labels:example-label context:test runInTransaction:false

Nathan

1 Like
3

Thanks.
When i update the changelog with

--changeset 41:2 labels:example-label context:test runInTransaction:false

The create table statement can be run successfully. But the Liquibase tracking table DATABASECHANGELOG occurs a new error of Azure Synapse(SQLDW).
Do you have any idea about this?

2022-11-08T10:38:59.7177233Z liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: liquibase.exception.DatabaseException:
 Insert values statement can contain only constant literal values or variable references.
 [Failed SQL: (104334) INSERT INTO DevOps_GO_TEST.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('2', '41', './db.changelog-1.1.sql', GETDATE(), 1, '8:cf3ff8ccb81ab0f5941ece1d234a8a60', 'sql', 'example comment', 'EXECUTED', 'test', 'example-label', '4.17.0', '7903936185')]
4

Any update on the above error “Insert values statement can contain only constant literal values or variable references”.