Connect with Liquibase (Docker) to a SSH tunneled database

Question

I constructed an SSH tunnel to a MySQL database (I know that this can be done without a password, but this is not the question).

jfabianmeier@JFM-HP-2018:~$ sshpass -p mySuperPassword ssh -o StrictHostKeyChecking=no -M -S my-ctrl-socket -fNT -L 3306:mysql5:3306 myUserName@alfa3031.alfahosting-server.de
jfabianmeier@JFM-HP-2018:~$ ssh -S my-ctrl-socket -O check myUserName@alfa3031.alfahosting-server.de
Master running (pid=405)

Connecting to MySQL seems to work, at least without SSL (I don't know why, maybe the server only supports old protocols).

jfabianmeier@JFM-HP-2018:~$ mysql -h 127.0.0.1 -P 3306 -u web1444 -pmyDBPassword --ssl-mode=DISABLED
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 140637890
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| usr_web1444_1      |
| usr_web1444_2      |
| usr_web1444_3      |
| usr_web1444_4      |
| usr_web1444_5      |
+--------------------+
6 rows in set (0.26 sec)

mysql> exit
Bye

Liquibase does not work, though. I tried below Docker command, but cannot figure out what is wrong about it.

jfabianmeier@JFM-HP-2018:~$ docker run -e INSTALL_MYSQL=true --rm -v $(pwd):$(pwd) liquibase/liquibase:4.15 --url=jdbc:mysql://localhost:3306/usr_web1444_4?autoReconnect=true&useSSL=false --changeLogFile=~/changelogs/changelog.sql --username=web144 --password=myDBPassword update
[1] 408
-bash: --changeLogFile=~/changelogs/changelog.sql: No such file or directory
jfabianmeier@JFM-HP-2018:~$ Checksum verified. Installing mysql-connector-java-8.0.30.jar to /liquibase/lib/
mysql-connector-java-8.0.30.jar successfully installed in classpath.
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  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 09:56:38 (version 4.15.0 #4001 built at 2022-08-05 16:17+0000)
Liquibase Version: 4.15.0
Liquibase Community 4.15.0 by Liquibase
Missing required subcommand
Usage: liquibase [GLOBAL OPTIONS] [COMMAND] [COMMAND OPTIONS]
Command-specific help: "liquibase  --help"

Global Options
....

I would like to know whether my Liquibase command is just wrong, or the URL is wrong, or if the issue might be with the SSL problem above. I honestly do not understand the error message and was not able to find something helpful through Google.

jccampanero · Answer

If you are able to connect with the mysql client probably the tunnel is correctly configured.

The error seems to be motivated because Liquibase doesn't understand the command provided when invoking Docker from the command line:

docker run -e INSTALL_MYSQL=true --rm -v $(pwd):$(pwd) liquibase/liquibase:4.15 --url=jdbc:mysql://localhost:3306/usr_web1444_4?autoReconnect=true&useSSL=false --changeLogFile=~/changelogs/changelog.sql --username=web144 --password=myDBPassword update

As you can see, it is printing by console the error and the typical usage help:

Missing required subcommand
Usage: liquibase [GLOBAL OPTIONS] [COMMAND] [COMMAND OPTIONS]
Command-specific help: "liquibase  --help"

I tested the code and it seems to be motivated by the url argument.

Please, try providing that value enclosed within quotes:

docker run -e INSTALL_MYSQL=true --rm -v $(pwd):$(pwd) liquibase/liquibase:4.15 --url="jdbc:mysql://localhost:3306/usr_web1444_4?autoReconnect=true&useSSL=false" --changeLogFile=~/changelogs/changelog.sql --username=web144 --password=myDBPassword update

In addition, bash is reporting that Liquibase is unable to find your change logs:

-bash: --changeLogFile=~/changelogs/changelog.sql: No such file or directory

The Liquibase official Docker image documentation mentions the following when describing how to provide the necessary change logs:

The docker image has a /liquibase/changelog volume in which the directory containing the root of your changelog tree can be mounted. Your -- changeLogFile argument should list paths relative to this.

Following that advice, please, try providing the following docker command for running your migration (assuming your current directory contains changelogs/changelog.sql):

docker run -e INSTALL_MYSQL=true --rm -v $(pwd):/liquibase/changelog liquibase/liquibase:4.15 --url="jdbc:mysql://localhost:3306/usr_web1444_4?autoReconnect=true&useSSL=false" --changeLogFile=changelog/changelogs/changelog.sql --username=web144 --password=myDBPassword update

Please, note again the use of double quotes to enclose the url parameter.

If your tunnel is not working properly Liquibase will report something like the following:

####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ## 
##  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 17:22:56 (version 4.15.0 #4001 built at 2022-08-05 16:17+0000)
Liquibase Version: 4.15.0
Liquibase Community 4.15.0 by Liquibase

Unexpected error running Liquibase: Connection could not be created to jdbc:mysql://localhost:3306/usr_web1444_4?autoReconnect=true&useSSL=false with driver com.mysql.cj.jdbc.Driver.  Could not create connection to database server. Attempted reconnect 3 times. Giving up.
  - Caused by: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
  - Caused by: Connection refused

For more information, please use the --log-level flag

Connect with Liquibase (Docker) to a SSH tunneled database

1 Answers