Grafana + MariaDB Mapping between Item number and Item name

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)

1 Like

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 to influxdb.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 :wink:

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?

seems promising,.

Please ask @christoph_wempe if the script should work with your openHAB version.

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.