Databricks on JDK 21 fails with "Object 'DATABASECHANGELOGLOCK' already exists.

[pradeep-general-motors-canada]

Search first

• I searched and no similar issues were found

Description

Our Team is trying to use Liquibase for Databricks Schema migration .

Unfortunately, Every time I run the Liquibase status or update command, It shows that the DATABASECHANGELOGLOCK tables already exists and keep failing the command. This happens even at the first run.
I even tried to change the table name for the lock table, but no luck.

There is a similar question in the Liquibase forum but it has not been resolved. I did add a reply on that post. But I do not see, there are some activities on that ticket.

https://forum.liquibase.org/t/liquibase-creating-datachangeloglock-multiple-times-in-databricks-catalog-table/8967

System Setup:
OS : Windows 11
JAVA : 21
Liquibase : 4.27.0 ( even tried with 4.25 and 4.26 )
libraries : liquibase-databricks-1.1.3, DatabricksJDBC42(2.6.36)
Unit Catalog Enabled : Yes
Databricks : Azure Databricks

ERROR LOG:

FINE: CREATE TABLE uap_innovation.liquibase_example.databasechangelognew (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, 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))
Apr 10, 2024 10:37:21 AM liquibase.executor
FINE: -1 row(s) affected
Apr 10, 2024 10:37:21 AM liquibase.executor
FINE: Changelog query completed.
Apr 10, 2024 10:37:21 AM liquibase.servicelocator
FINE: Loaded liquibase.lockservice.LockService instance liquibase.lockservice.LockServiceImpl
Apr 10, 2024 10:37:21 AM liquibase.servicelocator
FINE: Loaded liquibase.lockservice.LockService instance liquibase.lockservice.MockLockService
Apr 10, 2024 10:37:21 AM liquibase.servicelocator
FINE: Loaded liquibase.lockservice.LockService instance liquibase.lockservice.OfflineLockService
Apr 10, 2024 10:37:21 AM liquibase.servicelocator
FINE: Loaded liquibase.lockservice.LockService instance liquibase.lockservice.StandardLockService
Apr 10, 2024 10:37:21 AM liquibase.servicelocator
FINE: Loaded liquibase.database.LiquibaseTableNames instance liquibase.database.StandardLiquibaseTableNames
Apr 10, 2024 10:37:21 AM liquibase.servicelocator
FINE: Loaded liquibase.database.LiquibaseTableNames instance com.datical.liquibase.ext.database.ProLiquibaseTableNames
Apr 10, 2024 10:37:21 AM liquibase.configuration
FINE: No configuration value for liquibase.dbclhistory.tableName aka databasechangeloghistory.tableName found
Apr 10, 2024 10:37:21 AM liquibase.configuration
FINE: Configuration liquibase.dbclhistory.tableName is using the default value of DATABASECHANGELOGHISTORY
Apr 10, 2024 10:37:21 AM liquibase.configuration
FINE: No configuration value for liquibase.sql.showSqlWarnings found
Apr 10, 2024 10:37:21 AM liquibase.configuration
FINE: Configuration liquibase.sql.showSqlWarnings is using the default value of true
Apr 10, 2024 10:37:21 AM liquibase.executor
FINE: Create Database Lock Table
Apr 10, 2024 10:37:21 AM liquibase.executor
FINE: CREATE TABLE uap_innovation.liquibase_example.databasechangelocknew (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOCKNEW PRIMARY KEY (ID))
Apr 10, 2024 10:37:30 AM liquibase.executor
FINE: -1 row(s) affected
Apr 10, 2024 10:37:30 AM liquibase.executor
FINE: Changelog query completed.
Apr 10, 2024 10:37:30 AM liquibase.lockservice
FINE: Created database lock table with name: uap_innovation.liquibase_example.databasechangelocknew
Apr 10, 2024 10:37:31 AM liquibase.lockservice
FINE: Failed to create or initialize the lock table, trying again, iteration 1 of 10
liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Error executing SQL SELECT COUNT() FROM uap_innovation.liquibase_example.databasechangelocknew: [Databricks]JDBCDriver Error caught in BackgroundFetcher. Foreground thread ID: 1. Background thread ID: 35. Error caught: Could not initialize class com.databricks.client.jdbc42.internal.apache.arrow.memory.util.MemoryUtil.
at liquibase.lockservice.StandardLockService.isDatabaseChangeLogLockTableInitialized(StandardLockService.java:219)
at liquibase.lockservice.StandardLockService.init(StandardLockService.java:135)
at liquibase.command.core.helpers.DatabaseChangelogCommandStep.checkLiquibaseTables(DatabaseChangelogCommandStep.java:145)
at liquibase.command.core.helpers.DatabaseChangelogCommandStep.run(DatabaseChangelogCommandStep.java:91)
at liquibase.command.CommandScope.execute(CommandScope.java:219)
at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55)
at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24)
at picocli.CommandLine.executeUserObject(CommandLine.java:1953)
at picocli.CommandLine.access$1300(CommandLine.java:145)
at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2352)
at picocli.CommandLine$RunLast.handle(CommandLine.java:2346)
at picocli.CommandLine$RunLast.handle(CommandLine.java:2311)
at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179)
at picocli.CommandLine.execute(CommandLine.java:2078)
at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$2(LiquibaseCommandLine.java:395)
at liquibase.Scope.child(Scope.java:199)
at liquibase.Scope.child(Scope.java:175)
at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$3(LiquibaseCommandLine.java:370)
at liquibase.Scope.child(Scope.java:199)
at liquibase.Scope.child(Scope.java:175)
at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:367)
at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:104)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at liquibase.integration.commandline.LiquibaseLauncher.main(LiquibaseLauncher.java:116)
Caused by: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Error executing SQL SELECT COUNT() FROM uap_innovation.liquibase_example.databasechangelocknew: [Databricks]JDBCDriver Error caught in BackgroundFetcher. Foreground thread ID: 1. Background thread ID: 35. Error caught: Could not initialize class com.databricks.client.jdbc42.internal.apache.arrow.memory.util.MemoryUtil.
at liquibase.executor.jvm.ChangelogJdbcMdcListener.query(ChangelogJdbcMdcListener.java:62)
at liquibase.lockservice.StandardLockService.isDatabaseChangeLogLockTableInitialized(StandardLockService.java:216)
... 24 more
Caused by: liquibase.exception.DatabaseException: Error executing SQL SELECT COUNT(*) FROM uap_innovation.liquibase_example.databasechangelocknew: [Databricks]JDBCDriver Error caught in BackgroundFetcher. Foreground thread ID: 1. Background thread ID: 35. Error caught: Could not initialize class com.databricks.client.jdbc42.internal.apache.arrow.memory.util.MemoryUtil.
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:101)
at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:227)
at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:235)
at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:243)
at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:258)
at liquibase.executor.jvm.JdbcExecutor.queryForInt(JdbcExecutor.java:279)
at liquibase.executor.jvm.JdbcExecutor.queryForInt(JdbcExecutor.java:274)
at liquibase.lockservice.StandardLockService.lambda$isDatabaseChangeLogLockTableInitialized$2(StandardLockService.java:216)
at liquibase.executor.jvm.ChangelogJdbcMdcListener.lambda$query$1(ChangelogJdbcMdcListener.java:55)
at liquibase.Scope.child(Scope.java:199)
at liquibase.Scope.child(Scope.java:175)
at liquibase.executor.jvm.ChangelogJdbcMdcListener.query(ChangelogJdbcMdcListener.java:55)
... 25 more
Caused by: java.sql.SQLException: [Databricks]JDBCDriver Error caught in BackgroundFetcher. Foreground thread ID: 1. Background thread ID: 35. Error caught: Could not initialize class com.databricks.client.jdbc42.internal.apache.arrow.memory.util.MemoryUtil.
at com.databricks.client.hivecommon.dataengine.BackgroundFetcher.run(Unknown Source)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
Caused by: com.databricks.client.support.exceptions.GeneralException: [Databricks]JDBCDriver Error caught in BackgroundFetcher. Foreground thread ID: 1. Background thread ID: 35. Error caught: Could not initialize class com.databricks.client.jdbc42.internal.apache.arrow.memory.util.MemoryUtil.
... 5 more
Caused by: java.lang.NoClassDefFoundError: Could not initialize class com.databricks.client.jdbc42.internal.apache.arrow.memory.util.MemoryUtil
at com.databricks.client.jdbc42.internal.apache.arrow.memory.ArrowBuf.getDirectBuffer(ArrowBuf.java:229)
at com.databricks.client.jdbc42.internal.apache.arrow.memory.ArrowBuf.nioBuffer(ArrowBuf.java:224)
at com.databricks.client.jdbc42.internal.apache.arrow.vector.ipc.ReadChannel.readFully(ReadChannel.java:87)
at com.databricks.client.jdbc42.internal.apache.arrow.vector.ipc.message.MessageSerializer.readMessageBody(MessageSerializer.java:728)
at com.databricks.client.jdbc42.internal.apache.arrow.vector.ipc.message.MessageSerializer.deserializeRecordBatch(MessageSerializer.java:363)
at com.databricks.client.spark.arrow.ArrowBuffer.deserializeBatch(Unknown Source)
at com.databricks.client.spark.arrow.ArrowBuffer.handleInitializeBuffer(Unknown Source)
at com.databricks.client.hivecommon.api.HiveServer2BaseBuffer.initializeBuffer(Unknown Source)
at com.databricks.client.hivecommon.api.RowsetBuffer.initializeBuffer(Unknown Source)
at com.databricks.client.hivecommon.api.HS2Client.getRowSetInformation(Unknown Source)
at com.databricks.client.hivecommon.api.HS2Client.fetchFromServer(Unknown Source)
at com.databricks.client.spark.jdbc.DownloadableFetchClient.fetchNRows(Unknown Source)
at com.databricks.client.hivecommon.api.HS2Client.fetchRows(Unknown Source)
at com.databricks.client.hivecommon.dataengine.BackgroundFetcher.run(Unknown Source)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: java.lang.ExceptionInInitializerError: Exception java.lang.RuntimeException: Failed to initialize MemoryUtil. [in thread "pool-1-thread-1"]
at com.databricks.client.jdbc42.internal.apache.arrow.memory.util.MemoryUtil.(MemoryUtil.java:136)
... 19 more

