Overview

Often, when you are upgrading to a higher Kill Bill version, you would also need to upgrade the Kill Bill/plugin database. This document is a step-by-step guide on how the database upgrade can be performed.

Prerequisites

Ensure that you have kpm installed.

Upgrading Kill Bill Database

Step 1 - Obtain the migrations to run

The kpm migrate command can be used to obtain database migrations.

Command

kpm migrations github_repository from_tag to_tag

Example

To download the migration files to migrate from Kill Bill 0.22.32 to 0.24.0:

kpm migrations killbill killbill-0.22.32 killbill-0.24.0

This command creates a temporary folder with the migration SQL statements and displays its path.

Note
 Note: Because the implementation relies on the GitHub API, unauthenticated requests are subject to rate limiting. To work around it, generate a token via https://github.com/settings/tokens (default public, read-only, permissions will work) and specify it to KPM via kpm migrations killbill killbill-0.22.32 killbill-0.24.0 --token=TOKEN

Step 2 - Execute the migrations

The migrations can be executed either manually or using the Flyway command-line tool.

Executing migrations manually

Execute the SQL scripts obtained in Step 1 directly in the database.

Executing migrations via the Flyway Command-Line Tool

Migrations can also be executed using the Flyway command-line tool.

  1. Install Flyway Command-Line Tool:

    Follow the installation guide here: Flyway CLI Installation.

  2. Run the Baseline Command (First-Time Use Only):

    If Flyway is being used the first time, run the baseline command to create the flyway_schema_history table.

    Command

    flyway -url=jdbc:mysql://127.0.0.1:3306/killbill -user=<db_user> -password=<db_password> baseline

    Example

    flyway -url=jdbc:mysql://127.0.0.1:3306/killbill -user=root -password=killbill baseline

  3. Run the migrate command to execute the migrations.

    Command

    flyway -url=jdbc:mysql://127.0.0.1:3306/<db_name> -user=<db_user> -password=<db_password> -locations=filesystem:<migrations_path> migrate

    Example

    flyway -url=jdbc:mysql://127.0.0.1:3306/killbill -user=root -password=killbill -locations=filesystem:C:/var/migrations migrate

Additional Notes for Production Environments

