“Cannot find the declaration of element 'databaseChangeLog'” error when using master changelog xml file

Following this guide from the official website I’ve created my own changelog-master.xml file:

I’ve then created the liquibase.properties file in the same directory:

# Enter the path for your changelog file.
changeLogFile=changelog-master.xml

#### Enter the Target database 'url' information  ####
url=jdbc:sqlite://home/username/liquibase/examples/sqlite-test/testdb

(the file is correct, because if i run a normal .sql changelog it runs correctly)

I’ve then created a .sql changelog file (still in the same directory), which by documentation should be ran automatically after launching liquibase update. The file is 000020-changelog.sql and contains

--liquibase formatted sql

--changeset daniel:3
create table phonebook(name TEXT, surname TEXT, type TEXT, phone_number TEXT);

But now when I launch liquibase update I get an XML Parsing error:

Unexpected error running Liquibase: cvc-elt.1.a: Cannot find the declaration of element ‘databaseChangeLog’.

And here is my problem, I’ve checked multiple times if I had some errors in the changelog-master.xml but it looks fine.

This is the project’s structure, if you need it

root@dev-machine:/home/username/liquibase/examples/sqlite-test# ls -la
total 68
drwxr-xr-x 2 root    root     4096 Sep  4 17:28 .
drwxr-xr-x 5 username username  4096 Sep  4 17:02 ..
-rw-r--r-- 1 root    root      139 Sep  4 13:35 000020-changelog.sql
-rw-r--r-- 1 root    root      118 Sep  4 13:36 000030-changelog.sql
-rw-r--r-- 1 root    root      201 Sep  4 17:05 000040-changelog.sql
-rw-r--r-- 1 root    root      240 Sep  4 17:28 000050-changelog.sql
-rw-r--r-- 1 root    root      456 Sep  4 14:22 changelog-master.xml
-rw-r--r-- 1 root    root     2637 Sep  4 16:36 liquibase.properties
-rw-r--r-- 1 root    root    32768 Sep  4 17:28 testdb

testdb is the sqlite database I’m using to test liquibase. The .sql files are the consecutive changelogs that must be run to update the DB

I’ve managed to fix the issue. In short since I was running Liquibase 4.0.0 it’s not compatible with the old version of the XSD I passed in the XML file. To Fix it I had liquibase generate a changelog from the existing DB and copied the XML declaration in my own changelog-master.xml file, which now looks like this:

But now I have another issue. If I put more sql statements in a file only the first one gets executed.

If I run liquibase updatesql i see the correct sql statements:

-- Create Database Lock Table
CREATE TABLE DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TEXT, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DATABASECHANGELOGLOCK;

INSERT INTO DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Lock Database
UPDATE DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = '10.20.0.35 (10.20.0.35)', LOCKGRANTED = '2020-09-09 09:12:49.059' WHERE ID = 1 AND LOCKED = 0;

-- Create Database Change Log Table
CREATE TABLE DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TEXT NOT NULL, ORDEREXECUTED INTEGER 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 schemasql/000010-changelog.sql::raw::includeAll
create table users(id int primary key, username text, password text);

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('raw', 'includeAll', 'schemasql/000010-changelog.sql', CURRENT_TIMESTAMP, 1, '8:1dcc42c194963b8c79824923636afb03', 'sql', '', 'EXECUTED', NULL, NULL, '3.10.2', '9635572458');

-- Changeset schemasql/000020-changelog.sql::raw::includeAll
create table phonebook(phone_number text, name text, surname text);
create table quickdial(quick_dial int, phone_number text, name text, surname text);

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('raw', 'includeAll', 'schemasql/000020-changelog.sql', CURRENT_TIMESTAMP, 2, '8:75f3a8bd9f758dd20fc5e2bf69386af4', 'sql', '', 'EXECUTED', NULL, NULL, '3.10.2', '9635572458');

-- Changeset schemasql/000030-changelog.sql::raw::includeAll
create table user_options(user_id INT, option_text TEXT);

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('raw', 'includeAll', 'schemasql/000030-changelog.sql', CURRENT_TIMESTAMP, 3, '8:58ce948a7d5b8f66e8ae46de3185e9b5', 'sql', '', 'EXECUTED', NULL, NULL, '3.10.2', '9635572458');