Apr 10, 2024 10:37:32 AM liquibase.executor
FINE: Create Database Lock Table
Apr 10, 2024 10:37:32 AM liquibase.executor
FINE: CREATE TABLE uap_innovation.liquibase_example.databasechangelocknew (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOCKNEW PRIMARY KEY (ID))
Apr 10, 2024 10:37:32 AM liquibase.lockservice
FINE: Failed to create or initialize the lock table, trying again, iteration 2 of 10
liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: [Databricks]JDBCDriver ERROR processing query/statement. Error Code: 0, SQL state: 42P07, Query: CREATE TAB***, Error message from Server: org.apache.hive.service.cli.HiveSQLException: Error running query: [TABLE_OR_VIEW_ALREADY_EXISTS] org.apache.spark.sql.catalyst.analysis.TableAlreadyExistsException: [TABLE_OR_VIEW_ALREADY_EXISTS] Cannot create table or view liquibase_example.databasechangelocknew because it already exists.
Choose a different name, drop or replace the existing object, add the IF NOT EXISTS clause to tolerate pre-existing objects, or add the OR REFRESH clause to refresh the existing streaming table.
at org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:48)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:697)
at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)
at com.databricks.unity.UCSEphemeralState$Handle.runWith(UCSEphemeralState.scala:45)
at com.databricks.unity.HandleImpl.runWith(UCSHandle.scala:103)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.org$apache$spark$sql$hive$thriftserver$SparkExecuteStatementOperation$$execute(SparkExecuteStatementOperation.scala:574)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.$anonfun$run$2(SparkExecuteStatementOperation.scala:423)
at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)
at com.databricks.logging.UsageLogging.withAttributionContext(UsageLogging.scala:420)
at com.databricks.logging.UsageLogging.withAttributionContext$(UsageLogging.scala:418)
at com.databricks.spark.util.PublicDBLogging.withAttributionContext(DatabricksSparkUsageLogger.scala:27)
at com.databricks.logging.UsageLogging.withAttributionTags(UsageLogging.scala:472)
at com.databricks.logging.UsageLogging.withAttributionTags$(UsageLogging.scala:455)
at com.databricks.spark.util.PublicDBLogging.withAttributionTags(DatabricksSparkUsageLogger.scala:27)
at com.databricks.spark.util.PublicDBLogging.withAttributionTags0(DatabricksSparkUsageLogger.scala:72)
at com.databricks.spark.util.DatabricksSparkUsageLogger.withAttributionTags(DatabricksSparkUsageLogger.scala:172)
at com.databricks.spark.util.UsageLogging.$anonfun$withAttributionTags$1(UsageLogger.scala:491)
at com.databricks.spark.util.UsageLogging$.withAttributionTags(UsageLogger.scala:603)
at com.databricks.spark.util.UsageLogging$.withAttributionTags(UsageLogger.scala:612)
at com.databricks.spark.util.UsageLogging.withAttributionTags(UsageLogger.scala:491)
at com.databricks.spark.util.UsageLogging.withAttributionTags$(UsageLogger.scala:489)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.withAttributionTags(SparkExecuteStatementOperation.scala:65)
at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.$anonfun$withLocalProperties$8(ThriftLocalProperties.scala:161)
at com.databricks.spark.util.IdentityClaim$.withClaim(IdentityClaim.scala:48)
at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties(ThriftLocalProperties.scala:160)
at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties$(ThriftLocalProperties.scala:51)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.withLocalProperties(SparkExecuteStatementOperation.scala:65)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:401)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:386)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1899)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2.run(SparkExecuteStatementOperation.scala:435)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)

