Support environment for Liquibase & Informix using Docker

Goal

To run liquibase against Informix quickly on your local system to reproduce and work support issues.

Methodology

Will be using Docker to create an Informix container. Install the version of liquibase you want to use (switch to the correct version if you have a side by side setup of multiple liquibase versions). Pull docker image from docker hub and docker run to run a docker registered Informix image.

 

Install liquibase

…. on your local system.

You can download the software here. I will be using side x side installs on a mac. So I downloaded the last few versions:

 

Ronaks-MacBook-Pro:opt root# pwd /usr/local/opt Ronaks-MacBook-Pro:opt root# mkdir liquibase-3.10.1 Ronaks-MacBook-Pro:opt root# tar -xzvf liquibase-3.10.1.tar.gz -C liquibase-3.10.1 ... drwxr-xr-x 9 root wheel 288B Jul 9 11:17 ./ drwxr-xr-x 6 root wheel 192B Jul 8 10:21 ../ lrwxr-xr-x 1 root wheel 16B Jul 9 11:17 liquibase@ -> liquibase-3.10.1 -rw-r--r--@ 1 ronak staff 7.0M Jun 29 17:08 liquibase-3.10.0.tar.gz drwxr-xr-x 14 root wheel 448B Jul 9 11:16 liquibase-3.10.1/ -rw-rw-rw-@ 1 ronak staff 7.1M Jul 8 14:44 liquibase-3.10.1.tar.gz -rw-rw-rw-@ 1 ronak staff 7.0M Jul 9 11:07 liquibase-3.8.9.tar.gz -rw-rw-rw-@ 1 ronak staff 7.0M Jul 9 11:06 liquibase-3.9.0.tar.gz -rw-rw-rw-@ 1 ronak staff 6.4M Jul 9 11:02 liquibase-4.0.0-beta2.tar.gz Ronaks-MacBook-Pro:opt root# which liquibase /usr/local/opt/liquibase/liquibase Ronaks-MacBook-Pro:opt root# liquibase --version Starting Liquibase at Thu, 09 Jul 2020 11:17:42 CDT (version 3.10.1 #17 built at Wed Jul 01 06:58:05 UTC 2020) Liquibase Version: 3.10.1 Liquibase Community 3.10.1 by Datical Running Java under /Library/Java/JavaVirtualMachines/jdk1.8.0_251.jdk/Contents/Home/jre (Version 1.8.0_251)

Also note I downloaded the tar.gz not the dmg. I had some issues with that when switching versions so I just went for more control using tar.gz. and symlinks.

Running Informix via Docker.

I used these instructions from Docker Hub. These instructions only get you part way there, here’s what I did:

  1. Run:

    1. docker pull ibmcom/informix-developer-database

    2. docker run --name ronakInformix -e DBSERVERNAME=dbserver -e LICENSE=accept -e LOCAL=true -p 9088:9088 -e DBA_USER=test -e DBA_PASS=test ibmcom/informix-developer-database

  2. I had to create the testdb for this example, so open a docker bash session (after you run the above docker pull, the output should tell you how to open a docker bash session for this container, ex. docker exec -it 55e1345371b4 bash)

    1. Create a database called testdb (you will also end up verifying your connection information here, I used informix:in4mix, seems like that is the image default)

    2. Your database must use ANSI logging, here is the query to create the db (you can do that after you connect to the next section) CREATE DATABASE ansiDatabase WITH LOG MODE ANSI;

  3. Note, at the time this pulls down IBM Informix Dynamic Server Version 14.10.FC3DE.

Connect to Database

You can use sqlplus or a SQL Browser. I used DBeaver, and here was my connection config:

DBeaver

Can’t figure out what your server name or port is? As noted here, Try opening a bash prompt to your docker container and vi $INFORMIXSQLHOSTS

Prepare Databases/Schemas/objects

Now that we have a Docker container that is running Informix, and a test db named testdb.

Configure Liqubase Project

