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 <command-name> --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 1 · 2022-09-13T21:25:18.540

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 <command-name> --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

Fabian, please, could you test the suggested change in the command? Did it work? — jccampanero, Sep 15 '22 at 21:39
Sorry to be so slow, was pretty busy, I will try it on the weekend. — J Fabian Meier, Sep 16 '22 at 15:33
Please Fabian, there is no need to say sorry. On the contrary, please, test the change when you have time. — jccampanero, Sep 16 '22 at 21:25
Thanks for you answer. I tried what you suggested and ended up with the screen you gave at the very end of the answer, i.e. the Communications Link failure. Now I need to figure out why it is not using the tunnel properly. — J Fabian Meier, Sep 17 '22 at 17:40
Thank you very much for the feedback @JFabianMeier. I am happy to see that the command worked, although I am sorry because you aren't able to reach the mysql database yet. The problem could be motivated because in the url we are trying using `localhost`. If you are running [mac or windows](https://docs.docker.com/desktop/networking/#i-want-to-connect-from-a-container-to-a-service-on-the-host) you could try using `host.docker.internal` instead of `locahost`, something like: `jdbc:mysql://host.docker.internal:3306/usr_web1444_4?autoReconnect=true&useSSL=false` when running your container. — jccampanero, Sep 17 '22 at 18:57
If you are running linux you could try tweaking the SSH connection. Please, see this [related SO question](https://stackoverflow.com/questions/39143689/access-hosts-ssh-tunnel-from-docker-container). I never tested using `host.docker.internal` with a SSH tunnel but I think it may work. I hope it helps. — jccampanero, Sep 17 '22 at 18:59

Connect with Liquibase (Docker) to a SSH tunneled database

1 Answers1