Remote Connectivity to MariaDB

dome · 2021-06-02T16:54:09.040Z

First of all, I’d like to say Hello to everyone who is reading this topic.

I recently purchased the EmonPi after spending a good few months trying to find an energy monitor that would fit. I’ve managed to get it all set up and running giving me the real-time information I was looking for.

However, I was wanting to manually change the pulse sensor value to match what i have on my consumer unit. I’m lucky that I have 1k pulses per 1kw so there isn’t much math involved to get this to function using the pulse sensor that I purchased.

Here is what I have tried:

Logging into MySql using HeidiSQL ( Remote sql connection over the Lan )
I added in a rule sudo ufw allow MySQL and checked netstat to see if the device was listening,
Then I tried to log in but the account does not have a remote connection enabled…
MYSQL, I logged into here via SSH using the credentials provided in Service Credentials - Guide | OpenEnergyMonitor
but the account does not have permission to add remote connectivity.
The reason I’m trying to use HeidiSQL is due to the lack of SQL knowledge and I’m more comfortable using the GUI that it comes with.

I did have a quick search on the form to see if anyone else has had an issue connecting to the local MariaDB server but i didn’t find anything that stood out.

Thanks in advance.

Keiren

zag · 2021-06-03T13:08:39.374Z

Hi Kieren,

By chance, I was looking at something similar just now. I don’t have the complete answer, but I have some pointers.

First off - you should be able to change the pulse sensor factor (if needed at all) in the feeds section. You may not need to go near the database.

Second - if you do need to access the database there are a few things you need to clarify.

If you are connecting from a different machine you will need to edit a config file to have the server listen on the correct interface/IP address. I don’t have the config in front of me but the path is along the lines of /etc/mysql/mariadb/clients.cfg. There’s a line in the file which says to listen on 127.0.0.1 or localhost which means that you can only connect from the emonPi itself (the ‘local host’). If you change that line to listen on the wired or wireless interface you will be a step closer to connecting.
If you are connecting directly from the emonPi then the config above won’t be a problem. You should be able to establish the connection by just running a command line - sudo mysql. I know you want to use a GUI tool, but check at least that you can connect directly.

David

[edit] I will be working on my own DB access over the weekend and will try to post up specifics of the changes required.

dome · 2021-06-03T16:33:37.328Z

Thanks for the reply zag,

I’m lucky in the fact that the pulse is every Watt so 1000 pulses equal 1kw. What I was trying to do was change the current reading of say 6000 pulses to 123456.000 this would then be the same as the meter reading on my consumer unit. and would theoretically match the consumer unit.

I did try and navigate down the Database>table etc but I could only get so far before it “You shall not pass” aka no permissions

I’ll add my local range to that config file and will let you know if that works.

dome · 2021-06-03T16:45:12.854Z

zag:

/etc/mysql/mariadb/clients.cfg

I might also add, i purchased the EmonPi and this directory does not seem to exist, Below is the directory structure

   pi@emonpi:/etc/mysql $ ls -l
total 24
4 drwxr-xr-x 2 root root 4096 Aug 19  2020 conf.d
4 -rw------- 1 root root  277 Aug 19  2020 debian.cnf
4 -rwxr-xr-x 1 root root 1620 Jul  4  2020 debian-start
4 -rw-r--r-- 1 root root  869 Jul  4  2020 mariadb.cnf
4 drwxr-xr-x 2 root root 4096 Aug 19  2020 mariadb.conf.d
0 lrwxrwxrwx 1 root root   24 Aug 19  2020 my.cnf -> /etc/alternatives/my.cnf
4 -rw-r--r-- 1 root root  839 Aug  3  2016 my.cnf.fallback
pi@emonpi:/etc/mysql $

zag · 2021-06-03T17:24:17.964Z

My memory wasn’t too bad. the path I was looking for is /etc/mysql/mariadb.conf.d/50-server.cnf

#Instead of skip-networking the default is now to listen only on
#localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

