Execute command line script (with SQL statement inside) from OH3 docker

Setup, Configuration and Use Migration
, ,
1

Hello,

am in the middle of migration to OH3. After searching 4 days in the forum, I got a lot closer - but do not get this to work. To pull data from my remote SQL data base I have a script that provides items and things from my SBFspot database. Later I also want to use scripts to write specific data into mySQL.

Environment:

  • Ubuntu server 21.04
  • Openhab 3.1.0 as docker (standard configuration)
  • On my server (not as docker): mySQL Server 8.0.25

I was able to call the script as root within Linux with result from my remote DB. So mySQL on the new machine also works.

I can call the script from a rule (conform to latest documentation):

var String test =  executeCommandLine(Duration.ofSeconds(10),"/openhab/conf/scripts/sma.script", "ETotal")

For testing my bash script (chown, whitelist, chgrp) I have included a simple textfile creation into the rule - to be shure it is executed :slight_smile: :

#!/bin/bash
user=openhab
password=mypassword
host=192.168.178.199
sql_name=$1

echo "Sql Name " $sql_name >> /openhab/conf/scripts/temp_output.txt

SMA_Val=$(mysql --defaults-extra-file=/openhab/conf/scripts/config.cnf  -N -e "select $sql_name from SBFspot.Inverters")

case "$sql_name" in
    EToday)     val=`echo "scale=2 ; $SMA_Val / 1000" | bc`;;
    ETotal)     val=`echo "scale=2 ; $SMA_Val / 1000" | bc`;;
    *)          val=$SMA_Val;;
esac

echo $val

And the text file gets created and filled

SQL Name  ETotal

As openhab executes inside the docker, my (fist) biggest issue was to find out - that the docker has different paths:

Host volume 	    Path in container
/opt/openhab/conf 	/openhab/conf

This is resolved. BUT in the log files show the following and I get no result from the sql statement:

2021-07-24 22:37:24.251 [INFO ] [.openhab.core.model.script.Test Exec] - /openhab/conf/scripts/sma.script: line XX: mysql: command not found
/openhab/conf/scripts/sma.script: line XY: bc: command not found

I guess the docker on sh command line cannot execute / interpret the mysql and bc!
My docker PATHs /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

Is my assumption right, the sql engine is not found by the openhab docker?
Any suggestion how to fix this?
Or do I need to do a fresh install of OH3 without docker?

Thanks for any hint or advise.

Bernd

2

Yes, it looks like both commands are not installed in docker.
One possibility could be: How to run shell script on host from docker container? - Stack Overflow Although this is against the idea of having separation between guest and host.
Or create a new docker container that also includes bc and mysql client.

3

Thank you Wolfgang!

As I really want a clear and consistent setup… the solution to run scripts on host does not look preferred. This is mistreating the docker concept.

On the other hand: My docker skills to create my own Openhab3, mySQL and BC container are not sufficient… this I need to skip.

Unfortunately I have already 90% setup my OH3 in the docker… As there is no openhab-cli to backup for the docker. For those who also get stuck… This is how I managed.

  • Stop and remove OH3 docker
  • Install OH3 with apt-get as in documentation (install java 11 fist!)
  • Copy the folder content like cp -arv /opt/openhab/userdata/* /var/lib/openhab/ for:
  1. /opt/openhab/conf form Docker goes into /etc/openhab then all my textual defined items, things, rules, scripts, etc. are moved
  2. /opt/openhab/addons from Docker goes into /usr/share/openhab/addons then all additional addons are moved
  3. /opt/openhab/userdata from Docker goes into /var/lib/openhab then all Paper UI configuration is moved

Start your service systemctl start openhab.service and off you go!

1 Like
4

Look at Executing shell scripts before openHAB is started in the Dockerhub readme.

Follow that to have the container execute a script that installs the MySQL client before it starts openHAB. You don’t have to create a custom Docker Image.

docker exec -it openhab runtime/bin/backup will do the equivalent to openhab-cli backup. However when one is running in a container, one usually mounts the config as volumes into the container and those volumes are backed up separately and independently from the container.