How to delete/reset stored data of item from mysql persistence?

ohb1138 · September 16, 2018, 6:32pm

Hello *,

I use min and max items in openHAB giving them constant values to set the right dimension for a chart. I accidentally have set a wrong value to the max value “24” instead of “2.4” and now the chart line for the max value has a peak and zooms out the whole graph to the max of 24 which is too big and makes the intended graph data too small to see any details. I would now like to reset all data of the max value which is stored in an item called “maxGraphValue”.

Is there a way how to purge all data of an item from the mysql openhab database?
I have found some hints that dropping the table of an item will delete all the data but I am totally newbie to administering mysql. Can anyone help me to get the right commands together to achieve my goal?

EDIT: Ok, I found a way via the phpMyAdmin Tool from my NAS to login to the database.
Now I see only Items1 to Items126. Is there a way to find out the mapping between the item numbers in the database and the item names in openhab?

Thanks for any idea and support
Justus

ohb1138 · September 16, 2018, 7:44pm

OK, here are my findings how to access the data via the phpMyAdmin UI … but I still would be interested in the command line commands to achieve this also via commands …

My database for openhab is called openhab (But the name does not really matter ) It contains tables for each item named from "Item1" to "Item###" (### depending on your number of items stored in your personal database).
These tables contain the collected data for each items from the time when the collection started.
There is one table called "Items" (in my graphical view of the phpMyAdmin UI it is the last table behind all "Item###" tables.
This table contains the mapping of the item id which represents the number in the table count "Item1" to "Item###".

Example from my database:

ItemId : “1” is mapped to ItemName : "aktFD3CegWZTbatt"

=> You find the data for “aktFD3CegWZTbatt” in table "Item1" :

The list of data is of course longer than this screenshot

And now you are able to change, copy delete values from the "Item1" table
by clicking on the appropriate command in the GUI:

“Bearbeiten” = “Change” - “Kopieren” = “Copy” - “Löschen” = “Delete”

Ok, this is now the “GUI way” … is anyone able/so kind to provide the commands to get this all accomplished via the command line???

Cheers
Justus

Richard · September 16, 2018, 8:00pm

Hi Justus,

You can just login to your database through SSH and then use the

$ mysql --user=your-user-name --password=your-password
OR
$ mysql -u your-user-name -p'Your-password' -h your-hostname

and once you are in the mysql shell you can type

mysql> show databases;

you should be albe find the rest of the commands yourself

ohb1138 · September 16, 2018, 8:31pm

Hi Richard,

Thanks for your advice … that wasn’t too hard to find out. I have done this already during the initial setup to create the openhab database and check that it is there. I am able to log in an see the list of databases but as I said I have no database skills …

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| openhab            |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]>

… so I really don’t know how to go from this point. Unfortunately your advice does not bring me any further

I thought it must be easy for someone who is experienced to provide the appropriate query and change commands … but it seems that I will need to find a mysql tutorial in the internet to teach myself how to get these commands myself …

ohb1138 · September 16, 2018, 9:17pm

So here are my bozo findings for mysql/MariaDB commands:

After login show the databases:

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| openhab            |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]>

Connect to database:

MariaDB [(none)]> use openhab;
Database changed
MariaDB [openhab]>

See that the identifier in the [ ] changes from [(none)] to [openhab]

Show the tables of the database

MariaDB [openhab]> show tables;
+-------------------+
| Tables_in_openhab |
+-------------------+
| Item1             |
| Item10            |
| Item100           |
| Item101           |
| Item102           |
| Item103           |
| Item104           |
| Item105           |
| Item106           |
| Item107           |
| Item108           |
| Item109           |
| Item11            |
...
| Item97            |
| Item98            |
| Item99            |
| Items             |
+-------------------+
127 rows in set (0.00 sec)

MariaDB [openhab]>

Find out the mapping between ItemId and ItemName looking into table Items

MariaDB [openhab]> select * from Items;
+--------+------------------------------------------------+
| ItemId | ItemName                                       |
+--------+------------------------------------------------+
|      1 | aktFD3CegWZTbatt                               |

...

|    125 | minGraphValue                                  |
|    126 | maxGraphValue                                  |
+--------+------------------------------------------------+
126 rows in set (0.03 sec)

MariaDB [openhab]>

a) Show all data stored for Item126 = ItemName: maxGraphValue

MariaDB [openhab]> select * from Item126;
+---------------------+-------+
| Time                | Value |
+---------------------+-------+
| 2018-09-16 20:23:01 |   2.2 |
| 2018-09-16 21:54:51 |   2.2 |
| 2018-09-16 22:13:26 |   2.2 |
+---------------------+-------+
3 rows in set (0.00 sec)

MariaDB [openhab]>

b) Show stored data of specific date and time for Item126 = ItemName: maxGraphValue

select * from Item126 where Time = "2018-09-16 20:23:01";
+---------------------+-------+
| Time                | Value |
+---------------------+-------+
| 2018-09-16 20:23:01 |   2.2 |
+---------------------+-------+
1 row in set (0.00 sec)

