HABPanel - Chart shows no data - using persistance - jdbc - mariadb

Hi i want to include some charts in my HABPanel, but i didnt get it to work.
Set already up a maria db server on the pi und created a persistence file with the config to the server, no errors in the openhab log so for but no data shows up in the Chart.

I used the items from the yahoo whether binding and the power usage items from my TPLINK HS110 Power plugs, the items its self have the information in it.

server


If I remember correctly from the other thread, the values get everyMinute into your mariaDB tables?
If that’s the case, you should have no problems retrieving them from:

  • Items (all your items)
  • Item1 - ItemXXX (one table per item with Time and Value)

please put in a screenshot from HABPanel, where you configured your chart.

Strange thing, mariadb is availiable no connection errors but on loading the chart.

20:32:49.281 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘MultimediaWohnzimmer_EnergyUsage’.
20:32:49.284 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Trockner_EnergyUsage’.
20:32:49.294 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘PC_Power’.
20:32:49.340 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Kuechengeraete_EnergyUsage’.

in mariadb there were no item tables created, its still empty. (I switched from nas to the pi for the mariadb.)

User rights:
SHOW GRANTS FOR ‘openhab’@‘localhost’;
Grants for openhab@localhost |
±------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘openhab’@‘localhost’

mariadb status
MariaDB [(none)]> status

mysql Ver 15.1 Distrib 10.1.23-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

Connection id: 23
Current database:
Current user: openhab@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ‘’
Using delimiter: ;
Server: MariaDB
Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 4 hours 40 min 11 sec

Threads: 2 Questions: 51 Slow queries: 0 Opens: 27 Flush tables: 1 Open tables: 4 Queries per second avg: 0.003

MariaDB [openhab]> show tables;
Empty set (0.00 sec)



That bothers me… Don’t you have some tables in there?
HABPanel uses the same configuration as openHAB uses. If there are items in there, you can use them!

I think i found the problem,some rights, user has global rights but at GRANT there was NO, but at the moment if have some issues with the root user with pw cant login and need to reset the password and for a better overview i will install phpmyadmin.

So took 2 days to reinstall that messy mariadb , no i can work with phpmyadmin, but still dont work, no creation of tables possible, no idea whats missing.

phpmyadmin_03

Fixed it, changed the config files to mysql and now its running.

So ive got a last big Question :slight_smile:

How i get Numbers which i get via shell script in a Item to use it in a persistance.?

At first, glad it worked out finally!

What do you mean by “use it in a persistence”? You don’t want to write directly to the database, that’s openhab’s job. You just update the values of the items and if these items are configured to use persistence, they’re persisted.

Getting values inside openHAB:

  • Use bindings
  • If no bindings available, using REST or MQTT are the best options (we talked about it, didn’t we?)

yes we did , i make a example.

the script - homematicip-rest-api
I got a python script for homematic (reads my homematic ip sensors out and write them in a txt file)
python3 /home/pi/homematicip-rest-api/homematicip_cli.py --list-devices > $hip_log

example from the device which where in the txt file
3014F711A0000E57098F22CB HmIP-STH Temperatur- und Luftfeuchtigkeitssensor lowbat(False) unreach(False) rssiDeviceValue(-75) rssiPeerValue(-72): actualTemperature(19.1) humidity(47)

now iam picking the temperature and write the value for pimatic in a variable
aztemp=$(cat $hip_log | grep ‘3014F711A0000E57098F22CB’ | grep -o -P ‘(?<=actualTemperature().*(?=) )’)

Result
aztemp=19.1

And now i have to build it for Openhab2 :slight_smile:

so, you’re almost done! :wink:

Using REST:

os.system('curl --header "Content-Type: text/plain" --request PUT --data "' + aztemp + '" http://<IPofYourOpenHAB>:8080/rest/items/<ItemName>/state')

If you have MQTT up and running:

os.system("mosquitto_pub -h <IPofYourBroker> -u '<User>' -P '<Password>' -t '<openHAB-TOPIC>' -m '" + aztemp + "'")

Where I put that REST statement ? In my shell script ? Rest API is already working in Openhab2? Didnt find a binding for it.

I thought you were talking about python?
the code snippets I wrote is python (you’ll need import os for that - just put it in the first lines).
and yes - REST API is running without a binding, it’s live, if openHAB is live! :wink:

if using shellscript alone, just remove the “os.system("")” part.

the script is a shell, perhaps the syntax is not correct ? Nothing to see on the openhab site, nothing in the log

curl --header “Content-Type: text/plain” --request PUT --data “’ + $aztemp + '” http://localhost:8080/rest/items/hmip_aztemp>/state

I figured, you know some things or two on how to use bash, but… :wink:

