Databricks on JDK 21 fails with "Object 'DATABASECHANGELOGLOCK' already exists. about liquibase HOT 4 OPEN

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

from liquibase.

@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.

from liquibase.

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?

from liquibase.

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

from liquibase.