I have been successfully running 20 arduinos sending data to my server for about a year now.
I updated some stuff and rebooted. Now mySQL database isn’t getting the data from my mosquitto client. Grafana still reads the mySQL database properly. If I manually add a data point it will show up.
The data does not show up in OpenHAB2
I know my mosquitto.conf file was damaged, so I fixed that.
# Place your local configuration in /etc/mosquitto/conf.d/
# this place is /etc/mosquitto/mosquitto.conf
# A full description of the configuration file is at
# /usr/share/doc/mosquitto/examples/mosquitto.conf.example
user openhab
port 1883
#pid_file /var/run/mosquitto.pid
persistence false
#persistence_location /var/lib/mosquitto/
#persistence_file mosquitto.db
log_dest file /var/log/mosquitto/mosquitto.log
#remote_username openhab
#remote_password asdf
protocol mqtt
allow_anonymous true
include_dir /etc/mosquitto/conf.d
#auth_opt_backends mysql
#auth_opt_port 3306
#listener 1883
#auth_opt_redis_host 127.0.0.1
#auth_opt_redis_port 6379
#connection_messages true
#log_dest topic <this don't work
log_type error
log_type warning
log_type notice
log_type information
log_timestamp true
I know if I “mosquitto_sub -d -t /sensor3/temperature/degree” on a command line, it will return the sensor data.
I am missing something between mosquitto and mySQL.
It is possible I had a routine running that didn’t restart after the reboot.
Platform information:
Hardware: Dell Poweredge
OS: Ubuntu Server 14.0
Java Runtime Environment: which java platform is used and what version
openHAB version: openhab2
Issue of the topic: above
Please post configurations
Items configuration related to the issue
Sitemap configuration related to the issue
Rules code related to the issue
Services configuration related to the issue
If logs where generated please post these here using code fences:
Either this is entirely outside the scope of openHAB, in which case we are unlikely to be able to help, or the thing missing between Mosquitto and MySQL is openHAB. Mosquitto does not directly store data into MySQL for you, at least not with the config you present above. I think it might be possible for Mosquitto to use MySQL as a persistence store but you will have to ask on the Mosquitto forum to figure that out.
Are you populating MySQL from openHAB using Persistence? If so then either your Persistence config is corrupted or, more likely given the very limited information you have provided, your openHAB MQTT config is not working. If you are using openHAB to persist this data into MySQL than you need to focus there.
What is your openHAB configs for MQTT? What version are you running? Errors in the logs? What changed? How did your mosquitto.conf get broken? Could that have broken openHAB’s config files too?
Thank you. I think that is my problem. I did an apt-get upgrade and my Mosquitto.conf file was almost empty. I think I was using the persistence configuration of mosquitto to tell openHAB my data. Give me a day or so to absorb this and I will reply.
Unlikely. A typical approach is to use the openHAB MQTT binding to subscribe to the MQTT topics where the devices publish their data. Then openHAB has the MySQL Persistence addon configured to store those values.
I’ve personally never seen a case where Mosquitto is configured to save messages directly into MySQL. But if it was, then this question is outside the scope of this forum and you will find better help on a Mosquitto forum.
I am at work now, but at lunchtime I will copy up my config files.
openhab2 default items
Group gAll
Number livingtemp "Living Room Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:sensor/1/temperature/degreeFahrenheit:state:default]"}
Number livinghumid "Humidity [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:sensor/1/humidity/percentRelative:state:default]"}
Number 82662bme280temp2 " Water entering house Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor2/esp82662/bme280/temp22:state:default]"}
Number 82662bmp280temp2 " Water heater outlet pipe Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor2/esp82662/bmp280/temp2:state:default]"}
Number 82662dht22temp2 " room temp abv water heater [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor2/esp82662/dht22/temp32:state:default]"}
Number 82662mpx5010press2 " Plentum pressure [%.1f ]" <pressure> (gAll) {mqtt="<[mosquitto:/sensor2/esp82662/mpx5010/press2:state:default]"}
Number 82663bme280temp " esp8266.3 Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor3/temperature/degree:state:default]"}
Number 82663bme280humid " esp8266.3 Humid [%.2f rh]" <humidity> (gAll) {mqtt="<[mosquitto:/sensor3/humidity/percent:state:default]"}
Number 82663bme280pressure " esp8266.3 Pressure [%.2f in/hg]" <pressure> (gAll) {mqtt="<[mosquitto:/sensor3/barometer/pressure:state:default]"}
Number 82663bme280volts " esp8266.3 Volts [%.2f dc]" <volts> (gAll) {mqtt="<[mosquitto:/sensor3/battery/volts:state:default]"}
Number 82665bme280temp " esp8266.5 Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor5/temperature/degree:state:default]"}
Number 82665bme280humid " esp8266.5 Humid [%.2f rh]" <humidity> (gAll) {mqtt="<[mosquitto:/sensor5/humidity/percent:state:default]"}
Number 82665bme280pressure " esp8266.5 Pressure [%.2f cm/h2o]" <pressure> (gAll) {mqtt="<[mosquitto:/sensor5/barometer/pressure:state:default]"}
Number 82665bme280volts " esp8266.5 Volts [%.2f dc]" <volts> (gAll) {mqtt="<[mosquitto:/sensor5/battery/volts:state:default]"}
Number 82668dht22temp " esp8266.8 Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor8/temperature/degree:state:default]"}
Number 82668dht22humid " esp8266.8 Humid [%.2f rh]" <humidity> (gAll) {mqtt="<[mosquitto:/sensor8/humidity/percent:state:default]"}
Number 82668battery " esp8266.8 Volts [%.2f ]" <battery> (gAll) {mqtt="<[mosquitto:/sensor8/voltage:state:default]"}
Number 82669temp " esp8266.9 Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor9/temperature/degree:state:default]"}
Number 82669a0 " esp8266.9 Pressure [%.2f ]" <pressure> (gAll) {mqtt="<[mosquitto:/sensor9/a0:state:default]"}
Number 826611dht22temp " esp8266.11 Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor11/temperature/degree:state:default]"}
Number 826611battery " esp8266.11 Volts [%.2f ]" <battery> (gAll) {mqtt="<[mosquitto:/sensor11/voltage:state:default]"}
Number 826611water " esp8266.11 Water [%.2f ]" <humidity> (gAll) {mqtt="<[mosquitto:/sensor11/A0:state:default]"}
Number 826612dht22temp " esp8266.12 Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor12/temperature/degree:state:default]"}
Number 826612dht22humid " esp8266.12 Humid [%.2f rh]" <humidity> (gAll) {mqtt="<[mosquitto:/sensor12/humidity/percent:state:default]"}
Number 826612battery " esp8266.12 Volts [%.2f ]" <battery> (gAll) {mqtt="<[mosquitto:/sensor12/voltage:state:default]"}
Number 826614temp " esp8266.14 Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor14/temperature/degree:state:default]"}
Number 826614a0 " esp8266.14 Water Sensor [%.2f ]" <pressure> (gAll) {mqtt="<[mosquitto:/sensor14/a0:state:default]"}
Number 826615temp " esp8266.15 Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor15/temperature/degree:state:default]"}
Number 826615d3 " esp8266.15 Volts [%.2f ]" <volts> (gAll) {mqtt="<[mosquitto:/sensor15/voltage:state:default]"}
Number 826616a0 " esp8266.16 light Sensor [%.2f ]" <pressure> (gAll) {mqtt="<[mosquitto:/sensor16/a0:state:default]"}
Number 826617temp " esp8266.19 temperature [%.2f ]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor17/temperature/degree:state:default]"}
Number 826617pres " esp8266.19 pressure [%.2f ]" <pressure> (gAll) {mqtt="<[mosquitto:/sensor17/pressure/inHg:state:default]"}
Number 82661420 " esp8266.20 Water Sensor [%.2f ]" <pressure> (gAll) {mqtt="<[mosquitto:/sensor20/a0:state:default]"}
Number 826622temp " esp8266.22 Temp [%.2f F]" <temperature> (gAll) {mqtt="<[mosquitto:/sensor22/temperature/degree:state:default]"}
Number 826622A0 " esp8266.22 Volts [%.2f ]" <battery> (gAll) {mqtt="<[mosquitto:/sensor22/a0:state:default]"}
mysql.persist
Strategies {
everyHour : "0 0 * * * ?"
everyDay : "0 0 0 * * ?"
everyMinute : "0 * * * * ?"
every5Min : "0 */5 * * * ?"
every5Sec : "*/5 * * * * ?"
// if no strategy is specified for an intem entry below, the default list is used
default = everyMinute
}
Items {
// let's store EVERYTHING - we may need it later
* : strategy = everyMinute
}
services/mqtt.cfg
#
# Define your MQTT broker connections here for use in the MQTT Binding or MQTT
# Persistence bundles. Replace <broker> with an ID you choose.
#
# URL to the MQTT broker, e.g. tcp://localhost:1883 or ssl://localhost:8883
mosquitto.url=tcp://localhost:1883
# Optional. Client id (max 23 chars) to use when connecting to the broker.
# If not provided a random default is generated.
#<broker>.clientId=<clientId>
# Optional. True or false. If set to true, allows the use of clientId values
# up to 65535 characters long. Defaults to false.
# NOTE: clientId values longer than 23 characters may not be supported by all
# MQTT servers. Check the server documentation.
#<broker>.allowLongerClientIds=false
# Optional. User id to authenticate with the broker.
mosquitto.user=openhab
# Optional. Password to authenticate with the broker.
mosquitto.pwd=asdf
allow_anonymous=true
# Optional. Set the quality of service level for sending messages to this broker.
# Possible values are 0 (Deliver at most once),1 (Deliver at least once) or 2
# (Deliver exactly once). Defaults to 0.
mosquitto.qos=1
# Optional. True or false. Defines if the broker should retain the messages sent to
# it. Defaults to false.
mosquitto.retain=true
# Optional. True or false. Defines if messages are published asynchronously or
# synchronously. Defaults to true.
mosquitto.async=false
# Optional. Defines the last will and testament that is sent when this client goes offline
# Format: topic:message:qos:retained <br/>
#<broker>.lwt=<last will definition>
services/mysql.cfg
# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://127.0.0.1/openhab
# the database user
user=openhab
# the database password
password=asdf
# the reconnection counter
#reconnectCnt=
# the connection timeout (in seconds)
#waitTimeout=
# 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
openhab2 default.sitemap
# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://127.0.0.1/openhab
# the database user
user=openhab
# the database password
password=asdf
# the reconnection counter
#reconnectCnt=
# the connection timeout (in seconds)
#waitTimeout=
# 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
