Personal blog of Yzmir Ramirez

Database-as-code service is a new concept and gaining some popularity in recent years. As we already know, we have deployment solutions for application code. Managing and tracking application changes are quite easy with tools like Git and Jenkins.

Now this concept is applied in the database domain as well, assuming SQL as a code to manage database changes (DDL, DML) the same way that applications handle code. From a database standpoint, this allows tracing the history of modifications, allowing problems to be quickly detected and addressed.

What is Liquibase?

Liquibase is an open source, database-independent framework for deploying, managing, and tracking database schema changes. All the modifications or changes to the database are stored in text files (XML, YAML, JSON, or SQL) known as changesets. To specifically list database changes in order, Liquibase employs a changelog. The changelog serves as a record of changes and includes a list of changesets that Liquibase can execute on a target database.

Let’s see how we can set up Liquibase and perform some database changes with this tool.

Installing Liquibase

1. Download and extract Liquibase files.

shell> wget https://github.com/liquibase/liquibase/releases/download/v4.17.2/liquibase-4.17.2.tar.gz 
shell> mkdir /usr/local/bin/liquibase/ 
shell> sudo tar -xzvf liquibase-4.17.2.tar.gz --directory  /usr/local/bin/liquibase/

2. Define the installation directory to the environment path and add the same in “~/.bashrc” file as well.

export PATH=$PATH:/usr/local/bin/liquibase/liquibase

3. Validate the installation.

shell> liquibase --version

####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ## 
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ## 
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 15:04:16 (version 4.17.2 #5255 built at 2022-11-01 18:07+0000)
Liquibase Home: /usr/local/bin/liquibase
Java Home /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.352.b08-2.el7_9.x86_64/jre (Version 1.8.0_352)

Note – Java(JDK) needs to be set up on your system for Liquibase to function.

How to use Liquibase with MySQL

1. To use Liquibase and MySQL, we need the JDBC driver JAR file. Copy the jar file in liquibase internal library.

shell> wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-j-8.0.31.tar.gz 
shell> tar -xzvf mysql-connector-j-8.0.31.tar.gz 
shell> cp mysql-connector-j-8.0.31/mysql-connector-j-8.0.31.jar  /usr/local/bin/liquibase/internal/lib/

2. First, initialize a project with the options below.

shell> liquibase init project \ 
--project-dir=/home/vagrant/liquibase_mysql_project \ 
--changelog-file=file \ 
--format=sql \ 
--project-defaults-file=liquibase.properties \ 
--url=jdbc:mysql://localhost:3306/sbtest \ 
--username=root \ 
--password=Root@1234

Let’s understand what these parameters are.

--project-dir => project location where all files related to the project will be kept.
--changelog-file => file containing deployment changes.
--format =>format of deployment file (.sql,xml etc).
--project-defaults-file => liquibase property file.
--url => MySQL database url.
--username=root => database user name.
--password => database password.

3. We can then create a manual file (“changelog.sql”) in the project location and define the changeset. Other formats for defining the deployment changes include (.xml, .json, or .yaml). 

CREATE TABLE test_table (test_id INT, test_column VARCHAR(256), PRIMARY KEY (test_id))

4. Validate the connection to the database is successful. Inside the Liquibase project folder, run the below command.

shell> liquibase --username=root --password=Root@1234 --changelog-file=changelog.sql status

5. Inspect the SQL before execution.

shell> liquibase --changelog-file=changelog.sql update-sql

6. Finally, deploy the changes.

shell> liquibase --changelog-file=changelog.sql update

Output:

Starting Liquibase at 16:56:44 (version 4.17.2 #5255 built at 2022-11-01 18:07+0000) 
Liquibase Version: 4.17.2 
Liquibase Community 4.17.2 by Liquibase 
Running Changeset: changelog.sql::raw::includeAll 
Liquibase command 'update' was executed successfully.

7. Validate the changes in the database. Additionally, you observe below extra tables in the same database which captures a few more stats of the execution.

a)  Table: DATABASECHANGELOG

mysql> select * from DATABASECHANGELOG\G;
*************************** 1. row ***************************
ID: raw
AUTHOR: includeAll
FILENAME: changelog.sql
DATEEXECUTED: 2022-11-08 16:10:36
ORDEREXECUTED: 1
EXECTYPE: EXECUTED
MD5SUM: 8:155d0d5f1f1cb1c0098df92a8e92372a
DESCRIPTION: sql
COMMENTS: 
TAG: NULL
LIQUIBASE: 4.17.2
CONTEXTS: NULL
LABELS: NULL
DEPLOYMENT_ID: 7923831699

Note – Each changeset is tracked in the table as a row and is identified by the id, author, and filename fields.

b) Table: DATABASECHANGELOGLOCK

