How to solve SchemaVersionTrackingException in dbdeploy due to no default value in delta_set?

Question

I am trying to do automatic DB migration. I am using dbdeploy for the same. I followed the steps in this link http://blog.codeborne.com/2012/09/using-dbdeploy-in-gradle.html

I created the change log table as:

CREATE TABLE changelog (
  change_number INTEGER NOT NULL,
  delta_set VARCHAR(10) NOT NULL,
  start_dt TIMESTAMP NOT NULL,
  complete_dt TIMESTAMP NULL,
  applied_by VARCHAR(100) NOT NULL,
  description VARCHAR(500) NOT NULL
);

ALTER TABLE changelog ADD CONSTRAINT Pkchangelog PRIMARY KEY (change_number, delta_set);

The updateDatabase task in build.gradle is:

task updateDatabase << {
    ant.dbdeploy(driver: dbDriver,
            url: dbUrl,
            userid: dbUsername,
            password: dbPassword,
            dir: './src/main/resources/deploy/sql',
            dbms: 'mysql'

    )
}

When I do gradle updateDatabase, I get com.dbdeploy.exceptions.SchemaVersionTrackingException: Could not update change log because: Field 'delta_set' doesn't have a default value.

I tried assigning 'main' as default value in the change table log file as:

delta_set VARCHAR(10) NOT NULL DEFAULT 'Main'

But, I still got the same exception.

I also removed the delta_set attribute, I got the same exception. This really confused me.

I am completely new to datamigration. So, any help regarding this error and how I should go about it will be deeply appreciated.

Thank you in advance.

Answer 1

The DBDeploy documentation isn't very explicit about this, but the changelog table format changed between versions 2.X and 3.X (see the upgrade instructions). I suspect that you are using DBDeploy 3.X.

You need to:

1. Remove the old changelog table:

   DROP TABLE changelog;
   

2. Recreate it using the new format:

   CREATE TABLE changelog (
     change_number INTEGER NOT NULL,
     complete_dt TIMESTAMP NOT NULL,
     applied_by VARCHAR(100) NOT NULL,
     description VARCHAR(500) NOT NULL
   );
   
   ALTER TABLE changelog ADD CONSTRAINT Pkchangelog PRIMARY KEY (change_number);
   

After this, everything should work.