If I wanted to connect in from another machine I would edit that line to something like

bind-address = 192.168.1.4

where 192.168.1.4 is the address of my emonPi. And then reboot my pi or otherwise reload mysql. However, this is just a suggestion to make it easier for you to use a GUI. There may be other ways to achieve your aim.

David

dome · 2021-06-03T17:29:53.267Z

Ah, Perfect, just made the change and waiting for it to bounce.

#Update it did not like having the bind-address set as the local ipv4 address

dome · 2021-06-03T18:41:33.650Z

By setting the address to 0.0.0.0 the web interface and data seems to be writing to the DB, However, the remote connection now shows the following errors,

    ERROR 1130 (00000): Host ''xxx.xx.xxx.xxx'' is not allowed to connect to this MariaDB server

This seems to be liked to the user emoncms not having the correct privileges. I did try and create a new user which would have the correct privileges but the following error was thrown:

ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation.

Seems I’ve hit another wall for this evening.

dome · 2021-06-03T19:50:59.072Z

I have managed to get in using an SSH Tunnel,

1, Enable SSH on the EmonPi,
2, using HeidiSQL enter the below details ( 10.11.11.180:22 is the IPV4 address of EmonPi)

3, Then Add in the Database Connection details as following:

Note here Because we are using an SSH Tunnel the hostname is 127.0.0.1, the Same username and password as listed in the documentation for the MariaDB

I can then access the database structure within the GUI,

image1135×472 62.5 KB

now to find and update the values…

zag · 2021-06-03T21:24:47.856Z

I now have access to the database in a similar manner to the way you suggested, but I created another SQL user along the lines of david@192.168.1.88 (the IP address of the host I’m connecting from) and used that to gain access. I couldn’t get the SSH tunnel to pass the authentication for root or emoncms properly.

Anyhoo, now that I’ve connected to the database I realise that the readings aren’t stored in there. The ‘input’ and ‘feeds’ tables store information about their relevant entities, but the value for the feed at a particular time is not stored there.

Have you found where they are stored?

David

Robert.Wall · 2021-06-03T21:48:07.000Z

zag:

Have you found where they are stored?

I have to admit I wondered why you wanted access to MySQL. The metadata is indeed in there, but the actual data is in “Home-grown” databases, which are explained in the “Learn” section. The directories are /var/opt/emoncms/phpfina & /var/opt/emoncms/phptimeseries.

dome · 2021-06-03T23:07:20.298Z

@zag
So, I managed to fudge the numbers by adding a value on top of the input within the inputs, then clicking on the spanner icon next to the sensor in question In my case, it was the Pulse Sensor usage I was trying to manipulate.

image1894×502 21.5 KB

Note I am not sure if it was necessary but I moved the “+ Value” above the sensor itself.

image902×339 15.9 KB

When this value is added to the original value I get my actual meter reading give or take a few watts.

@Robert.Wall
I was merely looking for a way to adjust the kwh value above the base value. I didn’t realize at this point that there was indeed a way that this could have been done within the EmonCMS UI.

I just wanted to add that it does seem like there is a decent community here, I can see a lot of RTFM for myself over the next few weeks while I get familiar with the UI.

Thanks

Robert.Wall · 2021-06-03T23:44:51.345Z

dome:

Note I am not sure if it was necessary but I moved the “+ Value” above the sensor itself.

That is indeed necessary. The data is processed as you read the list, top to bottom. With + value below Log to feed, the original value would go into the feed, you’d then add the constant but the result would never be used because there is nothing below to use it. Note that some processes (like Log to feed) pass the value they receive down without altering it.

For info, the energy units are not W/h, they are Wh (watts × hours, not watts per hour).

dome:

I can see a lot of RTM for myself over the next few weeks while I get familiar with the UI.

FTFY. We don’t use bad language to newbies, or anyone for that matter. There an awful lot to learn here, and we were all new once upon a time. My advice is don’t try to go too quickly.