OpenEVSE MQTT back to EmonCMS

glyn.hudson · 2023-07-13T09:28:25.449Z

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:

GitHub

GitHub - emoncms/emoncms-docker: Emoncms docker container(s)

Emoncms docker container(s). Contribute to emoncms/emoncms-docker development by creating an account on GitHub.

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

Ryan_S · 2023-07-13T11:22:46.599Z

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 · 2023-07-14T17:14:24.972Z

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.

image994×669 42.3 KB

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 · 2023-07-15T13:23:14.435Z

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 · 2023-07-15T23:47:29.310Z

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 · 2023-07-16T14:01:51.618Z

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 · 2023-07-17T15:58:46.904Z

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 · 2023-07-17T23:06:00.832Z

Mosquitto does appear to be running per systemctl output.

image943×376 43 KB

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 · 2023-07-18T01:18:13.187Z

Ryan_S:

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

image994×669 42.3 KB

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.

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 · 2023-07-18T01:35:44.226Z

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

Ryan_S · 2023-07-18T18:44:24.492Z

To expand. Is the Mosquitto MQTT server apart of EmonCMS or separate? Also, since this is a fresh install I’m beginning to wonder if there might have been an update to Mosquitto MQTT server that might have broken something. Not sure if you guys have updated your own and ran into this. That said, I could reinstall Mosquitto MQTT server just to see if maybe something in the script caused a bad install. Does the script just install Mosquitto or does it do anything else?

glyn.hudson · 2023-07-18T23:26:30.885Z

Ryan_S:

To expand. Is the Mosquitto MQTT server apart of EmonCMS or separate?

Totally separate. You should focus on getting MQTT Explorer to connect reliably to Mosquitto server before thinking about Emoncms. The script just installs Mosquitto and Mosquitto PHP plugin:

github.com

openenergymonitor/EmonScripts/blob/master/install/mosquitto.sh

#!/bin/bash
source load_config.sh

if [ "$install_mosquitto_server" = true ]; then
    echo "-------------------------------------------------------------"
    echo "Mosquitto Server installation and configuration"
    echo "-------------------------------------------------------------"
    sudo apt-get install -y mosquitto
    sudo apt-get install -y libmosquitto-dev

    # Disable mosquitto persistance
    sudo sed -i "s/^persistence true/persistence false/" /etc/mosquitto/mosquitto.conf
    # append line: allow_anonymous false
    sudo sed -i -n '/allow_anonymous false/!p;$a allow_anonymous false' /etc/mosquitto/mosquitto.conf
    # append line: password_file /etc/mosquitto/passwd
    sudo sed -i -n '/password_file \/etc\/mosquitto\/passwd/!p;$a password_file \/etc\/mosquitto\/passwd' /etc/mosquitto/mosquitto.conf
    # append line: log_type error
    sudo sed -i -n '/log_type error/!p;$a log_type error' /etc/mosquitto/mosquitto.conf
    # append line: listener 1883
    sudo sed -i -n '/listener 1883/!p;$a listener 1883' /etc/mosquitto/mosquitto.conf

This file has been truncated. show original

Try adding the following to mosquitto.conf

listener 1883

Then restart mosquitto and try and connect with MQTT explorer.

Ryan_S · 2023-07-19T01:59:46.739Z

Well, adding that made a difference.

image1014×731 35.3 KB

So, now that I’m connected. Time to see if OpenEVSE is getting anywhere. But, it’s a nice change. I think I added that before but got no love. Though, I might have forgot to restart MQTT.

glyn.hudson · 2023-07-19T11:29:20.976Z

Excellent, what version of mosquitto are you running? I think the requirement for listener 1883 was added in a newer version that we currently use.

I can see the openevse is connecting successfully, you should be good to go now.

I’ll add listener 1883 to our install script.

Ryan_S · 2023-07-19T14:38:47.461Z

I’ll get the version when I get home. I was starting to wonder if maybe I just happened to have gotten a newer version and a requirement changed, but it’s good to see I got the catch the change so we can nib this in the bud now. Both OpenEVSE are connection via MQTT and uploading to EmonCMS. Now I just need to flip my CTs for IotaWatt as it looks like my guess was 180 degrees off. Then build some Feeds.

Ryan_S · 2023-07-19T22:53:00.060Z

Look like it’s version 2.0.11

||/ Name           Version         Architecture Description
+++-==============-===============-============-====================================================
ii  mosquitto      2.0.11-1ubuntu1 amd64        MQTT version 5.0/3.1.1/3.1 compatible message broker

glyn.hudson · 2023-07-19T23:09:26.926Z

Interesting, the version on our image is also 2.0.11, however looking at mosquitto.conf it looks like listener 1883 is present, it must have been added in later but not included in the installation script.

Do you recall this @TrystanLea?

Sorry for the hassle, thanks for helping us to fix this.

Ryan_S · 2023-07-20T02:15:42.434Z

No worries. Next up for me is that it appears that EmonCMS is locking up after running for an extended time. I’m still troubleshooting that to confirm it’s not a fluke as I also appear to have an IotaWatt that is out of commission now, possible corrupted SD Card maybe or something else. lol. Home reno and tech is a fun mix.

borpin · 2023-07-26T06:28:40.918Z

glyn.hudson:

Interesting, the version on our image is also 2.0.11, however looking at mosquitto.conf it looks like listener 1883 is present, it must have been added in later but not included in the installation script.

I raised it in May. Problem with Mosquitto starting in Local mode - #5 by borpin

glyn.hudson · 2023-07-26T09:08:24.846Z

Ah right, thanks.