OpenEVSE MQTT back to EmonCMS

Ryan_S · 8 July 2023 14:17

So, I’ve been out of it for about a year now due to moving and “new” home that needed a full gut. After a long while I finally am to the point where I can bring back up my two OpenEVSEs and start rebuilding EmonCMS. So, the OpenEVSEs both came right back up and connected to the network with little trouble. My EmonCMS database though did have a small change where I moved it over, fresh install, to my Proxmox server. No firewall on the server for this container so it should work just like it was it’s own stand alone thing (think RasPI).

Anyway, so when I try to have my OpenEVSE connect via MQTT (and I also am trying the Energy Monitor as well with no luck) I am not getting any connection. Well, the logs look like there is a connection via MQTT, but nothing. Here’s what the log shows.

Now, I setup an Input in EmonCMS using the auto input build for OpenEVSE single Phase.

And finally my OpenEVSE setup.

So, I guess what I’m wondering is, it appears it is trying to connect. The log makes me think, it connects, but then is getting rejected. That makes me wonder if I have the node or base topic wrong. I saw an earlier post on here with a similar error and the solution they found was to reset the passwords for MQTT, but I can’t for the life of me find how I would do that. Help?

glyn.hudson · 8 July 2023 21:41

Have you got a mosquito MQTT server up and running?

Ryan_S · 9 July 2023 03:13

It would appear so.

Ryan_S · 10 July 2023 14:10

Just some more data to see if we can nail down what is going on or what I might be doing wrong.

I have two IotaWatts for monitoring various circuits in my house. One is on my main panel and another on the House Sub panel. Both of the IotaWatts were able to connect and send data to EmonCMS once I configured them. So this at least tells me that there are no firewall issues as both IotaWatts are on the WIFI VLAN and EmonCMS is on the LAN VLAN. The OpenEVSE are on the WIFI VLAN as well.

My guess is maybe I am configuring something incorrectly in OpenEVSE that is preventing it from posting because I think it should work no problem through the Energy Monitoring with minimal configuration. So, my first question. For Energy Monitoring, do I need to put a port in the address? Also, what is the node? Is that where I need to create an Input for the OpenEVSE and the node name of the input is what I use there? Last, I have a read key and a write key. I figure I use the write key.

On the MQTT side. IP address and port have their own location and those should be the same. But what about the user name? Where can I check what the user name is and password? Also, base topic. Before I had it under emon/openevse. But this is a new install and I don’t know or at least don’t remember where I can check what that is.

glyn.hudson · 10 July 2023 16:21

emoncms_mqtt is the Emoncms service which connects to an MQTT server. You need to run a local mosquito MQTT server if you’re self-hosting. You could use our pre-built emonSD raspberrpi image which as everything setup and pre-configured: emonSD — OpenEnergyMonitor 0.0.1 documentation

Or here’s the documentation for installing on your own server from scratch: EmonCMS Install Scripts — OpenEnergyMonitor 0.0.1 documentation

This script handles the installation of Mosquitto https://github.com/openenergymonitor/EmonScripts/blob/stable/install/mosquitto.sh

Ryan_S · 10 July 2023 19:15

I’ll give this a shot. I was thinking it was running based on the screen grab I put above in EmonCMS saying it was there. Guess I didn’t think about a separate service being required. I wonder why the Energy Monitor is unable to connect. Does that also run on a different or separate service or does it use the same connection that the IotaWatt is using?

That said, since this is a container in Proxmox. I’m starting to wonder if I should just redo it and start fresh. I installed InfluxDB within it and maybe that broke some things. I think I’ll do a specific EmonCMS container and separate InfluxDB container. It’s just been awhile since I installed in the first place.

Ryan_S · 11 July 2023 19:19

So I’ve reinstalled EmonCMS within a container in Proxmox. Things I do notice is that the container does not come with PHP 8.1 preinstalled, so have to do that. Next, the issue of OpenEVSE not being able to connect to EmonCMS via Energy Monitor or MQTT seems to persist. Now, I think I saw a previous issue maybe it was on github or in here where the Mosquito settings are not being set with a user name or password in the settings.ini. Not sure if maybe the logged error above might be referring to that.

glyn.hudson · 11 July 2023 22:08

You should either use the same mosquitto username and password as the emonPi (username: emonpi password: emonpimqtt2016) or make sure you change the passwords in the clients to match your MQTT server e.g settings,php, emonhub.conf and openevse config.

See docs here for how to test the MQTT server is working: https://docs.openenergymonitor.org/emoncms/mqtt.html

Ryan_S · 12 July 2023 02:52

