Hi all guys.
Do you know how I can easily map the Item Name (eg. Temperature_C) to the Item number (eg. Item2) into which MariaDB stores the Item values in order to use it in the SQL queries in grafana? Can you give me some examples? Sorry for the question, but i’m a noob in SQL queries. Thankyou in advance.
MariaDB will have a main table called items
where the item name and the table for that item are stored
Yes I know, but I’m only able to do a SELCT * and manually translate ItemName to ItemId. Is ther an automatic way to do this? Tks.
No. Of course you can use jdbc to use MariaDB as persistence service, and there is a parameter
tableUseRealItemNames
in jdbc.cfg
to get the item names as table names. You have to build a new database (but you can import old data manually to the correct table)
I think I will migrate from mysql to influxdb as it seems working better with grafana. Is there a guide to follow for migrating from mysql to influxdb ? I did a little bit search and found this InfluxDB+Grafana persistence and graphing I’ll give it a try.
Well, you have to
- setup influxDB,
- install the influxDB addon
- copy
mysql.persist
toinfluxdb.persist
(or at least the items which shall be persisted), - configure influxdb Persistence service via
influxdb.cfg
, - and of course configure grafana to use influxDB as an additional data source.
- Finally, you have to adjust the graphs to use the new data source.
I doubt that it’s easily possible to export MySQL data to influxDB, so you will start with empty graphs
Tks Udo, that’s what I did and it works very well. Just wanted to find a way to import my old data… maybe using this script https://github.com/CWempe/rest2influxdb . It seems to pull data using REST APIs and inject them into influxDB tables. I’m I right? Is there someone that has tried it? Another question: Is there a way to purge my old mysql databases?
I don’t know if the script still works with newer versions of openhab.
But I think so.
When you want to inject the old data to MariaDB, you just need to modify this line.
But don’t ask me how this would look for MariaDB.
In case someone stumbles here and wants to continue with MySQL:
I also faced this problem using MySQL and Grafana. The solution is to get the itemid from the Items table, and then construct the query. However, for dynamic table names you have to use a statement instead of normal select. Grafana created the correct SQL, but did not execute it properly (I guess only single line queries are supported). To overcome this, just create a procedure in your openhab database, e.g.
DROP PROCEDURE `itemDataByName`; CREATE DEFINER=`vb`@`%` PROCEDURE `itemDataByName`(IN `in_itemName` VARCHAR(128), IN `in_timeFrom` DATETIME, IN `in_timeTo` DATETIME) DETERMINISTIC READS SQL DATA SQL SECURITY INVOKER BEGIN SET @itemId:=(SELECT ItemId from openhab.Items WHERE ItemName = in_itemName); SET @itemTableName:=(SELECT CONCAT('openhab.Item', @itemId)); set @qry1:=concat('select UNIX_TIMESTAMP(Time) as time_sec, Value as value from ', @itemTableName, ' WHERE Time BETWEEN "', in_timeFrom, '" AND "', in_timeTo, '" ORDER BY Time ASC'); prepare stmt from @qry1; execute stmt; END
Now you can configure the query in grafana for an item named bla as follows:
CALL `itemDataByName`('bla', $__timeFrom(), $__timeTo());
Even after little adaptions i could not get this mysql procedure to be accepted by my DB.
The point it always fails is
SET @itemId:=(SELECT ItemId from openhab.Items WHERE ItemName = in_itemName);
The error my MariaDB gives is the simple #1064
error at the line around the where.
What could be possible wrong?
Im more a mysql noob then a crack and searching google did not really helped me.
So for give all the maybe needed informations: My DB is 5.5.62-MariaDB
I have tested the different parts of this procedure like the first select manually and the seem to work.
But the whole thing leads to above error.
Has someone a idea?
Here is my version of the above code adapted to my db:
DROP
PROCEDURE IF EXISTS `itemDataByName`;
DELIMITER @@;
CREATE DEFINER = `grafana`@`%` PROCEDURE `itemDataByName`(
IN `in_itemName` VARCHAR(128),
IN `in_timeFrom` DATETIME,
IN `in_timeTo` DATETIME
) DETERMINISTIC READS SQL DATA SQL SECURITY INVOKER
BEGIN
SET
@MyItemId =(
SELECT
ItemId
FROM
openhab.items
WHERE
items.itemname = in_itemName
);
SET
@itemTableName =(
SELECT
CONCAT('openhab.item', @MyItemId)
);
SET
@qry1 = CONCAT(
'select UNIX_TIMESTAMP(Time) as time_sec, Value as value from ',
@itemTableName,
' WHERE Time BETWEEN "',
in_timeFrom,
'" AND "',
in_timeTo,
'" ORDER BY Time ASC'
);
PREPARE
stmt
FROM
@qry1;
EXECUTE
stmt;
END
@@;
DELIMITER ;
i found the fix myself! For future readers:
My version of phpmyadmin did automatical a format of the one line code from v_b
and this failed because of the missing delimeter.
I changed my code in the posting above.
One problem still remains:
My items are starting from item0001 and end at item0018.
With the above aproach this does not fit.
Adding 00 is not enough for my case.
In case the formatting of the table names has changed, maybe use the LPAD
function to pad the itemid with zeros to get a fixed length string.