In production environments, database access is often restricted and developers don’t necessarily have rights to execute DDL commands (i.e. CREATE, ALTER, DROP, etc. statements). In such cases, migrations can be executed as follows:

  1. Create the flyway_schema_history table manually:

    CREATE TABLE `flyway_schema_history` (
  `installed_rank` int(11) NOT NULL,
  `version` varchar(50) DEFAULT NULL,
  `description` varchar(200) NOT NULL,
  `type` varchar(20) NOT NULL,
  `script` varchar(1000) NOT NULL,
  `checksum` int(11) DEFAULT NULL,
  `installed_by` varchar(100) NOT NULL,
  `installed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `execution_time` int(11) NOT NULL,
  `success` tinyint(1) NOT NULL,
  PRIMARY KEY (`installed_rank`),
  KEY `flyway_schema_history_s_idx` (`success`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into flyway_schema_history (installed_rank, version, description, type, script, installed_by, installed_on, execution_time, success) VALUES (1, 1, '<< Flyway Baseline >>', 'BASELINE', '<< Flyway Baseline >>', 'admin', NOW(), 0, 1);

  2. Run a Dry Run (if needed for review before applying migrations):

    Flyway CLI does not support dryRun directly, but you can use -outputType=json to review pending migrations:

    Command

    flyway -url=jdbc:mysql://127.0.0.1:3306/<db_name> -user=<db_user> -password=<db_password> info -outputType=json

    Example

    flyway -url=jdbc:mysql://127.0.0.1:3306/killbill -user=root -password=password -locations=filesystem:C:/var/migrations info -outputType=json

    This will generate a JSON output similar to the following, which includes details such as the migration version, description, and the file path containing the SQL queries that will be executed:

    {
"schemaVersion": "20221118152343",
"schemaName": "",
"migrations": [
{
"category": "",
"version": "1",
"rawVersion": "1",
"description": "<< Flyway Baseline >>",
"type": "BASELINE",
"installedOnUTC": "2025-02-06T05:58:28Z",
"state": "Baseline",
"undoable": "No",
"filepath": "",
"undoFilepath": "",
"installedBy": "root",
"executionTime": 0
},
{
"category": "Versioned",
"version": "20220208000000",
"rawVersion": "20220208000000",
"description": "change usage and catalog attrs type",
"type": "SQL",
"installedOnUTC": "2025-02-07T07:29:38Z",
"state": "Success",
"undoable": "No",
"filepath": "/tmp/d20250211-43349-n5ijvx/V20220208000000__change_usage_and_catalog_attrs_type.sql",
"undoFilepath": "",
"installedBy": "root",
"executionTime": 117
},
{
"category": "Versioned",
"version": "2022070713524522",
"rawVersion": "2022070713524522",
"description": "record date time",
"type": "SQL",
"installedOnUTC": "",
"state": "Pending",
"undoable": "No",
"filepath": "/tmp/d20250211-43349-n5ijvx/V2022070713524522__record_date_time.sql",
"undoFilepath": "",
"installedBy": "",
"executionTime": 0
}
],
"flywayVersion": "9.22.3",
"database": "test",
"allSchemasEmpty": false,
"timestamp": "2025-02-11T17:58:06.028141481",
"operation": "info",
"exception": null,
"licenseFailed": false,
"jsonReport": "/home/killbill/Downloads/flyway-commandline-9.22.3-linux-x64/flyway-9.22.3/report.json",
"htmlReport": "/home/killbill/Downloads/flyway-commandline-9.22.3-linux-x64/flyway-9.22.3/report.html"
}

    This helps in reviewing the changes before executing them.

Upgrading Plugin Database

Step 1 - Obtain the migrations to run

The kpm migrate command can be used to obtain database migrations.

Command

kpm migrations github_repository from_tag to_tag

Example

To download the migration files to migrate the analytics plugin from 8.0.0 to 8.1.0:

kpm migration killbill-analytics-plugin analytics-plugin-8.0.0 analytics-plugin-8.1.0

This command creates a temporary folder with the migration SQL statements and displays its path.

Note
 Note: Because the implementation relies on the GitHub API, unauthenticated requests are subject to rate limiting. To work around it, generate a token via https://github.com/settings/tokens (default public, read-only, permissions will work) and specify it to KPM via kpm migrations killbill killbill-0.22.32 killbill-0.24.0 --token=TOKEN

Step 2 - Execute the migrations

The migrations can be executed either manually or using the Flyway command-line tool.

Executing migrations manually

Execute the SQL scripts obtained above directly on your database.

Executing migrations via the Flyway Command-Line Tool

Migrations can also be executed using the Flyway command-line tool.

  1. Install Flyway Command-Line Tool:

    Follow the installation guide here: Flyway CLI Installation.

  2. Run the Baseline Command (First-Time Use Only):

    If Flyway is being used the first time, run the baseline command to create the flyway_schema_history table.

    Command

    flyway -url=jdbc:mysql://127.0.0.1:3306/killbill -user=<db_user> -password=<db_password> -table=<plugin_name>_schema_history baseline

    Example

    flyway -url=jdbc:mysql://127.0.0.1:3306/killbill -user=root -password=killbill -table=analytics_schema_history baseline

  3. Run the migrate command to execute the migrations.

    Command

    flyway -url=jdbc:mysql://127.0.0.1:3306/<db_name> -user=<db_user> -password=<db_password> -locations=filesystem:<migrations_path> -table=<plugin_name>_schema_history migrate

    Example

    flyway -url=jdbc:mysql://127.0.0.1:3306/killbill -user=root -password=killbill -locations=filesystem:C:/var/migrations -table=analytics_schema_history migrate

Additional Notes for Production Environments

In production environments, database access is often restricted and developers don’t necessarily have rights to execute DDL commands (i.e. CREATE, ALTER, DROP, etc. statements). In such cases, migrations can be executed as follows:

  1. Create the <plugin_name>_schema_history table manually:

    CREATE TABLE `<plugin_name>_schema_history` (
  `installed_rank` int(11) NOT NULL,
  `version` varchar(50) DEFAULT NULL,
  `description` varchar(200) NOT NULL,
  `type` varchar(20) NOT NULL,
  `script` varchar(1000) NOT NULL,
  `checksum` int(11) DEFAULT NULL,
  `installed_by` varchar(100) NOT NULL,
  `installed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `execution_time` int(11) NOT NULL,
  `success` tinyint(1) NOT NULL,
  PRIMARY KEY (`installed_rank`),
  KEY `schema_history_s_idx` (`success`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into <plugin_name>_schema_history (installed_rank, version, description, type, script, installed_by, installed_on, execution_time, success) VALUES (1, 1, '<< Flyway Baseline >>', 'BASELINE', '<< Flyway Baseline >>', 'admin', NOW(), 0, 1);

    Ensure that you replace <plugin_name> with the name of a plugin like analytics.

  2. Run a Dry Run (if needed for review before applying migrations):

    Flyway CLI does not support dryRun directly, but you can use -outputType=json to review pending migrations:

    Command

    flyway -url=jdbc:mysql://127.0.0.1:3306/<db_name> -user=<db_user> -password=<db_password> -table=<plugin_name>_schema_history info -outputType=json

    Example

    flyway -url=jdbc:mysql://127.0.0.1:3306/killbill -user=root -password=password -locations=filesystem:C:/var/migrations-table=analytics_schema_history info -outputType=json

    This will list pending migrations without applying them.