So I checked the settings and the username and password for mosquito are correct. Began to do the testing and it would appear nothing is going through. So I decided to install Ubuntu Server 22.04 in a VM to see if maybe it was an issue with the container version of Ubuntu Server. Waiting on the install now, but will likely not finish playing with it until tomorrow.

Ryan_S · 13 July 2023 02:31

So, unfortunately I’m still having the same issue where OpenEVSE is unable to post data to EmonCMS even though the IotaWatt is posting. But I’m at a loss on how to change the log level to something other than WARN to see if I might glean some more information on what is going on.

glyn.hudson · 13 July 2023 09:28

You could try running the docker version, this has an MQTT built in as standard, at least it will allow you to quickly test to determine where the issue is:

MQTT explorer is very useful to test an MQTT server: http://mqtt-explorer.com/

Ryan_S · 13 July 2023 11:22

Not sure I would be able to run the Docker container within Proxmox, will look into that. I did see a previous post where someone else had the same error showing. They said they reset the MQTT credentials following a procedure in the knowledge base. I’ve wanted to try that, but for the life of my can not find that procedure on how to reset those.

That said, I have noticed that during the installation near the end there seems to be a Fatal Error. I think I will rebuild it and look to capture that error to see if maybe that might be the cause as well. I tend to rerun the install script and it finishes fine the 2nd time.

Ryan_S · 14 July 2023 17:14

Continuing on. Here is the error that I am seeing when I install EmonCMS.

PHP Fatal error:  Uncaught mysqli_sql_exception: Table 'graph' already exists in /var/www/emoncms/Lib/dbschemasetup.php:275
Stack trace:
#0 /var/www/emoncms/Lib/dbschemasetup.php(275): mysqli->query()
#1 /opt/openenergymonitor/EmonScripts/common/emoncmsdbupdate.php(20): db_schema_setup()
#2 {main}
  thrown in /var/www/emoncms/Lib/dbschemasetup.php on line 275

Fatal error: Uncaught mysqli_sql_exception: Table 'graph' already exists in /var/www/emoncms/Lib/dbschemasetup.php:275
Stack trace:
#0 /var/www/emoncms/Lib/dbschemasetup.php(275): mysqli->query()
#1 /opt/openenergymonitor/EmonScripts/common/emoncmsdbupdate.php(20): db_schema_setup()
#2 {main}
  thrown in /var/www/emoncms/Lib/dbschemasetup.php on line 275

Also, I’m using MQTT Explorer and this is all I seem to see.

It would appear that it’s not even able to connect. Or is and disconnecting immediately. Like something on the EmonCMS side is not keeping the connection.

Edit:
So in continuing to troubleshoot. I went in an edited the settings.ini and rebooted. Finally got a change, though not what I was hoping for, but a change. Still getting the same result on MQTT Explorer as see above, but the EmonCMS Log shows something.

<details><summary>LAST ENTRIES ON THE LOG FILE</summary><br />
2023-07-14 17:46:31.018|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:46:37.003|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:46:37.014|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:46:37.014|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:46:43.002|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:46:43.013|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:46:43.013|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:46:49.003|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:46:49.014|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:46:49.014|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:46:55.002|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:46:55.013|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:46:55.013|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:47:01.007|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:47:01.018|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:47:01.018|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:47:07.004|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:47:07.014|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:47:07.015|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:47:13.007|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:47:13.017|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:47:13.017|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:47:19.007|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:47:19.018|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:47:19.018|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.</details><br />

Sorry for the repeated entry. I might have been slightly frustrated. But I was trying both MQTT Explorer and OpenEVSE. This is a change from the one I was seeing before. So, looks like it sees the connection coming in at least.

Ryan_S · 15 July 2023 13:23

Stepping back a bit. Change my log level to Error to see what else might be going on. So, this is the MQTT information the log shows.

2023-07-15 13:13:33.-80|WARN|emoncms_mqtt.php|Not connected, retrying connection
2023-07-15 13:13:33.-69|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Accepted.: code: 0
2023-07-15 13:13:33.-69|INFO|emoncms_mqtt.php|Subscribed to: emon/# ID - 1
2023-07-15 13:13:33.-59|INFO|emoncms_mqtt.php|Callback subscribed to topic:

So, from what I can tell. It looks like it does establish a connection, but there doesn’t seem to be any topic that is being sent back. Am I reading this right?

Ryan_S · 15 July 2023 23:47

So I pulled up the debug option in OpenEVSE to see if maybe I could see what the EVSE was seeing when it tried to connect via MQTT.