Steps To Reproduce

Followed the below documentation to setup Liquibase on Windows and Run the status and update command.
liquibase-databricks

CHANGLOG FILE NAME : example-changelog.sql
CHANGELOG FILE CONTENET:

--liquibase formatted sql

--changeset your.name:1 labels:example-label context:example-context
--comment: example comment
create table person (
    id int primary key auto_increment not null,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
)
--rollback DROP TABLE person;

--changeset your.name:2 labels:example-label context:example-context
--comment: example comment
create table company (
    id int primary key auto_increment not null,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
)
--rollback DROP TABLE company;

--changeset other.dev:3 labels:example-label context:example-context
--comment: example comment
alter table person add column country varchar(2)
--rollback ALTER TABLE person DROP COLUMN country;

---

LIQUIBASE PROPERTIES FILE NAME: liquibase.properties
LIQUIBASE PROPERTIES FILE CONTENT:

databaseChangeLogTableName=databasechangelog
databaseChangeLogLockTableName=databasechangelock

changeLogFile: example-changelog.sql

#### Enter the Target database 'url' information  ####
liquibase.command.url=jdbc:databricks:/<workspace_url>:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/1.0/warehouses/<warehouse_id>;ConnCatalog=<catalog_name>;ConnSchema=liquibase_example;

