Sqlite constraint violation updating databasechangelog

General Discussion
1

I’m trying to use liquibase to create some basic tables in an sqlite db but it’s giving me an error when it tries to insert the the changeset log in the DATABASECHANGELOG table.

This is my changelog in YAML format:

databaseChangeLog:
  - changeSet:
    id: 1
    author: knightsg
    changes:
      - createTable:
          tableName: tracked_posts
          columns:
            - column:
              name: id
              type: text
              constraints:
                primaryKey: true
                nullable: false
            - column:
              name: parsed_at
              type: timestamp
              constraints:
                primaryKey: false
                nullable: false
                defaultValue: 'CURRENT_TIMESTAMP'
      - createTable:
          tableName: checks
          columns:
            - column:
              name: name
              type: text
              constraints:
                primaryKey: true
                nullable: false
            - column:
              name: count
              type: int
              constraints:
                primaryKey: false
                nullable: false
                defaultValue: 0
            - column:
              name: change
              type: int
              constraints:
                primaryKey: false
                nullable: false
                defaultValue: 0
            - column:
              name: value
              type: real
              constraints:
                primaryKey: false
                nullable: false
                defaultValue: 0.0
            - column:
              name: parsed_at
              type: timestamp
              constraints:
                primaryKey: false
                nullable: false
                defaultValue: 'CURRENT_TIMESTAMP'

The error i get is as follows:

Unexpected error running Liquibase: [SQLITE_CONSTRAINT] Abort due to constraint violation (NOT NULL constraint failed: DATABASECHANGELOG.ID) [Failed SQL: (19) INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES (NULL, NULL, ‘changelog/baseline.yml’, CURRENT_TIMESTAMP, 1, ‘8:d41d8cd98f00b204e9800998ecf8427e’, ‘empty’, ‘’, ‘EXECUTED’, NULL, NULL, ‘4.2.2’, ‘2838090685’)

For some reason liquibase is trying to insert the ID and author values as null. Does anyone know how to fix this?

Thanks

1 Like
2

@knightsg , the current changelog consists of one changeset containing 2 DDL statements (two create table statements). This is a bit incorrect as a changeset should be as atomic as possible (in other words, have one changeset/SQL statement).
The issue you encounter might come from a behavior that SQL Lite might have with regard to commiting the transactions.
Given the fact that each changeset is “wrapped” in it’s own transaction and the fact that each changeset is coming with an insert into databasechangelog table (that happens “under the hood”, after the actual SQL Statement it ran), the second create table could (auto)commit the changeset transaction before Liquibase expects to, therefore not having at that time all info available (id, author). This might be seen if you run updateSQL instead of update Liquibase call (update SQL displays all SQL statements Liquibase intends to run against your target DB without doing any comit).

Long story short, a potential fix is to split the changelog into 2 changesets, one for each table creation.
So you have:

databaseChangeLog:
  - changeSet:
    id: 1
    author: knightsg
    changes:
      - createTable:
          tableName: tracked_posts
[.. rest of code..]

  - changeSet:
    id: 2
    author: knightsg
    changes:
      - createTable:
          tableName: checks

[…rest of code… ]

Hope this helps. Cheers!

1 Like
3

Thanks Eduardo, for the comprehensive reply. I had actually tried separating the 2 table creates before I submitted this topic but forgot to mention that, so my apologies. Unfortunately, it still doesn’t work and gives me the exact same error.

I did try running the command with ‘updateSQL’ as you recommended, though, and this is what it output:

Starting Liquibase at 17:12:52 (version 4.2.2 #36 built at 2020-12-09 20:07+0000)
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: changelog/baseline.yml
-- Ran at: 2/9/21, 5:12 PM
-- Against: null@jdbc:sqlite:/tmp/wsb.db
-- Liquibase version: 4.2.2
-- *********************************************************************

-- 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 = 'ff0bf75d0b13 (172.17.0.2)', LOCKGRANTED = '2021-02-09 17:12:53.852' 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 changelog/baseline.yml::null::null
INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES (NULL, NULL, 'changelog/baseline.yml', CURRENT_TIMESTAMP, 1, '8:d41d8cd98f00b204e9800998ecf8427e', 'empty', '', 'EXECUTED', NULL, NULL, '4.2.2', '2890774185');

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

Liquibase command 'updateSQL' was executed successfully

I’m not 100% sure, but shouldn’t it be outputting the actual create table sql statements as well?

Thanks,
Guy

4

I made a test on my side with your yaml file and the error reproduced. Then I did something that caught my eye at the very first moment (but “forgot” about my initial thought after validating your yaml online - I think that gave me a false positive).
I managed to fix your issue by properly indentating the YAML file. It seems to be an YAML formatting issue (again, even though it seems correct as you have it also, it actually isn’t).
To be safe always with YAML files I suggest you take the tab way for each node. Something like below (please add back the rest of the columns)

databaseChangeLog:
- changeSet:
    id: 1
    author: knightsg
    changes:
    - createTable:
        tableName: tracked_posts
        columns:
        - column:
            name: id
            type: text
            constraints:
                primaryKey: true
                nullable: false
        - column:
            name: parsed_at
            type: timestamp
            constraints:
                primaryKey: false
                nullable: false
- changeSet:
    id: 2
    author: knightsg
    changes:
    - createTable:
        tableName: checks
        columns:
        - column:
            name: name
            type: text
            constraints:
                primaryKey: true
                nullable: false
        - column:
            name: count
            type: int
            constraints:
                primaryKey: false
                nullable: false
                defaultValue: 0

Should work now (hopefully :smiley: )

PS: I don’t know your business logic for the tables but I am not sure it’s a good idea to have the tables PKs on text column(s). Try to use int (or related) types instead.

1 Like
5

Thanks, it was the alignment as you correctly identified. I just needed to push the stuff under -changeset: over by one tab space.

As for the PK, thanks for the info - this isn’t a business project, just something I’m messing around with but I’ll keep your recommendation in mind.

Thanks again,
Guy

1 Like