MySQL and Influx cant connect when running in docker

Add-ons Persistence Services
1

I am trying to run Openhab in docker on Unraid, using the template pointing to this https://hub.docker.com/r/openhab/openhab/

I got MQTT working, and can successfully connect to the broker on another machine, but both Influxdb and MySQL cant get a connection.

Influxdb

2019-05-26 01:21:23.314 [ERROR] [.internal.InfluxDBPersistenceService] - database connection failed
retrofit.RetrofitError: No route to host (Host unreachable)
--- [ Cut all the other errors away ] ---
2019-05-26 01:21:23.329 [ERROR] [.internal.InfluxDBPersistenceService] - database connection error No route to host (Host unreachable)
2019-05-26 01:21:23.329 [ERROR] [.internal.InfluxDBPersistenceService] - database connection does not work for now, will retry to use the database.

Influxdb config

# The database URL, e.g. http://127.0.0.1:8086 or https://127.0.0.1:8084 .
# Defaults to: http://127.0.0.1:8086
# url=http(s)://<host>:<port>
url=http://192.168.1.100:8086

# The name of the database user, e.g. openhab.
# Defaults to: openhab
# user=<user>
user=openhab

# The password of the database user.
# password=
password=password

# The name of the database, e.g. openhab.
# Defaults to: openhab
# db=<database>
db=openhab

MySQL (using MariaDB)

2019-05-26 01:21:26.387 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://192.168.1.100:3306/openhab_selina?serverTimezone=Europe/Copenhagen, user=openhab2
com.mysql.cj.jdbc.exceptions.CommunicationsException: 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.

MySQL config

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://192.168.1.100:3306/openhab_selina?serverTimezone=Europe/Copenhagen
mysql:sqltype.string=VARCHAR(20000)

# the database user
user=openhab2

# the database password
password=password

# the reconnection counter
reconnectCnt=99

# the connection timeout (in seconds)
waitTimeout=14400

# Use MySQL Server time to store item values (=false) or use openHAB Server time (=true).
# For new installations, its recommend to set "localtime=true".
# (optional, defaults to false)
#localtime=true

I have tested both Influx and MySQL, and they are running, and receiving data from another Openhab installation, running in a VM.

2

Hey there, last week I also did this task and created a GitHub Repo for easy use.

I noticed some problems, you need to run the docker containers in network_mode: host otherwise openHAB won’t be able to detect devices in your network for example Phillips Hue etc.

1 Like
3

Isn’t the problem in that other thread something completely different? The reason for the exec binding not working, is pretty obvious, because it is running inside docker.

In my case openhab cant get any connection, with the influxdb and mysql binding. The MQTT binding does work, and can connect to the rest of the network.

4

The link was just to show the discussion why I did what I did.

To your problem.
Do you use native docker or docker compose?
In both cases please share your docker startup or docker-compose file.

5

I use docker in Unraid, and in that there is “Community Applications”, in there there is an openhab docker, which installs with just one click.

The one it installs is then this one https://hub.docker.com/r/openhab/openhab/

6

That information is vague.
Which docker image is used? tags

Is your Influxdb / MySQL (mariadb) a local setup or also a docker setup?

7

It seems to be using openhab/openhab:2.2.0-amd64-debian

8

version 2.2.0

Considering the stable version is 2.4.0 and there is 2.5.0 already, this 1 click easy setup is kinda bad.
I never used Unraid, but I have googled a bit and it seems most Unraid users prefer a GUI over shell.

Which docker version does your Unraid work with?

9

It is using Docker version 18.09.5, build e8ff056dbc

It is also possible to specify other templates, for installing some which isnt in the CA addon.

This is the template it use for the one I got in right now

<?xml version="1.0"?>
<Container version="2">
  <Name>openHAB</Name>
  <Repository>openhab/openhab:2.4.0-amd64-debian</Repository>
  <Registry>https://hub.docker.com/r/openhab/openhab/</Registry>
  <Network>br0</Network>
  <MyIP>192.168.1.35</MyIP>
  <Shell>sh</Shell>
  <Privileged>false</Privileged>
  <Support>https://forums.unraid.net/topic/78482-support-knex666-openhab/</Support>
  <Project>https://github.com/maschhoff/docker/tree/master/openhab</Project>
  <Overview>openHAB - a vendor and technology agnostic open source automation software for your home.&#xD;