"mqtt_connected":0}
{"amp":0,"voltage":240,"power":0,"pilot":40,"temp":387,"temp_max":387,"temp1":387,"temp2":false,"temp3":false,"temp4":false,"state":254,"status":"disabled","flags":513,"vehicle":0,"colour":5,"manual_override":0,"freeram":174564,"divertmode":1,"srssi":-60,"time":"2023-07-15T23:44:59Z","local_time":"2023-07-15T17:44:59-0600","offset":"-0600","session_elapsed":0,"session_energy":0,"total_energy":157.2954046,"total_day":0.223048336,"total_week":23.13938604,"total_month":43.64040468,"total_year":43.64040468,"total_switches":35,"elapsed":0,"wattsec":0,"watthour":157295.4046}
{"session_elapsed":0,"session_energy":0,"total_energy":157.2954046,"total_day":0.223048336,"total_week":23.13938604,"total_month":43.64040468,"total_year":43.64040468,"total_switches":35}
{"emoncms_connected":0,"emoncms_message":"Failed to connect"}

So, from what I can see. It looks like it EmonCMS is reporting it failed to connect (or OpenEVSE is). Though, my understanding is the “0” is a response that it connects. Anyone have any incite here?
@borpin I know you used Proxmox with a container and EmonCMS. Not sure if you used OpenEVSE and connected it via MQTT or not. I’ve tried this with both a container and VM and it’s the same either way which is odd. It’s like something has changed under the hood in EmonCMS here because I would expect through a VM and Ubuntu Server 22.04 to be as it would on a stand alone system.

Ryan_S · 16 July 2023 14:01

Continuing with my troubleshooting. So I did the MQTT test where I created a test topic and published to via a 2nd instance. That worked, so I at least know that MQTT is up and available to connect and publish. I’m starting to think the issue isn’t with MQTT but instead with EmonCMS. My question here is, once a connection is made to MQTT through Mosquitto. What occurs? Does the client then say it wants to connect to EmonCMS and publish into a database? Does it call up a topic "emon/#? I have edited the settings.ini to include a base topic thinking maybe that was it, but that didn’t seem to fix it.

glyn.hudson · 17 July 2023 15:58

Is mosquitto MQTT server running? What’s the output of

sudo systemctl status mosquitto

and the log

sudo tail /var/log/mosquitto/mosquitto.log

Also what’s the contents of the mosquitto config,

cat /etc/mosquitto/mosquitto.conf

It should look like:

# Place your local configuration in /etc/mosquitto/conf.d/
#
# A full description of the configuration file is at
# /usr/share/doc/mosquitto/examples/mosquitto.conf.example
listener 1883 0.0.0.0
pid_file /run/mosquitto/mosquitto.pid

persistence false
persistence_location /var/lib/mosquitto/

log_dest file /var/log/mosquitto/mosquitto.log

include_dir /etc/mosquitto/conf.d
allow_anonymous false
password_file /etc/mosquitto/passwd
log_type error

Ryan_S · 17 July 2023 23:06

Mosquitto does appear to be running per systemctl output.

Output of the mosquitto log is this.

pi@EmonCMS-CT:~$ sudo tail /var/log/mosquitto/mosquitto.log
1689370632: Opening ipv6 listen socket on port 1883.
1689370632: mosquitto version 2.0.11 running
1689370721: New connection from ::1:55062 on port 1883.
1689370721: New client connected from ::1:55062 as emoncms (p2, c1, k5, u'emonpi').
1689370729: New connection from ::1:45384 on port 1883.
1689370729: New client connected from ::1:45384 as auto-C0523A4B-CDB2-C09C-7F0F-BEF55D74B950 (p2, c1, k5, u'emonpi').
1689371649: Client auto-C0523A4B-CDB2-C09C-7F0F-BEF55D74B950 closed its connection.
1689371649: Client emoncms closed its connection.
1689371649: mosquitto version 2.0.11 terminating
1689371649: Saving in-memory database to /var/lib/mosquitto//mosquitto.db.

Looks like the client, emoncms, is closing the connection, but one was made.

Mosquitto configuration is this.

pi@EmonCMS-CT:~$ cat /etc/mosquitto/mosquitto.conf
# Place your local configuration in /etc/mosquitto/conf.d/
#
# A full description of the configuration file is at
# /usr/share/doc/mosquitto/examples/mosquitto.conf.example

pid_file /run/mosquitto/mosquitto.pid

persistence false
persistence_location /var/lib/mosquitto/

log_dest file /var/log/mosquitto/mosquitto.log

include_dir /etc/mosquitto/conf.d
allow_anonymous false
password_file /etc/mosquitto/passwd
log_type error

glyn.hudson · 18 July 2023 01:18

Try disabling the ‘Validate certificate’ toggle, does MQTT Explorer connect successfully now? If MQTT Explorer does not maintain a connection either, then the issue must be with Mosquitto MQTT server rather than Emoncms.

Ryan_S · 18 July 2023 01:35

I’ve tried it both ways and get the same result. Probably should reinstall MQTT?