Myphpadmin alternative? or who to debug persistence issues

Setup, Configuration and Use Beginners
1

Hi all,
I am running openhab 2.2 on a raspberry pi 3 (openhabian) with MySQL persistence (database on the same machine). In another thread I’ve read that it is not possible to install myphpadmin on the same machine also, as it requires apache web server which is not compatible with the one used by openhab.
Thus my question is how to manage the mysql database? Do you use any alternative to myphpadmin?
I am not very common with the syntax of mysql at command level. :wink:

In my specific case I would like to see what is written to the database for debuging. I have established a group of items (glight) in paper UI and put this group in mysql.presist with

glight*: strategy = everyChange

All other items and group do well but if I try to access historic data of this group openhab states:

[ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Light_office’.

Light_office is a member of glight which is clearly shown in PaperUi. I have the feeling that groups established in PaperUi or with items established in PaperUI (not part of any .items file) have issues with persistence. Especially because all other groups built in .item files work fine. To check this I would like to see what comes up in the data base.

Greetings
Sven

2

Personally i’m using the command line, it is not that hard.

mysql -u <user> -p

now you are asked for a password for the above specified user. If the user is OpenHab, execute

mysql -u OpenHab -p

Now you’re in the mysql command line

SHOW DATABASES;

lists all of your available dbs:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| OpenHab            |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

with the command

use OpenHab;

your switching to the Database where OpenHab stores the items (maybe you’ve selected a different name, so be sure that you are choosing the correct one).

After that:

show tables;

lists all of the available tables. Theres one table usually called “Items” that stores the name of the items and the table that is used for the items (i’ve truncated the output)


mysql> show tables;
+-------------------+
| Tables_in_OpenHab |
+-------------------+
| Item1             |
| Item10            |
| Item11            |
| Item12            |
| Item13            |
| Item14            |
| Item15            |
| Item16            |
| Item17            |
| Item18            |
| Item19            |
| Item2             |
| Items             |
+-------------------+
70 rows in set (0.00 sec)

With

select * from Items;

you should see all of the items stored from the persistence (again truncated)


mysql> select * from Items;
+--------+--------------------------------------------------+
| ItemId | ItemName                                         |
+--------+--------------------------------------------------+
|      1 | VentilHochbeet_Delay                             |
|      2 | VentilJohannisbeeren_Delay                       |
|      3 | Light_LivingRoom_GroundFloor_Meter_W             |
|      4 | Plug_TerraceLeft_Garden_Meter_W                  |
|      5 | Temperature_SmokeDetector_Entrance_Attic         |
|      6 | Pump_Circulation_Basement_Meter_kWh              |
|      7 | Pump_Circulation_Basement_Meter_W                |
|      8 | Plug_TerraceLeft_Garden_Meter_kWh                |
|      9 | Plug_TerraceRight_Garden_Meter_kWh               |
|     10 | Plug_TerraceRight_Garden_Meter_W                 |
|     11 | Light_Blind_Garden_Meter_kWh                     |
...
+--------+--------------------------------------------------+
69 rows in set (0.00 sec)

If your item is listed here, look for the ItemId and then you can look at all the values with

select * from Item5

where 5 would be replaced with your item ID. In my case i would see the Temperature values from a smoke detector in the attic

Make sure you put “;” after each command, otherwise you won’t get any output

3

Thank you @Sascha_Billian

I admit, this was sufficient for my purpose at the moment. I see that exactly the values I was looking for are not in my Database. Thank you for your easy “how-to”.
At least now I know that the issue is in the persistence and not in the way to read the database.
I have now to figure out what is the issue with the group. :frowning:

4

Can you post your complete mysql.persist?
How do you acess the historic data of the items in this group?

5

I solved the issue…
I tried to display the data (on off status) via timeline. This was so far correct.

The issue was solved after a reboot respectively restart of openhab. I have the feeling that somewhere in the chain

Group established in PaperUI —> items directed to group in paper UI —> group put in persistence via mysql.persist

the information can only flow with a restart.

I am sure if you work only in the .items file it works fine without restart … strange but now it runs.
Thank you for trying to help.

6

You absolutely can run both with a little work. Basically you need to install apache and change the default site to use different ports. I’m using debian 9.3, apache2 with phpmyadmin and nginx with OH2.2 on a VM.

Assuming you already have your OH setup and running without any phpmyadmin. This doesn’t do https setup AND I act as root… sudo is better.

apt install apache2

This will install but if you do systemctl status apache2… you will see it fails to start. This is because the ports it is bound to by default are already in use (80 and 443). We need to change them:

nano /etc/apache2/ports.conf

Change listen line from :80 to :81 (or whatever port you want)

nano /etc/apache2/sites-available/000-default.conf

Change <VirtualHost *:80> to <VirtualHost *:81>

Now disabled, enable and start apache:

a2dissite 000-default && a2ensite 000-default && systemctl start apache2

You can do a systemctl status apache2 and you should see it running. You should also be able to go to http://ip:81 in a browser and see the default apache page now.

Next install phpmyadmin as usual and go to http://ip:81/phpmyadmin and bingo… you should be in business.

7

You could use http://www.mysqlfront.de if you use a windows pc.

8

Thank you both … i will check the both ideas

9

I use https://www.mysql.com/de/products/workbench/ with ssh tunneling, works without additional configuration on the pi