&#xD;
For sample rules etc. have a look at https://github.com/maschhoff/OpenHAB</Overview>
  <Category>HomeAutomation:</Category>
  <WebUI>http://[IP]:[PORT:8080]/</WebUI>
  <TemplateURL/>
  <Icon>https://raw.githubusercontent.com/tinglis1/docker-containers/master/openhab/openhab-logo-square.png</Icon>
  <ExtraParams>--tty --shm-size 2g</ExtraParams>
  <PostArgs/>
  <CPUset/>
  <DateInstalled>1558954404</DateInstalled>
  <DonateText/>
  <DonateLink/>
  <Description>openHAB - a vendor and technology agnostic open source automation software for your home.&#xD;
&#xD;
For sample rules etc. have a look at https://github.com/maschhoff/OpenHAB</Description>
  <Networking>
    <Mode>br0</Mode>
    <Publish/>
  </Networking>
  <Data>
    <Volume>
      <HostDir>/etc/localtime</HostDir>
      <ContainerDir>/etc/localtime</ContainerDir>
      <Mode>ro</Mode>
    </Volume>
    <Volume>
      <HostDir>/mnt/user/appdata/openhab2/conf/</HostDir>
      <ContainerDir>/openhab/conf</ContainerDir>
      <Mode>rw</Mode>
    </Volume>
    <Volume>
      <HostDir>/mnt/user/appdata/openhab2/userdata/</HostDir>
      <ContainerDir>/openhab/userdata</ContainerDir>
      <Mode>rw</Mode>
    </Volume>
  </Data>
  <Environment/>
  <Labels/>
  <Config Name="Host Path 1" Target="/etc/localtime" Default="/etc/localtime" Mode="ro" Description="Container Path: /etc/localtime" Type="Path" Display="always" Required="true" Mask="false">/etc/localtime</Config>
  <Config Name="Host Path 2" Target="/openhab/conf" Default="/mnt/user/appdata/openhab/conf/" Mode="rw" Description="Container Path: /openhab/conf" Type="Path" Display="always" Required="true" Mask="false">/mnt/user/appdata/openhab2/conf/</Config>
  <Config Name="Host Path 3" Target="/openhab/userdata" Default="/mnt/user/appdata/openhab/userdata/" Mode="rw" Description="Container Path: /openhab/userdata" Type="Path" Display="always" Required="true" Mask="false">/mnt/user/appdata/openhab2/userdata/</Config>
</Container>
10

Okay.
Is your InfluxDB / MySQL Setup also Docker or Local setup?

11

The influxdb and mysql are also dockers. I can reach them from anywhere else on the network, and my openhab installation in a VM is also putting data into them.

12

I guess this is you old setup, since you try it now with docker.

No route to host

is stated in your openHAB error log.

<Networking>
    <Mode>br0</Mode>

This is in the template. But I already stated

Try to edit the template and change br0 to host

13

Yes, the VM is the old setup, but I like docker, because I dont have do dedicated resources to it, it can just take them as needed.

I already tried changing to host when I saw your first post, sadly that didnt fix the problem either.

14

Did you try to from inside the openHAB docker if you can even reach it?

docker exec it DOCKERID /bin/bash
15

I am not sure what you mean.

16

Udklip
Udklip.PNG915×73 5.63 KB

Should be able to connect to influx from inside the docker

Now I am not getting any errors
 But it still isnt working either

