Yugabyte-db: [YSQL] Feature Support - Liquibase Migration

[ramanans]

Need Support for Liquibase Migration

[ttyusupov]

Added some more context from user:

I used this example code ( https://codeload.github.com/juliuskrah/java-crud/zip/liquibase-hibernate-jpa ) and made changes to point to YugaByte running locally. The databasechangeloglock table is created, but there is no data in it. Has anyone used liquibase migration successfully?

The code just stops at "[DEBUG] 2019-07-16 11:58:12.995 [main] PersonRepositoryTest - Starting liquibase migration..." line...

[ttyusupov]

I've tried to reproduce locally with following diff based on input from @ramanans :

19:31 $ diff -r java-crud-liquibase-hibernate-jpa java-crud-liquibase-hibernate-jpa-orig
diff -r java-crud-liquibase-hibernate-jpa/pom.xml java-crud-liquibase-hibernate-jpa-orig/pom.xml
26,30c26,30
< <dependency>
<             <groupId>org.postgresql</groupId>
<             <artifactId>postgresql</artifactId>
<             <version>42.2.1</version>
<         </dependency>
---
>       <dependency>
>           <groupId>com.h2database</groupId>
>           <artifactId>h2</artifactId>
>           <version>${h2.version}</version>
>       </dependency>
67c67
< </project>
---
> </project>
\ No newline at end of file
diff -r java-crud-liquibase-hibernate-jpa/src/main/resources/META-INF/persistence.xml java-crud-liquibase-hibernate-jpa-orig/src/main/resources/META-INF/persistence.xml
29,31c29,32
<           <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
<           <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5433/postgres" />
<           <property name="javax.persistence.jdbc.user" value="postgres" />
---
>           <property name="javax.persistence.jdbc.driver" value="org.h2.Driver" />
>           <!-- <property name="javax.persistence.jdbc.url" value="jdbc:h2:tcp://localhost/~/test" /> -->
>           <property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MVCC=true" />
>           <property name="javax.persistence.jdbc.user" value="sa" />
41c42
< </persistence>
---
> </persistence>

But got different results:

$ <yb-dir>/bin/yb-ctl --rf 1 start

19:32 $ mvn clean install
....
[DEBUG] 2019-07-16 19:32:56.597 [main] PersonRepositoryTest - Starting liquibase migration...
[ERROR] 2019-07-16 19:32:57.353 [main] PersonRepositoryTest - Error occured in execution: liquibase.exception.DatabaseException: ERROR: Invalid argument: Invalid column number -102 [Failed SQL: CREATE TABLE public.databasechangeloglock (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]

The same result could be achieved by trying the same table create query from <yb-dir>/bin/ysqlsh.

[ndeodhar]

@ramanans I tried this on YB version 1.2.12 and got this error:

2019-07-16 10:28:24.621 PDT [36467] ERROR:  column "createddate" of relation "person" does not exist at character 21
2019-07-16 10:28:24.621 PDT [36467] STATEMENT:  insert into Person (createdDate, dateOfBirth, firstName, lastName, modifiedDate, id) values ($1, $2, $3, $4, $5, $6)

This is because postgres column names are case-sensitive. So, table person is created with column createdDate but liquibase tries to insert data using column name createddate.

To fix this, I added this property to src/main/resources/META-INF/persistence.xml:
<property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy" />

Application then runs successfully and I see the change logs as expected. See sample output:

postgres=# select * from person;
 id | firstname | lastname | dateofbirth |       createddate       |      modifieddate       
----+-----------+----------+-------------+-------------------------+-------------------------
  3 | Loretta   | Krah     | 1992-08-12  | 2019-07-16 10:56:11.873 | 
  5 | Julius    | Krah     | 1990-04-04  | 2019-07-16 10:56:11.946 | 
  4 | Julius    | Krah     | 1990-04-04  | 2019-07-16 10:56:11.882 | 
  1 | Abeiku    | Krah     | 1990-04-04  | 2019-07-16 10:56:11.828 | 2019-07-16 10:56:11.953
(4 rows)

postgres=# select * from databasechangelog;
 id | author |    filename     |        dateexecuted        | orderexecuted | exectype |               md5sum               |                  description                   |      comments       | tag | liquibase | contexts | labels | deployment_id 
----+--------+-----------------+----------------------------+---------------+----------+------------------------------------+------------------------------------------------+---------------------+-----+-----------+----------+--------+---------------
 1  | julius | dbChangelog.xml | 2019-07-16 10:56:11.820683 |             2 | EXECUTED | 7:763edbd3b7fe3d76f1cbdb0b4cc9b92f | createTable tableName=person                   | Create Person table |     | 3.5.3     |          |        | 3299771433
 0  | julius | dbChangelog.xml | 2019-07-16 10:56:11.538276 |             1 | EXECUTED | 7:8386df6a2ccbb44a27eef094c3eaf0a1 | createSequence sequenceName=hibernate_sequence |                     |     | 3.5.3     |          |        | 3299771433
(2 rows)

postgres=# select * from databasechangeloglock;
 id | locked | lockgranted | lockedby 
----+--------+-------------+----------
  1 | f      |             | 
(1 row)

Let me know when you get a chance to try these changes and if you need any further help.

[ndeodhar]

@ttyusupov The error that you saw when you tried is because of a backward-incompatible change that's present on master. If you create a new cluster and try this again, you won't see that error. Alternatively, you can run yb_build.sh reinitdb or yb_build.sh release reinitdb and then try again.

[ddorian]

I believe this is fixed after FOR UPDATE in 2.0.6 . At least confirmed by 1 user in slack.
@ramanans can you retry ?

[ddorian]

Just confirmed that migrations work with YugabyteDB 2.1.2.0, Liquibase 3.8.8 and postgresql-42.2.12.jar driver.

[rkarthik007]

Hi @ddorian - would be super useful to write the details of what you tried here as well, perhaps put it into our docs with screenshots and add a link here. Thanks!

[ddorian]

I followed tutorial on https://www.liquibase.org/documentation/tutorials/postgresql.html.
Downloaded liquibase 3.8.8 https://download.liquibase.org/ and postgresql-jdbc 42.2.12.jarhttps://jdbc.postgresql.org/download.html.

Create a new empty database on YSQL:

./bin/ysqlsh
ysqlsh (11.2-YB-2.1.2.0-b0)
Type "help" for help.

yugabyte=# create database liquibase;
CREATE DATABASE
yugabyte=# \c liquibase
You are now connected to database "liquibase" as user "yugabyte".

Create a ydb.changelog.xml file:

<?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"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">


    <changeSet id="1" author="bob">
        <createTable tableName="department">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="active" type="boolean"                     
              defaultValueBoolean="true"/>
        </createTable>
   </changeSet>

  </databaseChangeLog>

Run migrations:

./liquibase \
>   --driver=org.postgresql.Driver \
>   --classpath=postgresql-42.2.12.jar \
>   --url="jdbc:postgresql://127.0.0.1:5433/liquibase" \
>   --changeLogFile=ydb.changelog.xml \
>   --username=postgres \
>   --password=postgres \
> update
Liquibase Community 3.8.8 by Datical
Liquibase: Update has been successful.

Verify results:

liquibase=# \d+
                             List of relations
 Schema |         Name          | Type  |  Owner   |  Size   | Description 
--------+-----------------------+-------+----------+---------+-------------
 public | databasechangelog     | table | postgres | 0 bytes | 
 public | databasechangeloglock | table | postgres | 0 bytes | 
 public | department            | table | postgres | 0 bytes | 
(3 rows)