I’m using MariaDB as persistence. Also installed InfluxDB and used it already with Grafana, but all my data is in MariaDB. I added MariaDB as data source, but I’m unable to display graphs. I don’t understand the query that I need to provide.
Has somebody experience with Grafana and MariaDB (or MySQL)? Or is it easy to import all data to InfluxDB ?
1 Like
Hi,
I’m using Grafana and MariaDB. They work very well together!
(long story can be found here)
In Grafana, I just use an SQL query to define which data I would like to select. Example:
I’m not sure how familiar you are with MariaDB/SQL statements. But this gives you a lot of flexibility on which data is shown in Grafana.
Obviously, you will need to know the structure of your table(s) (phpMyAdmin can help).
Let me know if you need more input.
Thanks for the answer. I looks so simple… Will try it again tomorrow. Simple SQL queries shouldn’t be any problem for me. I’ll let you know if I need more help.
Display data in a table is working fine, just like you said, just use SQL statement.
eg
SELECT * FROM xiaomi_th_alex_temperature_0026
But I would like to have this data in a graph.
Tried this:
SELECT
UNIX_TIMESTAMP(time) as time_sec,
value as value
FROM xiaomi_th_alex_temperature_0026
WHERE $__timeFilter(time)
ORDER BY time ASC
But I receive this error message: Found row with no time value
Tried to so something like this DATE_FORMAT(time, "%Y-%m-%d %H:%i:%s"), but gives the same message
yes…
These tables are set up by OpenHAB itself.
Sorry, then I don’t know anymore.
I recommend you bring your question to the Grafana Community. I guess that community has a lot more expertise on Grafana then here.
I’ve you have found your answer, please share it here. I think others might be interested as well!
I found something myself, my previous attemp was almost correct:
SELECT
UNIX_TIMESTAMP(DATE_FORMAT(time,“%Y-%m-%d %H:%i:%s”)) as time_sec,
value
FROM
xiaomi_th_alex_temperature_0026
I’m only missing how I can give the series a name, now all of them are called ‘unknown’:
3 Likes
That’s the label for the graph/table it selves, not for the series.
With InfluxDB as a datasource you have this option:
I only use the Table Panel for MariaDB data, so I can’t say if it has that option as well.
What happens if you define it in your SQL statement? Something like:
SELECT
UNIX_TIMESTAMP(DATE_FORMAT(time,"%Y-%m-%d %H:%i:%s")) as time_sec,
value as myvaluename
FROM
xiaomi_th_alex_temperature_0026
I had the same problem.
The first google search result pointed out the solution:
The workaround is to use the FLOOR function, e.g.
FLOOR(UNIX_TIMESTAMP(time)) as time_sec
Thx, this is working fine!
Do you also have a solution to display correct series labels?
Sure thing:
SELECT
FLOOR(UNIX_TIMESTAMP(time)) as time_sec,
value,
'Your custom metric name' as metric
FROM your_sql_column_name
WHERE $__timeFilter(time)
ORDER BY time ASC;
2 Likes
thanks!
Now I can do everything with my Mariadb, no need for influxdb 
Guys
thanks a lot for sharing your solutions - I was already giving up with Grafana but after finding this thread its working - finally
Can anyone of you tell me how to adjust this script when I want to display more than one line in a chart?
Thanks & regards
John
I simply added a second query, or a third, fourth…
Sorry to re-live this topic.
I’ve spent a little time researching this to no avail…
Using openhab2 on Raspberry pi 3, (openhabian), JDBC Persistance MYSQL, MariaDB, Grafana Graphing… I don’t understand where I can change the following from “value” to the desired name (ie GarageTemp, Greenhouse Temp, etc)
Hopefully this is a quick reply without consuming too many cycles from anyone’s busy day 
AND… I really wish I understood how openhab creates the tablenames in its DB… 
Michael Krug shows the syntax for this but I’m not sure if this goes in Grafana or PHPMyAdmin that I’m using 
AND how to use the proper syntax for my specific application
Thanks Guys!!