mysql> select * from DATABASECHANGELOGLOCK\G;
*************************** 1. row ***************************
ID: 1
LOCKED: 0x00
LOCKGRANTED: NULL
LOCKEDBY: NULL
1 row in set (0.00 sec)

Note – To guarantee that only one instance of Liquibase is running at once, Liquibase employs the DATABASECHANGELOGLOCK table.

8. By running the command “liquibase history” we can check the past deployments as well.

Liquibase Version: 4.17.2
Liquibase Community 4.17.2 by Liquibase
Liquibase History for jdbc:mysql://localhost:3306/sbtest

- Database updated at 11/8/22 4:10 PM. Applied 1 changeset(s), DeploymentId: 7923831699
changelog.sql::raw::includeAll

So here we have successfully deployed the changes in the target database. Next, we will see how we can track the changes and perform rollback operations.

In order to perform rollbacks with respect to certain DDL or  DML we need to add rollback changeset details in the .sql file.

Let’s see the steps to perform the rollback operations

1) Create the deployment file “deployment.sql” inside the project location with the below changeset details.

--liquibase formatted sql

--changeset AJ:1 labels:label1 context:context1
--comment: DDL creation

create table P1 (
id int primary key auto_increment not null,
name varchar(50) not null
);
--rollback DROP TABLE P1;

create table P2 (
id int primary key auto_increment not null,
name varchar(50) not null
);
--rollback DROP TABLE P2;

Option details:

AJ:1 => denotes author:id

labels:label1 => Specifies labels that are a general way to categorize changesets like contexts.

context:context1 => Executes the change if the particular context was passed at runtime. Any string can be used for the context name

2) Next, run the deployment. Run the below command inside the project location.

shell> liquibase --changelog-file=deployment.sql update

3) Now, add the tagging to manage rollback scenarios.

shell> liquibase tag version4

4) By default, the tagging will be added in the recent deployments. We can check the same in the below table.

mysql> select * from DATABASECHANGELOG\G;

ID: 1
AUTHOR: AJ
FILENAME: deployment.sql
DATEEXECUTED: 2022-11-28 07:09:11
ORDEREXECUTED: 3
EXECTYPE: EXECUTED
MD5SUM: 8:2e4e38d36676981952c21ae0b51895ef
DESCRIPTION: sql
COMMENTS: DDL creation
TAG: version4
LIQUIBASE: 4.17.2
CONTEXTS: context1
LABELS: label1
DEPLOYMENT_ID: 9619351799

5) Let’s roll back the executed changes. This command will revert all changes made to the database after the specified tag.

shell> liquibase --changelog-file=deployment.sql rollback version4

Output:

Starting Liquibase at 07:38:36 (version 4.17.2 #5255 built at 2022-11-01 18:07+0000)
Liquibase Version: 4.17.2
Liquibase Community 4.17.2 by Liquibase
Rolling Back Changeset: deployment.sql::1::AJ
Liquibase command 'rollback' was executed successfully.

Note – Post this activity, we don’t see those tables anymore in the database (P1,P2). As a rollback operation these tables were dropped now.

Alternatively, we can perform the rollback activity on the basis of timestamps as well. The below command is used to revert all changes made to the database from the current date to the date and time you specify. 

Eg,

shell> liquibase --changelog-file=deployment.sql  rollback-to-date 2022-11-27

Output:

Starting Liquibase at 07:18:26 (version 4.17.2 #5255 built at 2022-11-01 18:07+0000)
Liquibase Version: 4.17.2
Liquibase Community 4.17.2 by Liquibase
Rolling Back Changeset: deployment.sql::1::AJ
Liquibase command 'rollback-to-date' was executed successfully.

Liquibase integration with Percona Toolkit (pt-osc)

A Liquibase extension is available to enable the pt-online-schema-change feature of the Percona Toolkit. With the use of pt-osc rather than SQL, this extension substitutes several default changes. With the aid of the pt-online-schema-change tool, you can upgrade a database without locking any tables.

Let’s see the steps to use pt-osc with Liquibase extension

1) Download the Percona Liquibase jar file.

shell> wget https://github.com/liquibase/liquibase-percona/releases/download/v4.17.1/liquibase-percona-4.17.1.jar

2)  Copy the jar file to Liquibase internal library folder.

shell> sudo cp liquibase-percona-4.17.1.jar /usr/local/bin/liquibase/internal/lib/

3) Add the below changeset in changelog.xml file which basically adds one column (“osc”) in table:liq1.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xmlns:pro="http://www.liquibase.org/xml/ns/pro"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

<changeSet id="3" author="AJ">
<addColumn tableName="liq1"
xmlns:liquibasePercona="http://www.liquibase.org/xml/ns/dbchangelog-ext/liquibase-percona"
liquibasePercona:usePercona="true">
<column name="osc" type="varchar(255)"/>
</addColumn>
</changeSet>
</databaseChangeLog>