MariaDB [openhab]>

a) Delete all data from table Item126 = ItemName: maxGraphValue

MariaDB [openhab]> delete from Item126;
MariaDB [openhab]> select * from Item126;
Empty set (0.00 sec)
MariaDB [openhab]>

b) Delete only specific value from specific date and time from Item126 = ItemName: maxGraphValue

MariaDB [openhab]> delete from Item126 where Time = "2018-09-16 20:23:01";
Query OK, 1 row affected (0.06 sec)

MariaDB [openhab]>
MariaDB [openhab]> select * from Item126;
+---------------------+-------+
| Time                | Value |
+---------------------+-------+
| 2018-09-16 21:54:51 |   2.2 |
| 2018-09-16 22:13:26 |   2.2 |
+---------------------+-------+
2 rows in set (0.00 sec)

MariaDB [openhab]>

Insert data into table Item126 = ItemName: maxGraphValue

MariaDB [openhab]> insert into Item126 (Time, Value) values ("2018-09-15 15:15:15", "1.5");
Query OK, 1 row affected (0.04 sec)

MariaDB [openhab]>
MariaDB [openhab]> select * from Item126;
+---------------------+-------+
| Time                | Value |
+---------------------+-------+
| 2018-09-15 15:15:15 |   1.5 |
| 2018-09-16 20:23:01 |   2.2 |
| 2018-09-16 21:54:51 |   2.2 |
| 2018-09-16 22:13:26 |   2.2 |
+---------------------+-------+
4 rows in set (0.00 sec)

MariaDB [openhab]>

Change/Update data of table Item126 = ItemName: maxGraphValue

update Item126 set value = "2.2" where Time = "2018-09-15 15:15:15";
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [openhab]>
MariaDB [openhab]> select * from Item126;
+---------------------+-------+
| Time                | Value |
+---------------------+-------+
| 2018-09-15 15:15:15 |   2.2 |
| 2018-09-16 20:23:01 |   2.2 |
| 2018-09-16 21:54:51 |   2.2 |
| 2018-09-16 22:13:26 |   2.2 |
+---------------------+-------+
4 rows in set (0.00 sec)

MariaDB [openhab]>

So, here are the basics that I found quickly and which represent my use case above to find out the mapping of the item names to the item ids and where and how they are stored. You can now update specific item values at specific dates and therefore correct wrong data.

I will update this topic with new helpful commands if I have to find them

And anyone is kindly invited to post his helpful commands to change/update/list/alter the data stored in mysql respectively MariaDB !!!

ohb1138 · September 16, 2018, 10:09pm

Change/Update all data in column Value of table Item126 = ItemName: maxGraphValue

MariaDB [openhab]> update Item126 set Value = "2.1";
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [openhab]> select * from Item126;
+---------------------+-------+
| Time                | Value |
+---------------------+-------+
| 2018-09-16 20:23:01 |   2.1 |
| 2018-09-16 21:54:51 |   2.1 |
| 2018-09-16 22:13:26 |   2.1 |
+---------------------+-------+
3 rows in set (0.00 sec)

MariaDB [openhab]>

rlkoshak · September 17, 2018, 5:39pm

You could do this through the REST API.

This will work for all the Persistence databases, not just MariaDB/MySQL.

ohb1138 · September 17, 2018, 6:57pm

Hi Rich,

this looks interesting. I don’t know this GUI. How can I access it?

rlkoshak · September 17, 2018, 7:42pm

Install the REST API Docs add-on from the Misc tab of PaperUI.

Richard · September 17, 2018, 8:22pm

Never hurt anyone to put in a little effort themselves. Seeing you have done so, you must probably have learned something too. Congrats!

ohb1138 · September 17, 2018, 8:24pm

Thank you for making me a better person …

Richard · September 17, 2018, 8:31pm

Oo, what else are friends for, right?

jonnydev13 · September 19, 2018, 1:46pm

Here are sql commands that I’ve used when my persistence had gotten screwed up (usually after changing the type of an Item that had already been persisted, like string to number). These will completely remove the data for an item. It’s probably best to do this when openhab is not running, or at minimum restart the persistence after removing the table and record in the Items table. The next time it starts up and maxGraphValue changes it will create a new table and record in the items table. It will have a different ItemId and table name after it has been recreated.

select * from Items where ItemName = ‘maxGraphValue’;
drop table Item126;
delete from Items where ItemId = ‘126’;

You already figured most of the following out but might see a few new possibilities in the where clauses. These queries could be adjusted to do what you were wanting to do.

show tables;
select * from Items;
select * from Items where ItemName = ‘maxGraphValue’;
select * from Item126;
delete from Item126 where Value > 2.5;

ohb1138 · September 30, 2018, 3:21pm

Thanks Jonathan,

I did not know that the select statement can be “chained”. This is interesting and adds definitely value to this topic how to handle data in the database!