# Enter the username for your Target database.
liquibase.command.username: token

# Enter the password for your Target database.
liquibase.command.password:  <PAT TOKEN>

# #### Enter the Source Database 'referenceUrl' information ####
# ## The source database is the baseline or reference against which your target database is compared for diff/diffchangelog commands.

# Enter URL for the source database
liquibase.command.referenceUrl: jdbc:databricks://<workdpace_url>:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/1.0/warehouses/<warehouse_id>;ConnCatalog=<catalog_name>;ConnSchema=liquibase_example;

# Enter the username for your source database
liquibase.command.referenceUsername: token

# Enter the password for your source database
liquibase.command.referencePassword: <PAT TOKEN>

liquibase.command.classpath: "C:\Program Files\liquibase\lib\liquibase-databricks-1.1.3.jar"

# Logging Configuration
# logLevel controls the amount of logging information generated. If not set, the default logLevel is INFO.
# Valid values, from least amount of logging to most, are:
#   OFF, ERROR, WARN, INFO, DEBUG, TRACE, ALL
# If you are having problems, setting the logLevel to DEBUG and re-running the command can be helpful.
logLevel: DEBUG

# The logFile property controls where logging messages are sent. If this is not set, then logging messages are
# displayed on the console. If this is set, then messages will be sent to a file with the given name.
# logFile: liquibase.log


### Liquibase Pro Key Information ####
#Learn more, contact support, or get or renew a Pro Key at https://www.liquibase.com/trial
liquibase.licenseKey: <LICENSE KEY>

COMMAD TO RUN:

• liquibase status
• liquibase update

Expected/Desired Behavior

Expecting tables mentioned in the changelog files gets deployed to databricks unity catalog

Liquibase Version

4.27

Database Vendor & Version

Databricks Unity Catalog

Liquibase Integration

CLI

Liquibase Extensions

liquibase-databricks-1.1.3, DatabricksJDBC42(2.6.36)

OS and/or Infrastructure Type/Provider

Windows 11

Additional Context

I am using Azure Databricks. Hence I created a Microsoft support Ticket and they connected me with Databricks Team who owns the databricks JDBC connector.

I did connect with the Databricks team to check, if the JDBC connector is having some issue. But Databricks Teams thinks its something to do with the Liquibase CLI and not the connector.
Databricks Team is ready to connect with anyone of the support engineer from Liquibase and work together .

NOTE:
If this issue required a collaboration between Liquibase and databricks. I would be really happy to setup a call with required engineers.

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[tati-qalified]

Hi @pradeep-general-motors-canada, thank you for reporting this issue.

I have been able to replicate it using the latest liquibase and liquibase-databricks versions.
Both liquibase status and liquibase update generate the Table already exists error, but running update-sql produces a correct script:

-- Create Database Lock Table
CREATE TABLE main.testing.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM main.testing.DATABASECHANGELOGLOCK;

INSERT INTO main.testing.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, false);

-- Lock Database
UPDATE main.testing.DATABASECHANGELOGLOCK SET LOCKED = true, LOCKEDBY = 'GXCN4031 (192.168.0.111)', LOCKGRANTED = current_timestamp() WHERE ID = 1 AND LOCKED = false;