-- Changeset schemasql/000040-changelog.sql::raw::includeAll
insert into users values(2, "user", "psw");
insert into users values(3, "usern", "psw");
insert into users values(6, "nome", "pass");

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('raw', 'includeAll', 'schemasql/000040-changelog.sql', CURRENT_TIMESTAMP, 4, '8:3097da9993349d3bff0d8e0abb6db702', 'sql', '', 'EXECUTED', NULL, NULL, '3.10.2', '9635572458');

-- Changeset schemasql/000050-changelog.sql::raw::includeAll
insert into users values(8, "name7", "psw");
insert into users values(9, "username7", "psw");
insert into users values(10, "nomeUtente", "pass");

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('raw', 'includeAll', 'schemasql/000050-changelog.sql', CURRENT_TIMESTAMP, 5, '8:bdf4932b776a2d7853137b8528a3c1f5', 'sql', '', 'EXECUTED', NULL, NULL, '3.10.2', '9635572458');

-- Release Database Lock
UPDATE DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

But then when i check into sqlite this is what i get:

image

In short it’s missing the quick_dial table and a lot of the users (this is just a test, that’s why I’m using plaintext “passwords”), any idea? By documentation this should work, but it’s not. I also have to use plain SQL since if I use --liquibase formatted sql that file doesn’t get executed for some weird reason. Even the most basic setup, following the Documentation step by step gives me issues, I don’t know what I’m doing wrong tbh

New update on this, so if someone stumbles on this thread might solve his problems. In short, what was causing the main issue of the --liquibase formatted sql files being ignored was because I wasn’t following the namescheme *.databasetype.sql (so, for example 0010-changelog.sqlite.sql, in my case). Now that my files are all with the correct names and all using --liquibase formatted sql everything works fine. Sadly this name scheme is not explained in the official documentation (unless I’ve missed it), but I’ve already opened an issue on GitHub so it should be taken care of, when the developers have some time to address it :slight_smile:

The importance of namescheme was in Liquibase fundamental Training. What it did not say explicitly is that “it will not work if not in THAT format” . I have not combed through documentation. But I will run a local test for this

2 Likes

Hey @hope99 and @daniel_bertagnolli, you are absolutely right on the naming convention and I faced that frustration too. I will reach out to our docs team (cc @AristyCat).

1 Like

@ronak

if I can be of assistance , please let me know. I need to start contributing to the project Liquibase and Documentation seems to be the low hanging fruit!

1 Like

@hope99 to be honest I wanted to follow the liquibase univeristy course to get certified but never found the time in the end. I only read the written documentation on the website and on https://docs.liquibase.com .

What it did not say explicitly is that “it will not work if not in THAT format”

It might be an issue with sqlite3 only, I just tested it with that since it’s what I’m using on the project.

Thank you @ronak for letting them know! It’s a small thing but it can get frustrating, and I found out how to fix it by chance ahah

I’d offer my help too with the Docs but my english it’s not good enough to write documentation for everyone right now

@hope99, thanks! I’ve seen you on Discord and we can totally use the help, let me dig up that link on how to update docs. @daniel_bertagnolli your english seem great! However, let me know your native language, perhaps you can answer questions in that language :slight_smile:

@ronak thank you! I’m Italian, I’ll gadly help the community if some italian needs it, if I’ll have the knowledge to, ofcourse

The Liquibase university fundamentals course is very engaging and there are short videos followed by relevant documentation which comes in handy when you want to go back and check for something and I personally found it very helpful.
The videos are elegantly made and with doodly kind of content its more engaging than the ones I have done in past.

The questions are relevant after each topic and I found the course useful even after having used Liquibase for over a year now.

I would say , keep at it and slowly get it done.

Here’s how to update the Liquibase Docs:

However, I believe @AristyCat is developing an easier way to update the docs.

-Ronak

1 Like

@daniel_bertagnolli
I tried your scenario with h2 database and I was able to run sql formatted sql using master change log on CLI update
my changelog file is below and command used is :
liquibase --changeLogFile myChangeLog_includesql.xml update

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<include file="changelog_2.sql" />

and my code file changelog_2.sql contains

–liquibase formatted sql

–changeset bob:3
create table test3(
id int primary key,
name varchar(255)
);

so it does not really need the datatype type (in my case h2 in the naming scheme)

have you resolved your issue ? what is version of liquibase ?’
mine is : Liquibase Version: 3.10.2

1 Like