2019-05-27 07:00:39.778 [INFO ] [.core.internal.i18n.I18nProviderImpl] - Time zone set to 'Europe/Copenhagen'.
2019-05-27 07:00:39.786 [INFO ] [.core.internal.i18n.I18nProviderImpl] - Location set to '0,0'.
2019-05-27 07:00:39.788 [INFO ] [.core.internal.i18n.I18nProviderImpl] - Locale set to 'da_DK'.
2019-05-27 07:00:39.953 [INFO ] [ebuilder.internal.HomeBuilderServlet] - Started Home Builder at /homebuilder
2019-05-27 07:00:39.966 [INFO ] [panel.internal.HABPanelDashboardTile] - Started HABPanel at /habpanel
2019-05-27 07:00:41.596 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'aircondition.items'
2019-05-27 07:00:41.638 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'workroom.items'
2019-05-27 07:00:41.651 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'common.items'
2019-05-27 07:00:41.826 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'mysql.persist'
2019-05-27 07:00:41.839 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'influxdb.persist'
2019-05-27 07:00:42.628 [INFO ] [thome.model.lsp.internal.ModelServer] - Started Language Server Protocol (LSP) service on port 5007
2019-05-27 07:00:42.789 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'nygade.sitemap'
2019-05-27 07:00:42.889 [INFO ] [el.core.internal.ModelRepositoryImpl] - Validation issues found in configuration model 'mqtt.things', using it anyway:
Provide a thing type ID and a thing ID in this format:
 <thingTypeId> <thingId>
Provide a thing type ID and a thing ID in this format:
 <thingTypeId> <thingId>
2019-05-27 07:00:42.891 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'mqtt.things'
2019-05-27 07:00:43.167 [INFO ] [.dashboard.internal.DashboardService] - Started Dashboard at http://192.168.1.100:8080
2019-05-27 07:00:43.169 [INFO ] [.dashboard.internal.DashboardService] - Started Dashboard at https://192.168.1.100:8443
2019-05-27 07:00:43.262 [INFO ] [arthome.ui.paper.internal.PaperUIApp] - Started Paper UI at /paperui
2019-05-27 07:00:43.411 [INFO ] [.transport.mqtt.MqttBrokerConnection] - Starting MQTT broker connection to '192.168.1.30' with clientid paho172348801358278 and file store '/openhab/userdata/mqtt/192.168.1.30'
17

Why did you create textual MQTT Things and not via PaperUI? Also you messed something up there.

Well let’s take a look at your influxdb.persist file :wink:

This is my crude, I don’t care, log everything file.

// Persistence Strategien haben einen Namen und eine Definition
Strategies {
    everyHour : "0 0 * * * ?"
    everyDay  : "0 0 0 * * ?"

    // Wenn bei einem unten definierten Item keine Strategie angebeben ist,
    // wird die Default-Liste angewendet
    default = everyChange
}

/*
 * Each line in this section defines for which item(s) which strategy(ies)
 * should be applied. You can list single items, use "*" for all items or
 * "groupitem*" for all members of a group item (excl. the group item itself).
 */
Items {
    // Speichere die Daten aller Items jeden Tag (veryDay), bei jeder Änderung (everyChange) und
    // ordne den Items bei einem Neustartand den letzten Wert zu (restoreOnStartup)
    * : strategy = everyChange, everyDay
}
18

Because I like having them in a text file.

As far as I can see, there is a bug where it validate it wrong, while it is fine.

influx.persist

Strategies {
  everyMinute : "0 * * * * ?"
  default = everyChange
}

Items {
  sensor* : strategy = everyMinute, restoreOnStartup
  startRestore* : strategy = everyChange, restoreOnStartup
}

aircondition.items

Number ac_water_level "Condensation water [%.0f %%]" <sewerage> (sensors) {channel="mqtt:topic:aircondition:water_level"}

mqtt.things

Bridge mqtt:broker:selina_mqtt [ host="192.168.1.30", secure=false ]
{
	Thing mqtt:topic:aircondition {
	Channels:
		Type number : water_level 	"AC water level" 	[ stateTopic="/AC/water_level" ]
	}
}
19

persist states

item def. states group name

I don’t know, is it like RegEx sonsor*? I would try sensors*

PS:
you can tail the influxdb logs to see if something (openHAB) accesses and writes values.

docker logs -f DOCKERID
20

On my old install I just use sensor* and then call sensorStuff it then include all the items which start with sensor