Note – here we mentioned using Percona=”true” which enable the DDL execution via pt-osc.

4) Validate the deployment changes before actual implementation. We can see below the pt-osc command reference which is going to be executed in the next phase. 

shell>  liquibase --changelog-file=changelog.xml update-sql

Output

Liquibase Community 4.17.2 by Liquibase
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: changelog.xml
-- Ran at: 11/28/22 8:28 AM
-- Against: root@localhost@jdbc:mysql://localhost:3306/liq
-- Liquibase version: 4.17.2
-- *********************************************************************

-- Lock Database
UPDATE liq.DATABASECHANGELOGLOCK SET `LOCKED` = 1, LOCKEDBY = 'localhost.localdomain (10.0.2.15)', LOCKGRANTED = NOW() WHERE ID = 1 AND `LOCKED` = 0;

-- Changeset changelog.xml::3::AJ
-- pt-online-schema-change --alter-foreign-keys-method=auto --nocheck-unique-key-change --alter="ADD COLUMN osc VARCHAR(255) NULL" --password=*** --execute h=localhost,P=3306,u=root,D=liq,t=liq1;

5) Finally, run the deployment.

shell>  liquibase --changelog-file=changelog.xml update

Summary

Apart from MySQL, Liquibase supports other popular databases like (PostgreSQL, Cassandra, and MongoDB). The developers benefit greatly from this since they can collaborate to write their own scripts that relate to the database and then commit them as part of their code. It maintains the versions of all the changes like any other version control change and supports branching and merging of SQL code.

Further reading

• [Liquibase installation]: https://docs.liquibase.com/install/liquibase-linux.html
• [Running MySQL & Liquibase with different options]: https://docs.liquibase.com/install/tutorials/mysql.html
• [JDBC URL options]: https://docs.liquibase.com/workflows/liquibase-community/using-jdbc-url-in-liquibase.html
• [Liquibase project setup]: https://docs.liquibase.com/commands/init/project.html
• [Rollback options]: https://docs.liquibase.com/workflows/liquibase-community/using-rollback.html
• [Liquibase-Percona]: https://docs.liquibase.com/workflows/liquibase-community/using-the-liquibase-percona-toolkit-extension.html

Recently, I helped a client that was having issues with deployments causing locking in their production databases.  At a high level, the two key components used in the environment were:

• Capistrano (scripted deployments) [website]
• Liquibase (database version control) [website]

At a high level, they currently used a CLI call to Liquibase as a sub-task within a larger deployment task.  The goal of this engagement was to modify that sub-task to run Liquibase in a non-blocking fashion as opposed to the default that just runs native ALTERS against the database.

As I wasn’t very familiar with Liquibase, I took this opportunity to learn more about it and it seems like a very valuable tool.  Essentially, it does the following:

• Adds two version control tables to your schema
• Reads an XML “changelog” file that has the schema changes
• Verifies which changes have yet to be applied
• Applies the changes in serial to the tables
• Records each change in the version control table as it happens

While this is a very powerful tool, as you dataset grows, this can be problematic.  As each change is run as an independent ALTER statement, consider a large (several million row) table that you are updating with multiple indexes – this can result in several lengthy blocking operations that can impact your application in other ways.

My first thought when hearing that table alters were locking up the production application was naturally to use Percona Toolkit’s pt-online-schema-change – but the challenge lied with the integration of the tools.  After some investigation and discussion, I found two approaches that seemed feasible to get pt-osc integrated:

1. Write a custom plugin for Liquibase
2. Utilize the updateSQL action to fetch the raw SQL and parse/process each statement

Due to time constraints, we landed on option 2.  In pseudocode, here is the approach that we took:

#Output is SQL string of all commands that Liquibase would run
sql = liquibase updateSQL
foreach sql as line
  switch (line)
    case blank/comment:
      next
    case INSERT/CREATE TABLE
      runNativeSql(line)
    case ALTER TABLE
      runPtOsc(line)
    case CREATE INDEX
      line = convertCreateToAlter(line)
      runPtOSC(line)

This turned out to be exactly what the client needed as it allowed the code deployment to still utilize the version control methods that were understood, but did so in a non-blocking manner.  The main caveat with this approach is that the application code needs to be backwards and forwards compatible with the changes.  As you now may see a long running background process, depending on when your code is actually deployed in the complete process, it may need to handle the database in different states.

Although this is a frequent scenario with deployments anyway, I just wanted make note that often times when changing schema, the application must be able to handle the database in different states.

Here is the link to the (sanitized) proof-of-concept code and some samples I used for testing: https://github.com/mbenshoof/liqui-cap-online.  Please note that I am by no means a Ruby developer so I assume there are plenty of coding optimizations that can be made – this was more of an exercise in combining the tools as opposed to a Ruby showcase.

The post Integrating pt-online-schema-change with a Scripted Deployment appeared first on MySQL Performance Blog.