-- Create Database Change Log Table
CREATE TABLE main.testing.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, 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));


-- Changeset changelog.sql::1::your.name
-- example comment
create table person (
    id int primary key auto_increment not null,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
);

INSERT INTO main.testing.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'your.name', 'changelog.sql', current_timestamp(), 1, '9:f6d6b04fbf860e734bbcaa93c2207423', 'sql', 'example comment', 'EXECUTED', 'example-context', 'example-label', '4.27.0', '2855724040');

-- Changeset changelog.sql::2::your.name
-- example comment
create table company (
    id int primary key auto_increment not null,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
);

INSERT INTO main.testing.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('2', 'your.name', 'changelog.sql', current_timestamp(), 2, '9:8105cd2916fe5e4a0d7e030fd54037dc', 'sql', 'example comment', 'EXECUTED', 'example-context', 'example-label', '4.27.0', '2855724040');

-- Changeset changelog.sql::3::other.dev
-- example comment
alter table person add column country varchar(2);

INSERT INTO main.testing.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('3', 'other.dev', 'changelog.sql', current_timestamp(), 3, '9:7ce8f8f671c85fee99df053c02c385f2', 'sql', 'example comment', 'EXECUTED', 'example-context', 'example-label', '4.27.0', '2855724040');

-- Release Database Lock
UPDATE main.testing.DATABASECHANGELOGLOCK SET LOCKED = false, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

We will be looking further into this. Do let us know if you come across other problems or if you have any further information.

Thank you,
Tatiana

CC: @kevin-atx

[pradeep-general-motors-canada]

Hi @pradeep-general-motors-canada, thank you for reporting this issue.

I have been able to replicate it using the latest liquibase and liquibase-databricks versions. Both liquibase status and liquibase update generate the Table already exists error, but running update-sql produces a correct script:

-- Create Database Lock Table
CREATE TABLE main.testing.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM main.testing.DATABASECHANGELOGLOCK;

INSERT INTO main.testing.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, false);

-- Lock Database
UPDATE main.testing.DATABASECHANGELOGLOCK SET LOCKED = true, LOCKEDBY = 'GXCN4031 (192.168.0.111)', LOCKGRANTED = current_timestamp() WHERE ID = 1 AND LOCKED = false;

-- Create Database Change Log Table
CREATE TABLE main.testing.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, 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));


-- Changeset changelog.sql::1::your.name
-- example comment
create table person (
    id int primary key auto_increment not null,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
);

INSERT INTO main.testing.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'your.name', 'changelog.sql', current_timestamp(), 1, '9:f6d6b04fbf860e734bbcaa93c2207423', 'sql', 'example comment', 'EXECUTED', 'example-context', 'example-label', '4.27.0', '2855724040');

-- Changeset changelog.sql::2::your.name
-- example comment
create table company (
    id int primary key auto_increment not null,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
);

INSERT INTO main.testing.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('2', 'your.name', 'changelog.sql', current_timestamp(), 2, '9:8105cd2916fe5e4a0d7e030fd54037dc', 'sql', 'example comment', 'EXECUTED', 'example-context', 'example-label', '4.27.0', '2855724040');

-- Changeset changelog.sql::3::other.dev
-- example comment
alter table person add column country varchar(2);

INSERT INTO main.testing.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('3', 'other.dev', 'changelog.sql', current_timestamp(), 3, '9:7ce8f8f671c85fee99df053c02c385f2', 'sql', 'example comment', 'EXECUTED', 'example-context', 'example-label', '4.27.0', '2855724040');

-- Release Database Lock
UPDATE main.testing.DATABASECHANGELOGLOCK SET LOCKED = false, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

We will be looking further into this. Do let us know if you come across other problems or if you have any further information.

Thank you, Tatiana

CC: @kevin-atx

Hi @tati-qalified
Thanks for responding on this issue. Just wanted to double confirm on the explaining. What I understood is, Liquibase uses an internal updated SQL command to run on update and status command. And you were able to generate the update-sql and the SQL looks perfect, however when we run the subsequent status or update commands, Liquibase Fails.

If my understanding is correct. There is no workaround to get the Liquibase working with databricks, unless this particular bug is addressed.

Have I understood it right?

[tati-qalified]

@pradeep-general-motors-canada luckily that's not the case - being able to generate the SQL script correctly means that you can run that script directly onto your database, and for any future changes just run liquibase update-sql again and the database's previous state will be taken into consideration.

That would be the workaround until the bug is fixed.

[pradeep-general-motors-canada]

Thanks for the clarity. Would love to use the fixed version once its ready :)