please use

curl --header "Content-Type: text/plain" --request PUT --data "${aztemp}"  http://localhost:8080/rest/items/hmip_aztemp/state
  • please use " instead of (this one could be depending on your browser)
  • insert variables in bash like: $variable or even better ${variable}
  • please remove the > from your URL

if there’s not entry in neither events.log or openhab.log, please :slight_smile:

  1. start the script outside openHAB (in this case, just from the shell)
  2. have a look in /var/log/messages, /var/log/system or whatever grows on the command in /var/log/ (depending on system configuration), there go all system logs

I tried a little bit , but iam confused bei the things / items construct and dont know to build it correct in the config files.

shell script said
{“error”:{“message”:“Item hmip_badluft does not exist!”,“http-code”:404}}
openhab logs says

So that means atleast i need to create that items and perhapsthings in the config file - this i got in

openhab log output
Received HTTP PUT request at ‘items/hmip_badluft/state’ for the unknown item ‘hmip_badluft’.

Entry of my items and things

.items file
Group gHMIPHumidity
Number hmip_badluft “Badezimmer Luftfeuchtigkeit [%d %%]” (gHMIPHumidity) { channel="h
omematicip:http*:hmip_humidity:humidity"}

Didnt get that chanel constuct, in the docu i read about binding, but i dont have one which i can refer to , so i wrote homematic:http … which is not right i guess :slight_smile:

*.things looks like

Thing homematic_ip:http:hmip_humidity “Luftdruck” @ “Home” [ ] < no idea what i put for the Value

Looks like iam on the finish line , so where i put the variable hmip_badluft which i filled up :slight_smile:

Thanks for the help so far.

Please go back to the documentation and learn about the underlying concept of things, items and links and how bindings work for this.
https://docs.openhab.org/concepts/index.html

Then go back to your installation and keep in mind:

  • You can’t change or update channels in API, MQTT or rule context
  • You can update items
  • Updating items manually (REST in your case), which are already linked to a channel is contraproductive as they get regular updates from the binding also
  • You don’t need a binding for a item’s configuration

If a binding won’t work (I learned, you don’t want or can use a binding?) you define an item without the binding part. Then update it via REST for example.

Some shorts Questions in addition:

For persistences, whats the value for 5 Minutes ? 5 instead 0 ?
Strategies {
everyMinute : “0 * * * * ?”
everyHour : “0 0 * * * ?”
everyDay : “0 0 0 * * ?”
default = everyChange
}

My created groups dont show up at the PaperUI what could the reason for it?
Normally it is called Sensoren, for yahoo weather it worked and shows register wheater




If you see something strange configured please tell me :slight_smile:

All of your Homematic related items refer to the exact same channel. As I don’t know anything about Homematic IP I can’t help here, but if the channel is configured correctly, all items will have the exact same value everytime. I also don’t see your bash script come into place here? :confused:

Regarding persistence, I’d expect temperature to be persisted onChange instead of every 5 min, but that’s personal flavour I guess. Please read the part on cron based triggers again in the documentation. It’s */1 for every sec/min/hour/… */5 is then interval of 5

So i hope i have it right, only have a problem with the name, is it necessary to use : and 3 name parts ?

i created the things name (homematicip:sensors:temps) by my self, because i have no binding
homematicip.things
Thing homematicip:sensors:temps “Temperaturen” @ “Sensoren” [location=0]

homematicip.items
Number hmip_aztemp “Arbeitszimmer Temperatur [%.1f °C]” (gHMIPTemps) { channel=“homematicip:sensors:temps:aztemp”}

Number hmip_badtemp “Badezimmer Temperatur [%.1f °C]” (gHMIPTemps) { channel=“homematicip:sensors:temps:badtemp”}

Number hmip_wztemp “Wohnzimmer Temperatur [%.1f °C]” (gHMIPTemps) { channel=“homematicip:sensors:temps:wztemp”}

Number hmip_kindtemp “Kinderzimmer Temperatur [%.1f °C]” (gHMIPTemps) { channel=“homematicip:sensors:temps:kindtemp”}

Its working in HAB Panel, but in the PaperUI i didnt Show up, did self created items didnt shop up and i have to make a sitemap oder use HABPanel or what could be the reason for it ?

got two Strange behavior , my crontab didnt work anymore, no idea why and i installed the openhab2.service which also didnt work.

I read that if you install openhab2 manually, it didnt work because of the wrong paths, changed them but also didnt worked.

Can somebody give advice ?

my crontab looks like (it worked before i added the last to lines.)

the openhab2.service looks like
I used the tutorial from the openhab site (https://docs.openhab.org/installation/linux.html#service)