You can use any liquibase project you want, i use one I have committed to source

  1. again, make sure you are in the root of ronak_lb_projects

  2. cd ronak_lb_projects/informix

  3. Make sure you install the jdbc driver for informix in the ronak_lb_projects/drivers folder

  4. verify liquibase.properties file has URL and passwords correct, mine looked like this:

    1.  

      changeLogFile=infromixlog.xml #### Primary Database Information #### # The primary database is the database you want to use when doing an update, or for performing comparisons. # Enter the URL of the source database url=jdbc:informix-sqli://localhost:9088/testdb:INFORMIXSERVER=dbserver # Enter the username for your source database. username: informix # Enter the password for your source database. password: in4mix classpath: ../drivers/jdbc-4.50.4.1.jar logLevel: ERROR
  5. test by running liquibase updateSQL, I used the following command:

    1. liquibase updateSQL

    2. if you get the following error, then you probably did not create your db ANSI logging compliant (see above in the docker setup)

    3.  

      Liquibase Pro 4.0.0 by Datical licensed to Liquibase Pro Customer until Mon May 17 19:00:00 CDT 2021 Starting Liquibase at 15:11:04 (version 4.0.0 #19 built at 2020-07-13 19:45+0000) Unexpected error running Liquibase: Migration failed for change set infromixlog.xml::1595879345576-1::ronak (generated): Reason: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: java.sql.SQLException: Transactions not supported For more information, please use the --logLevel flag [2020-07-27 15:11:05] SEVERE [liquibase.integration] Unexpected error running Liquibase: Migration failed for change set infromixlog.xml::1595879345576-1::ronak (generated): Reason: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: java.sql.SQLException: Transactions not supported liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set infromixlog.xml::1595879345576-1::ronak (generated): Reason: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: java.sql.SQLException: Transactions not supported at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:112) at liquibase.Liquibase$1.run(Liquibase.java:206) at liquibase.Scope.lambda$child$0(Scope.java:159) at liquibase.Scope.child(Scope.java:170) at liquibase.Scope.child(Scope.java:158) at liquibase.Scope.child(Scope.java:137) at liquibase.Liquibase.runInScope(Liquibase.java:1790) at liquibase.Liquibase.update(Liquibase.java:183) at liquibase.Liquibase$2.run(Liquibase.java:280) at liquibase.Scope.lambda$child$0(Scope.java:159) at liquibase.Scope.child(Scope.java:170) at liquibase.Scope.child(Scope.java:158) at liquibase.Scope.child(Scope.java:137) at liquibase.Liquibase.runInScope(Liquibase.java:1790) at liquibase.Liquibase.update(Liquibase.java:265) at liquibase.Liquibase.update(Liquibase.java:257) at liquibase.integration.commandline.Main.doMigration(Main.java:1577) at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:316) at liquibase.Scope.lambda$child$0(Scope.java:159) at liquibase.Scope.child(Scope.java:170) at liquibase.Scope.child(Scope.java:158) at liquibase.Scope.child(Scope.java:137) at liquibase.Scope.child(Scope.java:183) at liquibase.Scope.child(Scope.java:187) at liquibase.integration.commandline.Main$1.run(Main.java:315) at liquibase.integration.commandline.Main$1.run(Main.java:166) at liquibase.Scope.child(Scope.java:170) at liquibase.Scope.child(Scope.java:144) at liquibase.integration.commandline.Main.run(Main.java:166) at liquibase.integration.commandline.Main.main(Main.java:145) Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set infromixlog.xml::1595879345576-1::ronak (generated): Reason: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: java.sql.SQLException: Transactions not supported at liquibase.changelog.ChangeSet.execute(ChangeSet.java:670) at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:49) at liquibase.changelog.ChangeLogIterator$2$1.run(ChangeLogIterator.java:99) at liquibase.Scope.lambda$child$0(Scope.java:159) at liquibase.Scope.child(Scope.java:170) at liquibase.Scope.child(Scope.java:158) at liquibase.Scope.child(Scope.java:137) at liquibase.Scope.child(Scope.java:183) at liquibase.Scope.child(Scope.java:187) at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:91) at liquibase.Scope.lambda$child$0(Scope.java:159) at liquibase.Scope.child(Scope.java:170) at liquibase.Scope.child(Scope.java:158) at liquibase.Scope.child(Scope.java:137) at liquibase.Scope.child(Scope.java:183) at liquibase.Scope.child(Scope.java:187) at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:64) ... 29 more Caused by: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: java.sql.SQLException: Transactions not supported at liquibase.database.AbstractJdbcDatabase.setAutoCommit(AbstractJdbcDatabase.java:1227) at liquibase.changelog.ChangeSet.execute(ChangeSet.java:534) ... 45 more Caused by: liquibase.exception.DatabaseException: java.sql.SQLException: Transactions not supported at liquibase.database.jvm.JdbcConnection.setAutoCommit(JdbcConnection.java:383) at liquibase.database.AbstractJdbcDatabase.setAutoCommit(AbstractJdbcDatabase.java:1225) ... 46 more Caused by: java.sql.SQLException: Transactions not supported at com.informix.util.IfxErrMsg.buildExceptionWithMessage(IfxErrMsg.java:416) at com.informix.util.IfxErrMsg.buildException(IfxErrMsg.java:397) at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:379) at com.informix.jdbc.IfxSqliConnect.setAutoCommit(IfxSqliConnect.java:2185) at liquibase.database.jvm.JdbcConnection.setAutoCommit(JdbcConnection.java:381)

Destroying the Docker Image

find your container by listing it:

docker container ls -a

Which should return something like this:

Use the container id to delete the image if you are done